SSIS 2014 Execution Error while validating Script Component

It’s been so long after my last article. Looks like I been lazy for few years now 🙂

I assume lot of people using  SSIS package deployment utility after they support project deployment model. I always use the ispac for easier deployment though it doesn’t support environments variables in deployment. Today we deployed a project to our environment which contains new package and no modifications to the existing packages were made. New package worked fine but some of the old packages started failing, I checked internal versions of the package and couldn’t find any changes. Errors were happening in Script Task.

I remember this happened earlier but we figured out that we used latest Visual studio which upgraded the script component so reverted back but for this project we used the same version. We checked below things before started troubleshooting further

• None of the old packages are changed
• Same Visual studio 2013 is used so this should work in SQL Server 2014
• SSIS project setting was set to SQL Server 2014
• Validated the Package version after deployment , no change in internal version.

After all these checks I started trouble shooting further and found if you use latest SSMS to deploy a SSIS package to a older version then it will automatically upgrade the script tasks. In my case DBA deployed SQL Server 2014 ISPAC using SSMS 2016 to SSIS 2014 server which upgraded the script component to Visual Studio 2015 version. Bingo!! We found and redeployed the same ISPAC using SSMS 2014 which worked fine.

Now I thought of testing this by creating a sample package. I created a test project with only Script component in it and then built a ISPAC utility.

I thought of checking the script component version in the ispac so I extracted the content from ispac. If you have any compression software , use it to extract (you will see files as below).

Now right click on the package and then open it in notepad or any text editor. If you note the last modified product version in 12.0 which is SSMS 2014.

Now search for “<ScriptProject” , as you can see below the Major version is 3, so all looks good for me.

Now I deployed the project using SSMS 2016 and started the package but ended up with the error below. If you look at the error message it states it uses version 14.0 which is Visual Studio 2015 internal version.

Script Task:Error: There was an exception while loading Script Task from XML: System.Exception: The Script Task "ST_7432393ecf7a4af3906ba19425aeb245"

uses version 14.0 script

 that is not supported in this release of Integration Services. To run the package, use the Script Task to create a new VSTA script. In most cases, scripts are converted automatically to use a supported version, when you open a SQL Server Integration Services package in %SQL_PRODUCT_SHORT_NAME% Integration Services.     at Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask.LoadFromXML(XmlElement elemProj, IDTSInfoEvents events)

Now I thought of checking the package after deployment so I exported the project as an ispac from SQL Server.  I extracted the exported ispac and then opened the package in a notepad and all I see is “It’s AutoUpraged”

If you look at the header now the LastModifiedProductVersion is 13.0 (SQL Server 2016) and Script componenet Major version changed to 14 (Visual Studio 2015).

So its really important to use the same version as what you develop and which version you deploy to. If you want to deploy to SQL Server 2014 then use SSMS 2014 and use SSMS 2016 only for SQL Server 2016 and above.