Notes on SQL

Background

In previous articles I have covered the creation of the SSISDB and adding the SSIS package. In this article I show how to move an SSIS package from one server’s SSISDB to another server.

When using the Integration Services Catalogs, moving an SSIS package (dtsx) from one server to another is a fairly straightforward task.

Test Environment

In the following example both SQL2014_INST1 (Instance One) and SQL2014_INST3 (Instance Three) have the required Integration Services Catalog installed. However, Instance One has an SSIS package that is not present on Instance Three.

Creating an export file

The project can be exported to a file by selecting the project, right-click and select ‘Export…’. This requires you to specify a location and filename for the resultant ‘ispac’ file – the file type used for project deployments within Integration Services Catalog.

However, if the target server (Instance Three) can see the source server (Instance One) then it is also possible to retrieve the project directly from the SSISDB of the source server, so the export might not be required.

Preparing the target environment

At this point Instance Three has no folders created within the Integration Services Catlog and will require one before the project can be imported. The name doesn’t have to be the same as the name of the folder used on Instance One. Right-click on the ‘Integration Services Catalog/SSISDB’ and select ‘Create Folder…’.

Importing the project to the target

Now that the folder exists it is possible to import the package – on Instance Three, right-click on the ‘Projects’ folder and select ‘Deploy Project…’.

The wizard requires either the location of the ispac file, created in the earlier steps, or the server with the Integration Services Catalog that has the required copy of the project.

Either:

Or:

After the source has been selected the next page of the wizard requests the target location.

Once the target has been selected a summary screen is displayed, whereupon you can press the ‘Deploy’ button.
If all is well, a screen will display the result as a series of ‘Passed’ remarks.

The project now also exists on the target server, Instance Three.