Can a SSIS 2012 package be deployed to SQL Server

Question

I built a SSIS package in VS2013 and can't find any deployment instructions. One person mentioned he thought you can't deploy backwards. So before I rebuild the package in BIDS 2008, I want to make sure there is no way to deploy.

Answer 1

Pretty much none of the MS BI stack (SSIS, SSRS, SSAS) is backward compatible, meaning you cannot develop in a newer environment and deploy to an older SQL Server.

And if you think about it, it's easy to understand why:

SSIS packages, SSRS Reports, and SSAS databases all have one thing in common. They are all inert XML files. Open one in notepad, and you will see XML. Try to execute one, and nothing will happen. Well, it will probably try to open Visual Studio, or whatever you have associated with its file extension, but the point is, it can't execute on its own like an .exe file.

The Engine that makes SSIS packages and SSRS Reports execute, and that processes SSAS databases and makes them queryable, is the service running on the SQL Server. It is capable of reading the XML file and saying "ah, I know what to do with this." It's like the SSIS/SSRS/SSAS file is a recipe, and SQL Server is the cook.

So in SQL Server 2008, the SQL Server Developers wrote an engine that can look at an XML file and follow the instructions in the file, as long as those instructions conform to a limited set of commands that the engine is written to handle.

They give those commands to the Visual Studio developers and say, "As long as Visual Studio produces an XML file that uses these commands, our engine will be able to interpret it and execute it." So Visual Studio 2008 produces XML files that are limited to commands that SQL Server 2008 will understand.

Then in 2012, the SQL Server Developers create a new improved engine that understands new commands that hadn't been thought of back in 2008. It gives those commands to the Visual Studio guys, and they start producing XML that uses the new commands.

So when you use Visual Studio 2012 to make a BI object, it can include commands that didn't exist in 2008, and Microsoft isn't going to go back and add support for them in SQL Server 2008. So SQL Server 2008 will see the new commands and say "huh?"

This is a pretty gross oversimplification (eg, it's not just commands but the whole format of the XML that can change from version to version), but hopefully it illustrates pretty clearly why you can't develop BI objects on a newer version of Visual Studio than the one that was paired with your version of SQL Server.

And hopefully it will be useful to the next person who's asking the same question.