SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Changing the version of an SSIS package.

Of all of the annoying parts of SSIS, the major version sensitivity has to be the most annoying. Let’s say you create a package in SSDT 2012. You later open that same package with SSDT 16 without thinking about it. SSDT automatically upgrades your to package version 8 (SQL 2014 & 2016). You run the package and SSDT automatically saves it. And now you can’t open that same package in SSDT 2012. You’re also going to have problems running it on that original SQL 2012 instance.

If you work in a large company with multiple versions of SQL running, and multiple teams creating SSIS packages for those different versions it can be a real mess.

I have a table I try to maintain the different version of packages etc to help out with the confusion.

Fortunately though, one of the big changes (in my mind) of SSDT is the ability to change SSIS versions. Prior to this, you would have to have scripted the package out in BIML and re-created it in the correct version.

So to start with I installed SSDT 17, created a project, and practiced deploying to the SSIS catalog on a 2016 instance. (Yes I wrote 3 posts for no purpose other than to lead up to this one. In fact I’ll probably do one more in a week or two because of this also.)

So starting with the project I created for SQL 2016:

Right click on the project and select properties.

Under Configuration Properties -> TargetServerVersion you’ll see a drop down allowing you to set the version to SQL 2012 to SQL vNext. I’m going to select 2012.

We do get a warning at this point. If anything in our project doesn’t exist in 2012 we will have problems (not surprising, but thanks for the warning).

Next, we build the project.

Now if you take a look at the dtsx file (remember that the ispac file is just a zip file) you’ll see that the packageformatversion is now 6.

Just to be certain let’s import and run it. I know I already created the ispac file but this time I’m going to do the deployment wizard directly from SSDT. Warning: if you are using an ispac file and hand it off to someone using anything lower than SSDT 17 but higher than the version you exported the deployment wizard will upgrade the package when they deploy it which can be a rather unpleasant surprise.

And here it is. FYI You can confirm the package format version by querying SSISDB.internal.packages. Now time to run the package!

Since I had this pointing to my 2016 instance and AdventureWorks2014 I am going to modify those properties of the connection manager.

And Success! We created the package for SQL 2016 then changed the version and deployed and ran it on a SQL 2012 instance!


Filed under: Microsoft SQL Server, SSIS Tagged: microsoft sql server, SSIS

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...