What is the best way to move all SSIS projects in SSIS catalog to another server
This is when we have to upgrade to a new server version for example from 2017 to 2019.
The computer name may change.
Thanks,
October 23, 2020 at 8:28 pm
Are all the projects in a Visual Studio project? If so, simply re-deploy them to the new server.
If they are not, export the existing packages to an ,ispac and import them into a new server
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
October 23, 2020 at 8:35 pm
Thank you. We have the two situations you mentioned.
First if export then import , when doing import from the Ispac, will that automatically upgraded it to 2019 SSIS?
second, if deploy from visual studio if there are some environment variable what to do with them, manually create? Thanks
The SSIS packages will not be automatically upgraded to 2019 - you need to open them in VS 2019 and upgrade them, changing the target to be 2019 also. Deploying the packages from the .ispac will upload them as is - and they should still work since you are deploying to a higher version.
Environment variables are not part of any packages - those will have to be recreated in the new system.
The last option would be a backup/restore - but that gets a bit tricky. This can be done by backing up the master key - create the catalog on the new server, restore the SSISDB on the new server, restore the master key. You can search for the specifics for the steps.
I prefer re-deploying, as that gives you the opportunity to cleanup and restructure if needed, upgrade the packages - if needed, as well as removing old versions of the packages and starting with a clean installation. You will lose the history though...so keep that in mind, and you need to rebuild all permissions...but again, this is an opportunity to cleanup and remove unnecessary logins/users/groups.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
October 24, 2020 at 8:11 pm
Google is your friend. Please review the blogs. Andy Leonard probably did more SSIS related work than most members on this site.
(Attempting to) Upgrade the SSIS Catalog to SSIS 2016
https://andyleonard.blog/2016/07/attempting-to-upgrade-the-ssis-catalog-to-ssis-2016/
Use Catalog Compare to Migrate to the SSIS 2016 Catalog
https://andyleonard.blog/2016/07/use-catalog-compare-to-migrate-to-the-ssis-2016-catalog/
Personally, I upgraded SSIS packages from SQL 2008 R2 to SQL 2016 by exporting packages -> upgrading packages -> deploying to SQL 2016 from SSDT/VS 2015. If no SSIS upgrade(SQL 2016 from one box to another), backup and restore SSISDB worked fine multiple times(except error 15581 – Please create a master key in the database or open the master key in the session before performing this operation, just alter the master key).
November 10, 2020 at 3:27 am
This was removed by the editor as SPAM
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply