SSIS compatibility

by LbISS   Last Updated April 21, 2018 09:06 AM

I'm trying to use SSIS for preparing data warehouse and for making business objects for report generator. The problem is that i have to support multiple sql server versions - sql server 2008r2 and further (2012, 2014). As i'm understanding - i have to install sql server 2008r2 and business intelegence studio, visual studio 2010, visual studio 2013 with corresponding sql server data tools and make three independent packages...

Is that so? Is there any more appropriate way to develop SSIS packages for few versions of sql server? It'll be great just to use MSVS 2013 and convert ssis package to old formats (w/o new functions, of course).

Tags : sql-server ssis


Answers 3


You may run packages created with "any" earlier version of SSMS using dtexec, they seem to maintain backward compatibility.

https://msdn.microsoft.com/en-us/library/bb522577(v=sql.110).aspx

Check out the "Other versions" widget !

user2955677 - Greg
user2955677 - Greg
August 26, 2015 08:43 AM

Option A

Take a command and control approach. Designate your newest SQL Server tier of boxes as the runner of all things SSIS. Your connection manager for SQL Server will be able to speak the appropriate TDS dialect for everything in your enterprise.

Knowing nothing else about your organization, I would generally favor this approach. The management features added into 2012+ for SSIS make it a no brainer over 2008 both for management as well as development. Further, your solution will have the longest possible shelf life.

Option the second

As user2955677 proposes, build SSIS packages against your earliest form factor. They will seamlessly upgrade, per execution, to the current SSIS architecture. Test that though because the time spent on package execution will go from 10 seconds on 2008 to 10 + N seconds on 2016 instance and you will pay that cost every time the package runs. That cost may not matter much if you have the CPU and time to spend. But if you run that package every 15 seconds and it takes 5 seconds for the upgrade, someone's blowing their SLA.

Option wait and see

It has been announced citation needed that with the 2016 release of SQL Server, they will be offering a unified design experience with SSIS. In practical terms, your SSIS projects will be able to target SQL Server V.current and SQL Server V.current -?.

Were I a betting man, I would expect that the design tools that are released with 2016 will be able to emit SSIS packages that run on 2014 and 2012. The difference in the XML and the project deployment model would make it a trickier prospect to allow SSDT to also target 2005/2008.

Option write once, emit for all

This is the Java marketing line from the 90s - write once, run anywhere. Biml, Business Intelligence Markup Language, is an XML dialect used to describe business intelligence objects: cubes, dimensions, tables, ETL. There's a free addon, BIDS Helper that converts biml to SSIS packages.

Using this approach, you write your ETL in terms that describe the pattern: Foreach Enumerator with a DataFlow that reads all the CSVs and writes them to a table.

Armed with your biml file, you then open up whichever version of BIDS/SSDT you need to make a package run under and whoosh out comes the SSIS package. If you don't want to have 4+ different versions of Visual Studio/BIDS/SSDT-BI installed on your machine, then you can buy BimlStudio, previously known as Mist, and from there, you simply select the target version of SSIS you'd like to generate packages against.

This approach is perfect for those who have to deal with changing environments but want a consistently implemented solution for ETL.

billinkc
billinkc
August 26, 2015 14:06 PM

SSIS Package backwards compatibility for database developers, database administrators, and software engineers over the past ten years has been difficult. Grabbing the correct tools and getting to work on developing and/or maintaining existing code for SQL Server has been a bit of a nightmare.

The following video deals with the SSDT Naming Conflict - however, in the video are some resources for dealing with SSIS Backwards Compatibility you might find helpful.

Part 00 – SQL Server SSDT Naming Conflict https://youtu.be/Aw9k3l1fa4g

Tech Archangel
Tech Archangel
April 21, 2018 08:38 AM

Related Questions


SSIS Error on cryptographic error

Updated April 24, 2018 19:06 PM


Adding Image to excel Export in SSIS

Updated January 11, 2018 17:06 PM

Writing Date as text in Excel using SSIS

Updated February 01, 2018 11:06 AM

MySQL to SQL server table migrate using SSIS package

Updated August 01, 2016 08:02 AM