Extract, Import and Migrate SSIS Project

By:   |   Updated: 2020-04-23   |   Comments (10)   |   Related: More > Integration Services Development


Free SQL Server Performance and Monitoring Report


Dear Database Professional,

Download your free copy of the MSSQLTips.com SQL Server Performance and Monitoring Report. This survey was conducted in 2022 and polled 588 database professionals about various aspects of tuning and optimizing SQL Server.

Click here to download the free report

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…
Export the Catalog to a .ispac file

Choose file path and name

  1. Select folder
  2. Name .ispac file
  3. Save
Choose file path and name

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
  • And here you'll see the files inside it.
.ispac with a .zip extension
  • 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'
New VS Project

Import .ispac file with wizard

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

Name Project and give it a home

  1. Name Project
  2. Click …
  3. Enter folder name
  4. Select Folder
  5. Create
Name Project and give it a home
  1. Next
Next

Select .ispac file

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

Do the import

  1. Import
Import .ispac
  1. Check Results -> Close
Check results

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

  1. View
  2. Solution Explorer
Open VS Project

And here it is.

VS Project

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'
New VS Project
  1. Choose 'Integration Services Import Project Wizard'
  2. Next
Integration Services Import Project Wizard

Give Project a name and a home

  1. Name Project
  2. Click …
  3. Enter folder name
  4. Select Folder
  5. Create
Name Project and give it a home
  1. Next
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
Project source
  1. Import
Import
  1. Check results
  2. Close
Verify

And here it is.

VS Project

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
Deploy Project
  1. Next
Next

Choose SSIS Catalog

  1. Server name
  2. Connect
  3. Browse
  4. Choose Project
  5. Next
  6. Deploy
Choose target
  1. Check results
  2. Close
Verify

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…
Deploy

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
Select SSIS Project source server and path
  1. Next
Next

Select deployment target type

  1. Verify target
  2. Next
Select deployment target type

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 
Select deployment target

Verify and deploy.

  1. Verify
  2. Deploy
Deploy

Verify.

  1. Verify results
  2. Close
Verify

And here it is.

Deployed Project
Next Steps

We've seen three ways to get an SSIS Project from the Catalog. These are links to some more information:






get scripts

next tip button



About the author
MSSQLTips author Joe Gavin Joe Gavin is from Greater Boston. He has held many roles in IT and is currently a SQL Server Database Administrator.

View all my tips


Article Last Updated: 2020-04-23

Comments For This Article




Tuesday, May 3, 2022 - 10:14:30 AM - Joe Gavin Back To Top (90057)
Afraid not Marc. The scope of this tip was on-prem to on-prem. Haven't done anything with Azure Analysis Services yet.

Tuesday, May 3, 2022 - 5:08:42 AM - Marc De Vreese Back To Top (90056)
Did you find anything yet to deploy projects from one Azure SSIS Catalog (SSISDB) to another Azure Catalog ? Problem is that there is no SQL Authentication when selecting source, for destinition you have the SQL Authentication. So by using the wizard you can't deploy from Azure to Azure ? To bad that Microsoft didn't alter the Wizard by now.

Wednesday, March 30, 2022 - 12:58:55 PM - Joe Gavin Back To Top (89954)
Ann, I don't recall running into issue with needing the encryption key with that method so I think you're good.

Tuesday, March 29, 2022 - 11:59:15 AM - Ann Cai Back To Top (89951)
For option 3, do we need to do anything or any migration for the encryption key of SSIS catalog.
I guess not, we will just use the new server encryption setup for SSIS catalog, correct? that is because those environment variables need to setup on new server, so no need to migrate old encryption key, is that right?

Wednesday, October 6, 2021 - 3:40:38 PM - Joe Gavin Back To Top (89312)
Thanks Anil. I'm afraid you would need connectivity to both for #3.

Tuesday, October 5, 2021 - 9:49:02 PM - Anil Back To Top (89310)
Excellent Article, Thank you! With option 3, what if we do not have connectivity between source and target server.

Wednesday, August 25, 2021 - 11:24:18 AM - Saif M Back To Top (89166)
Excellent.. Thanks

Wednesday, June 30, 2021 - 9:26:42 AM - Chakrapani Kuppala Back To Top (88926)
Very useful two methods!

Saturday, January 16, 2021 - 3:06:40 AM - Samir Pachpute Back To Top (88054)
Thanks. Very useful post

Friday, April 24, 2020 - 2:19:49 AM - Venkataraman R Back To Top (85460)

Very useful information. Thanks 















get free sql tips
agree to terms