Extract, Import and Migrate SSIS Project

By: Joe Gavin   |   Updated: 2020-04-23   |   Comments (12)   |   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

1. Connect to SQL Server with the SSIS project using SSMS and expand the server dropdown in Object Explorer
2. Expand Integration Services Catalogs
3. Expand SSISDB
4. Expand Projects
5. Right click on the project to export
6. Export…

Choose file path and name

1. Select folder
2. Name .ispac file
3. Save

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.

• And here you'll see the files inside it.

• 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.

1. Open Visual Studio and choose 'Create a new project'

Import .ispac file with wizard

1. Choose 'Integration Services Import Project Wizard'
2. Next

Name Project and give it a home

1. Name Project
2. Click …
3. Enter folder name
4. Select Folder
5. Create

1. Next

Select .ispac file

1. Browse…
2. Browse to .ispac file path
3. Click on file
4. Open

1. Next

Do the import

1. Import

1. Check Results -> Close

You've now imported the Project so let look at it.

1. View
2. Solution Explorer

And here it is.

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.

1. Open Visual Studio and choose 'Create a new project'

1. Choose 'Integration Services Import Project Wizard'
2. Next

Give Project a name and a home

1. Name Project
2. Click …
3. Enter folder name
4. Select Folder
5. Create

1. Next

This is where we tell it to import from an SSIS Catalog rather than a .ispac file.

1. Server name
2. Path
3. Next

1. Import

1. Check results
2. Close

And here it is.

Redeploying SSIS Project

Whichever of the two methods above we used to get the Project into Visual Studio, the redeployment is the same.

1. View
2. Deploy

1. Next

Choose SSIS Catalog

1. Server name
2. Connect
3. Browse
4. Choose Project
5. Next
6. Deploy

1. Check results
2. Close

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.

1. Expand SQL Server
2. Expand Integration Services Catalogs
3. Expand SSISDB
4. Right click on Projects
5. Deploy Project…

Select SSIS Project source server and path.

1. Select Integration Services Catalog radio button
2. Fill in or Browse to SSIS server
3. Browse for Project
4. Select Project to deploy

1. Next

Select deployment target type

1. Verify target
2. Next

Select deployment target

1. Fill in or Browse to SSIS server
2. Connect
3. 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)
4. Next 

Verify and deploy.

1. Verify
2. Deploy

Verify.

1. Verify results
2. Close

And here it is.

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

Joe Gavin is from the Greater Boston area. He started working with SQL Server and Sybase in 1998 in the financial services industry and has been a SQL Server Database Administrator for a dairy cooperative since 2011. He graduated from Northeastern University in Boston with a Bachelor of Science in Engineering Technology (BSET) degree in Computer Technology. Joe has spoken at the Boston and Providence SQL Saturday events.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips