By: Joe Gavin | Comments (11) | Related: More > Integration Services Development
Problem
You have a SQL Server Integration Services (SSIS) Project deployed to an Integration Services Catalog and need to make changes to it or move it to another server, but you don't have the original source file or access to the Project source. Is it possible to access the project source code to make changes or deploy to another SQL Server?
Solution
We'll look at a few ways to solve this based on what needs to be accomplished.
- Option 1 - Extract Project it to .ispac file and import into Visual Studio
- Useful if you didn't have access to the SSIS Catalog form the machine you're running Visual Studio on so you could extract the file, move it, and import into Visual Studio
- Option 2 - Import Project directly into Visual Studio
- More straight forward if you need to make changes to Project
- Option 3 - Deploy Package from one SSIS Server to another via
SQL Server Management
Studio (SSMS)
- Easiest if you're just migrating from one server to another and don't need to make changes
The following versions were used in this tip:
- SQL Server 2017 CU19 Developer Edition
- SQL Server Management Studio 18.4
- Visual Studio 2019 v16.4.5
1 - Export SSIS Project to .ispac file and Import into Visual Studio
This method is handy if you maybe don't have Visual Studio at the moment, the SSIS Server is not on your network, or you just want to have the source.
First, let's see what the .ispac project deployment file is. Here's the Microsoft's definition from Deploy Integration Services (SSIS) Projects and Packages:
At the center of the project deployment model is the project deployment file (.ispac extension). The project deployment file is a self-contained unit of deployment that includes only the essential information about the packages and parameters in the project. The project deployment file does not capture all of the information contained in the Integration Services project file (.dtproj extension). For example, additional text files that you use for writing notes are not stored in the project deployment file and thus are not deployed to the catalog.
Export the Catalog to a .ispac file
- Connect to SQL Server with the SSIS project using SSMS and expand the server dropdown in Object Explorer
- Expand Integration Services Catalogs
- Expand SSISDB
- Expand Projects
- Right click on the project to export
- Export…
![Export the Catalog to a .ispac file](/tipimages2/6409_migrating-ssis-project.001.png)
Choose file path and name
- Select folder
- Name .ispac file
- Save
![Choose file path and name](/tipimages2/6409_migrating-ssis-project.002.png)
What's inside the ispac file
- We can digress for a moment. This is not necessary for this process, but if you're curious to see what's in an .ispac file, rename it to .zip or append a .zip to it and open it with Windows Explorer.
![Rename .ispac to .zip or append a .zip to it](/tipimages2/6409_migrating-ssis-project.003.png)
- And here you'll see the files inside it.
![.ispac with a .zip extension](/tipimages2/6409_migrating-ssis-project.004.png)
- Just rename it back to its original name before proceeding.
You're all set and can stop here at this point if all you need is the .ispac file to archive. But if you are making edits, we'll create a new Visual Studio Project, create a new SSIS Project and import the file.
- Open Visual Studio and choose 'Create a new project'
![New VS Project](/tipimages2/6409_migrating-ssis-project.005.png)
Import .ispac file with wizard
- Choose 'Integration Services Import Project Wizard'
- Next
![Integration Services Import Project Wizard](/tipimages2/6409_migrating-ssis-project.006.png)
Name Project and give it a home
- Name Project
- Click …
- Enter folder name
- Select Folder
- Create
![Name Project and give it a home](/tipimages2/6409_migrating-ssis-project.007.png)
- Next
![Next](/tipimages2/6409_migrating-ssis-project.008.png)
Select .ispac file
- Browse…
- Browse to .ispac file path
- Click on file
- Open
![Select .ispac file](/tipimages2/6409_migrating-ssis-project.009.png)
- Next
![NExt](/tipimages2/6409_migrating-ssis-project.010.png)
Do the import
- Import
![Import .ispac](/tipimages2/6409_migrating-ssis-project.011.png)
- Check Results -> Close
![Check results](/tipimages2/6409_migrating-ssis-project.012.png)
You've now imported the Project so let look at it.
- View
- Solution Explorer
![Open VS Project](/tipimages2/6409_migrating-ssis-project.013.png)
And here it is.
![VS Project](/tipimages2/6409_migrating-ssis-project.014.png)
2 - Import SSIS Project directly into Visual Studio
If we have Visual Studio and access to the SSIS Catalog, and you need to make edits and aren't concerned with having the source this option will save us some steps.
We'll start off in Visual Studio the same was as in the first method.
- Open Visual Studio and choose 'Create a new project'
![New VS Project](/tipimages2/6409_migrating-ssis-project.015.png)
- Choose 'Integration Services Import Project Wizard'
- Next
![Integration Services Import Project Wizard](/tipimages2/6409_migrating-ssis-project.016.png)
Give Project a name and a home
- Name Project
- Click …
- Enter folder name
- Select Folder
- Create
![Name Project and give it a home](/tipimages2/6409_migrating-ssis-project.017.png)
- Next
![Next](/tipimages2/6409_migrating-ssis-project.018.png)
This is where we tell it to import from an SSIS Catalog rather than a .ispac file.
- Server name
- Path
- Next
![Project source](/tipimages2/6409_migrating-ssis-project.019.png)
- Import
![Import](/tipimages2/6409_migrating-ssis-project.020.png)
- Check results
- Close
![Verify](/tipimages2/6409_migrating-ssis-project.021.png)
And here it is.
![VS Project](/tipimages2/6409_migrating-ssis-project.022.png)
Redeploying SSIS Project
Whichever of the two methods above we used to get the Project into Visual Studio, the redeployment is the same.
- View
- Deploy
![Deploy Project](/tipimages2/6409_migrating-ssis-project.023.png)
- Next
![Next](/tipimages2/6409_migrating-ssis-project.024.png)
Choose SSIS Catalog
- Server name
- Connect
- Browse
- Choose Project
- Next
- Deploy
![Choose target](/tipimages2/6409_migrating-ssis-project.025.png)
- Check results
- Close
![Verify](/tipimages2/6409_migrating-ssis-project.026.png)
3 - Deploy Package from one SSIS Server to another via SQL Server Management Studio (SSMS)
This method is handy if all you need to do is migrate an SSIS Project from one server to another and it doesn't require Visual Studio.
Open SSMS and connect to the source server.
- Expand SQL Server
- Expand Integration Services Catalogs
- Expand SSISDB
- Right click on Projects
- Deploy Project…
![Deploy](/tipimages2/6409_migrating-ssis-project.027.png)
Select SSIS Project source server and path.
- Select Integration Services Catalog radio button
- Fill in or Browse to SSIS server
- Browse for Project
- Select Project to deploy
![Select SSIS Project source server and path](/tipimages2/6409_migrating-ssis-project.028.png)
- Next
![Next](/tipimages2/6409_migrating-ssis-project.029.png)
Select deployment target type
- Verify target
- Next
![Select deployment target type](/tipimages2/6409_migrating-ssis-project.030.png)
Select deployment target
- Fill in or Browse to SSIS server
- Connect
- Browse to Project patch and enter Package name (I just renamed MySsisProject1 to MySsisProject2 where I'm deploying to the same server just to demonstrate)
- Next
![Select deployment target](/tipimages2/6409_migrating-ssis-project.031.png)
Verify and deploy.
- Verify
- Deploy
![Deploy](/tipimages2/6409_migrating-ssis-project.032.png)
Verify.
- Verify results
- Close
![Verify](/tipimages2/6409_migrating-ssis-project.033.png)
And here it is.
![Deployed Project](/tipimages2/6409_migrating-ssis-project.034.png)
Next Steps
We've seen three ways to get an SSIS Project from the Catalog. These are links to some more information:
- You can find a slew of SSIS related tip on MSSQLTips here: SQL Server Integration Services Development Tips
- And here is the Microsoft Docs SSIS documentation: SQL Server Integration Services
About the author
![MSSQLTips author Joe Gavin](/images/Joe-Gavin-2023.png)
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips