Downgrading SSIS packages by changing the package version, why it might not be the right choice!

While we upgrade the sql server, we do upgrade the DTS/SSIS packages to the latest versions compatible with the upgraded sql server version. SSIS packages for sql server 2008/2008 R2 are build by Business Intelligence Development Studio (BIDS) and sql server 2012 or higher are build by using SQL server Data tools.

In some situations, Developers build SSIS packages using latest version of Data tools thinking it can be compatible with lower versions of sql server. I recently faced this situation where a developer build a package in 2012 version of data tools and tried to run the package in sql 2008 through sql agent job.

Changing the package version seems to be an easy fix, but usually its not!

Here’s why?

Once you change the package version by right clicking the dtsx package>open in notepad and change the version from 6 (sql 2012 version number) to 3 (sql 2008 version number), the package will be able to run on sql 2008 via sql agent job. But what about the execution results? Are they executing properly? Not always.

To test this scenario, I created a SSIS package using sql 2008 BIDS with name package.dtsx. This package has a execute sql task which executes a simple error raising script to see how these versions handle errors.

Below is the piece of code I use in the execute sql task for both versions of packages

Raiserror(‘Something went wrong’,16,1)

Note: For both the SSIS packages created, in the Execute SQL task properties> set both FailPackageOnFailure and FailParentOnFailure to ‘True’. This will make sure the package fails if the Execute SQL task fails.

Once I created the package, I tried to execute this ‘package.dtsx’ ( created by using sql 2008 BIDS) on sql 2008/2008 R2 using sql agent job. Ideally, it should throw an error.

Executed the job and as expected, job failed. This is an expected behavior.

I Created the same package in Data tools 2012 with name ‘package_2012.dtsx’. Open the package in notepad and Change the package version 6 to version 3 and save the file.

Now run this ‘package_2012.dtsx’ using sql server agent job

Execute the job and check the results, it get executed successfully. In this scenario. this is a wrong behavior. As Execute sql task has raiseerror code in it, package should throw an error, but it didn’t.

Conclusion: By just changing the Package version from higher to lower version numbers may allow lower versions of sql server execute the package through sql agent job but It doesn’t mean that the package is executed efficiently. In this scenario, package should fail by throwing an error but it doesn’t fail and executed as success. It is always better to create the SSIS packages using BIDS or Data tools based on the sql server version on which the package should run.