4

We have SQL Server & SSIS 2014 in our local environment, and most of SQL Server 2014 in our production environment, except for the SSIS, which is still 2008.

So we're creating SSIS packages locally which can't be deployed to production, because the SSIS packages are not backwards-compatible.

Is it possible to create the packages locally to target SSIS 2008? If so, how?

Just to note, the SSIS packages are defined in Visual Studio 2008, so I don't understand why they're dependent on SQL Server 2014.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
DaveDev
  • 41,155
  • 72
  • 223
  • 385
  • Q1 -You have at least two production servers that are SQL Server. At least one is 2014, and one is 2008. But all the SSIS packages must run on the 2008 server? Q2- If you've authored the package using VS 2008, you've built a package that runs against SQL Server 2008 - see [breakdown](http://stackoverflow.com/questions/34118876/sql-server-data-tools-in-visual-studio-2015/34125728#34125728) I have all of them installed on my lab machine and can deploy up the version fine. Specifically, what are you seeing that isn't working? – billinkc Dec 07 '15 at 03:05
  • In production we had Sql Server 2008. We've upgraded all but the SSIS & SSRS components to be 2014. The SSRS stuff works fine, btw. – DaveDev Dec 07 '15 at 03:07
  • 1
    As long as you are authoring with VS 2008, it will run on 2008 in your production. Perhaps screen shots or something else might help my poor brain understand the roadblock you're encountering – billinkc Dec 07 '15 at 03:18
  • I'm still at a relatively early stage in this investigation and I'm very new to SSIS, so this may be a "red herring". Thanks for confirming that it *should* work :-) – DaveDev Dec 07 '15 at 03:21
  • Don't get me wrong, there are ways to break a perfectly fine 2008 package just by letting a 2014 tool touch it (basically if you deploy it using the dtutil in the 2014 program file location instead of 2008 which could happen if you installed 2014 first on the machine performing the deploys...) – billinkc Dec 07 '15 at 03:25
  • You might actually be on to something. I can see a script that attempts to execute dtutil for 2008, but if that fails it'll try 2014, which could explain why 2014 packages are being generated. Unfortunately, I can't see anywhere this script is being called! Hmm.. further investigation is warranted. – DaveDev Dec 07 '15 at 04:02
  • 1
    It's like I'm a genius (or as I tell people I've made all the dumb mistakes you can make with SSIS - learn from my failures). SSIS packages can always be opened by a newer version of the tooling thus, 2005 will run on a 2016 instance without you even asking (assuming no custom/3rd party components). When the version+ components touch a package, they discover it's an older version and do an in-memory upgrade to current version. For run, dtexec, that's not an issue as the memory version is discarded. For deploy, dtutil, it's going to deploy the upgraded/in memory version. Hijinx ensue – billinkc Dec 07 '15 at 04:19

1 Answers1

0

The format of the SSIS packages has change from SSDT 2012 to 2013/2014. I was facing that situation recently before a Go Live. A colleague had developed SSIS packages in SSDT 2013 with SQL Server 2012 as the target environment for the deployment. While the development and execution from within SSDT 2013 with SQL Server 2012 is no problem, the package is not deployable within the SSIS catalog of SQL Server 2012. SSIS Packages are NOT downward compatible. ...at least with respect to the deployment in the SQL Server SSIS catalogue.

Open the dtsx file in the editor and check the value for "PackageFormatVersion". Possible values are:

  • VS 2013/2013 = 8
  • VS 2010/2012 = 6

Unfortunately, you cannot open SSIS packages with PackageFormatVersion=8 in SSDT 2012 or lower in order to downgrade a package. You could of course figure out, how the format has changed. I started to do this but gave up after a while. The best possible solution was to redevelop the package in SSDT 2012. Argh!

So, when developing SSIS packages you must take care of the target SQL Server version and then decide to choose the right SSDT version.

Marcus Belz
  • 181
  • 1
  • 7