SSIS: Scripts in package part not being executed when target server is 2016

Question

I am having an issue with SSIS packages where scripts included in package parts ar not executed when the target server version is SQL Server 2016.

1. Create an SSIS project, set target server version to SQL Server 2016 (very important!)

2. Go ahead and add a simple script in the default Package. This should work fine.

   • In my case I have a script that sends an email using SendGrid service via SMTP. You can see the code here: https://pastebin.com/DW3hfBP3
   • However I tested this issue with other scripts Something like: MessageBox.Show("Great. Another bug. Thanks a lot guys..."); would do.

3. Create a package part.

4. Now just try re-creating the simple script from step 2 into the package part. Then add the package part onto the default Package or another package and execute the task. Even though the execution is successful, the script is not executed.

   NOTE: If you try to copy the script from the default package into the package part, you should also notice a moronic error saying that the version is wrong. This is why I say to re-create the script in the package part.

I wonder if anyone could reproduce this issue and if there is a way around it? Worth noticing that this issue doesn't seem to happen when the target server is SQL 2017.

Answer 1

Microsoft confirmed that this is indeed a bug that will be fixed in the next version of SQL Server Data Tools (SSDT) for Visual Studio 2017.

The confirmation was in the Integration Services (SSIS) Advisors Group on Yammer. The group is closed so I cannot post a direct link to it. However here is what I have been told:

There is no easy workaround. A package containing package part has issues when persisting in previous target server version. You can try to either:

1. Install and use SSDT for VS2015 release 16.5 (https://go.microsoft.com/fwlink/?LinkID=832313)
2. Use your existing SSDT for VS2017, but always design your package targeting to latest SQL Server. When you want to deploy to SQL Server 2016, switch target server version to 2016, but DO NOT open the package again or launch deployment wizard. Instead, you should copy the dtsx into another empty project which does not use package part, and deploy that project.

Sorry for the inconvenience. We will fix it ASAP.

NOTE: I have since then stopped using package parts and instead, I use parametrised child packages as an alternative.