Pages

Thursday, January 18, 2018

How to deploy SSIS package to Sql Server Integration Services Catalogs (SSISDB)?

Though there are different methods to deploy SSIS package, however; this blog post will cover how to deploy SSIS package to SSISDB. So far found, deploying to SSISDB is considered as best practice. SSISDB is introduced with SQL server 2012. The advantages you can have by using SSISDB as follows:
1) Easy to deploy from one environment to other
2) Report facilities in the SSISDB, so you can see execution time, error or whatever happend to your packages.
3) SSISDB is protected so your connection string and sensitive data is safe.

As per MSDN, "The SSISDB catalog is the central point for working with Integration Services (SSIS) projects that you’ve deployed to the Integration Services server. For example, you set project and package parameters, configure environments to specify runtime values for packages, execute and troubleshoot packages, and manage Integration Services server operations.The objects that are stored in the SSISDB catalog include projects, packages, parameters, environments, and operational history."

Let's divide the topic into two section 1) Creating Catalog 2) Deploy package to the catalog

Section 1: Creating catalog

step 1: Log into you Sql Server management studio and you will find the folder "Integration Services Catalogs", please create catalog as like below  fig 1:

fig 1: Create Catalog

Step 2: Create folder under Catalog:
Right click the newly created catalog 'SSISDB' and then create new folder with the different layers (e.g. staging, edw layer, data mart layer etc.)

fig 2: Folder under catalogue
After you create folder you will find  two new folders automatically created under the folder. It means if you create a folder called 'DSA' then you will find two folders  'Projects' and 'Environments' as like below Fig 2A
Fig 2A: folder under SSISDB


Section 2: Deploy package to the catalog

The below steps will assist you to deploy the package to the catalog.

Step 1: Build the solution and I am sure you all know how to build solution ;)

fig 3: build the solution
Step 2: Locate the .ispac file

fig 4: .ispac file

Step 3: Follow the wizard to deploy

Double click the .ispac file and then you will find below wizard:

fig 5: 1st page of the wizard

Choose the option project deployment and browse the .ispac file that you want to deploy.
fig 6: project deployment
Now you reach at the destination wizard where please  provide the destination server name (SQL server name where you will deploy the package) and then browse the path. When you click the browse for path you will find folder list from SSSIDB catalog (the folder you created at the section 1, step 2 of this post)

fig 7: choose destination path


So your package deployment is done to the SSISDB catalog.

SSISDB catalog has the opportunity to monitor the packages, you can create report from it. please find the below figure to generate the report:

fig 8: SSISDB catalog report