https://social.msdn.microsoft.com/Forums/sqlserver/en-US/bf8d8b77-9cf0-4608-b6ad-93a463c88556/ssis-with-sql-2014?forum=sqlintegrationservices
Thanks for the information, Jinu.
I did not realize that there would be two SQL Server Integration Services (11.0 and 12.0) running after doing the in-place upgrade to SQL Server 2014.
Does anyone know if there is a wizard or script to upgrade all the SSIS 2012 packages in the SSISDB to SSIS2014?
It seems that you did not plan an upgrade just went ahead with it.
There is a number of options as per MSDN Upgrade Integration Services Packages
Thanks, Arthur. We did plan for the upgrade , but sometimes you just have to bite the bullet and give it a try.
The article, which you linked to, is referring to upgrading from SQL Server 2005 and 2008. Our databases and SSIS packages are 2012. Since there is no new SQL Native Client in SQL Server 2014, there were no connection changes necessary. In fact, in the tests that we have done so far, we have not had to make any changes in our SQL Agent jobs or SSIS packages after a in-place upgrade from SQL Server 2012 to 2014.
Maybe my question should have been: Since an in-place instance upgrade from SQL Server 2012 to 2014 does not change the database compatibility of SSISDB to 2014, is it Microsoft's recommendation that the database compatibility of SSISDB be changed to 2014? And if so, will I have upgrade all my SSIS packages to 2014 after the compatibility change?
I think we shall experiment in a sandbox environment.
If you have one, let's change the compat level of SSISDB to SQL 2014 and upgrade all the packages, then redeploy. And test. I think this is the proper procedure, but I did not find ant supporting information.
Please post here your results.
Hi DBA_Donna,
Based on my research, there is no recommended way in which the Schema of the SSISDB can be changed directly from SQL Server 2012 to SQL Server 2014. So we cannot directly take a backup, restored and moved the SSISDB from your existing SQL Server 2012 SSISDB to your new SQL Server 2014 SSISDB.
To work around this issue, we can redeploy the SSIS Projects or import the packages as required from the SQL Server 2012 environment to SSIS 2014.
The following blog about migrate SSISDB from SQL Server 2012 to SQL Server 2014 is for your reference:
http://blogs.msdn.com/b/dataaccesstechnologies/archive/2015/07/09/import-deployment-execution-of-ssis-packages-fails-after-you-migrate-ssisdb-from-sql-server-2012-to-sql-server-2014.aspx
Thanks,
Katherine Xiong