Extract, Import and Migrate SSIS Project


By:   |   Updated: 2020-04-23   |   Comments (1)   |   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…
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:



Last Updated: 2020-04-23


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





Comments For This Article




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

Very useful information. Thanks 



download





Recommended Reading

Import UTF-8 Unicode Special Characters with SQL Server Integration Services

SSIS Variable and Expression Example - Part 1

SSIS Expression Examples for Dates, String Concatenation, Dynamic File Names and More

Import Text and CSV Files into SQL Server Database with SSIS Script Task

Install SQL Server Integration Services in Visual Studio 2019














get free sql tips
agree to terms