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