Deploying Packages In SQL Server Integration Services (SSIS) 2012

In our previous article we had seen how to upgrade package from SQL Server Integration Services (SSIS) 2008 to SSIS 2012.

As we all know instead of deploying packages on File system or msdb database, we can directly deploy the packages into the Integration Services catalog. Before you deploy the package to the integration services server, the server must contain the SSISDB catalogs.

While installing the SQL Server, the catalog doesn’t create automatically; you need to create it manually.

When you open SQL Server, you’ll find a folder named Integration Services Catalogs as shown below.



By default, this folder doesn’t contain anything. Before deploying any packages to the catalog, we need to create the one for the same. We can create more than 1 catalog in a SQL Server instance.

To create catalogs, right click on Integration Services Catalogs as shown below.



After this you’ll get a Create Catalog window as shown below.



In this window you’ll find below options.

• Enable CLR Integration - check this option.
• Name of the catalog database - check this option as well.
• Password - provide a password.

After proving above inputs, click on OK and your catalog gets ready. Now if you browse your database list, you’ll notice a new database created automatically named ‘SSISDB’ which contains its own objects as shown below.



Now let’s begin our deployment.

Open your project, right click on that and select Deploy,



You’ll get ‘Integration Services Deployment Wizard’ which tells you about the steps involves in the deployment.



Click on next button and select your source deployment file.



Click on next and "Select Destination", select your server where you want to deploy your package.



If you notice, you’ll find an error stating ‘The path does not exist’. This is because there’s no folder available in your catalog to settle your package. To overcome from this error, simply click on browse button and you’ll get something like below.



Create a New folder by clicking on the New Folder button and fill the details.



Click OK button and now you can select this as a folder in the catalog.



When you’ll click OK button, error gets disappeared and you’ll get below output.



Click on Next button and review your deployment.



Click on the Deploy button to begin the deployment. Your deployment gets start and you’ll get the following progress window as indication.



On successful deployment you’ll get a Passed result for all the action as shown in below window. Click on Close button.



We’ve deployed our package successfully.

Now when you expand your Integration Services Catalogs folder in SQL Server, you’ll find your packages over there. Below is the screenshot for the same.



Now you can use these packages in your job or you can execute it manually. We’ll do it by setting up a job.

You can learn how to create jobs in SQL Server from below article.

• Jobs in SQL Server

On General Tab enter your job name.

On Step tab create a new job step and fill the details as shown below.



Fill below details:

Step Type as SQL Server Integration Service Package,
Package Source as SSIS catalog,
Enter Server Name,

Select your SSIS package via a browse button and click on OK.

On successful creation your job will get listed in the Job activity monitor.



Now run your job and your package will begin the execution.

Another way to execute the package is to right click on a package and select "Execute".



This will open an Execute package window where you can view your parameters, "Connection Manager" and other advanced options as shown below.



Click on OK button to begin the execution. On successful execution, the report gets generated and you’ll find the details of the execution in that report as shown below.



This report tells you the execution information like Execution duration, its start time, end time.

Also, you can check how much time taken by each task under "Execution Overview" section.



Our package executed successfully and we’ve taken a backup of system databases and backup file saved under C:\Backup\ directory, for each database backup task created separate directory and stored their backup file in the same as in the following two images:




With this article we’ve successfully deployed our package in SQL Server Integration Catalog.

Conclusion

In this article we’ve seen how to enable Integration Services Catalog and we also learned the steps to deploy the package into that catalog. Further, we have seen how we can run those packages, i.e. via job and direct execute method. Next we executed our package to perform the backup operation and also we’ve generated report for the same.

I hope this and other previous articles help you in learning SSIS. This is not the end; still we’ve lots to learn in this series. In our next article we’ll explore other tasks of SSIS 2012, till then keep learning and keep sharing….

If you’ve any suggestions or if you find any mistake in the article, please feel free to share via your valuable comments and feedback. I’ll try my best to implement those in my next article.