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.

 ssms2016_ssis2014_deployment_1

ssms2016_ssis2014_deployment_2

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).

ssms2016_ssis2014_deployment_3

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.

ssms2016_ssis2014_deployment_4

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

ssms2016_ssis2014_deployment_5

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)

ssms2016_ssis2014_deployment_6

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).

ssms2016_ssis2014_deployment_7 ssms2016_ssis2014_deployment_8

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.


Posted

in

by

Comments

6 responses to “SSIS 2014 Execution Error while validating Script Component”

  1. Gelios avatar
    Gelios

    I have a 2016 server and a Visual Studio 2015
    In the studio, I opened a package created in the studio of 2013.
    I changed the Target ServerVersion to the SQL Server 2016 in the properties of the package.
    But the error still fades.
    What should I do to make the package work?

    1. VidhyaSagar avatar

      @Gelios
      It doesn’t matter even if you change the project property. When the deployment happens it upgrades the script component to use the version of deployment utility.

  2. sqlworldwide avatar
    sqlworldwide

    You saved my day. Thank you for your article.

  3. Srini avatar
    Srini

    I had the same problem with Visual Studio 2015 and SQL Server 2016 when deployed ispac file using SSMS 2017. It would have taken me ages to figure out the problem with out the help of this article. Great job! Thanks a heap.

  4. Srini avatar
    Srini

    EXECUTION in the title of the article is misspelt, please correct it.

    1. VidhyaSagar avatar

      @Srini, thanks for the correction. I did the changes now.

Leave a Reply

Your email address will not be published. Required fields are marked *