SSIS 2012 Projects: Setup, Project Creation and Deployment

Comments 0

Share to social media

Starting with SQL Server 2012 it is possible to use the newly-introduced Project Deployment model for SQL Server Integration Services. The new model promises to provide deployment flexibility, improved configuration options and easier monitoring and maintenance.

Up until SQL 2012 the SSIS deployment was package-based, i.e. each SSIS package had to be deployed separately, and the deployed packages could be stored either on the file system or in a system database repository. The new Project Deployment model uses its own designated database (called SSISDB) and the deployment is performed via a single ispac file, which contains all packages and configuration variables (Project/Package Parameters, Environments, Environment variables).

This article covers the process of deploying the project, including the setting-up of the SSIS Catalog and creating the project. In the next article, ‘SSIS 2012 Projects: Deployment Configurations and Monitoring’ I cover the configuration and management of environment variables, and explain how to monitor individual packages.

Setting up the Integration Services Catalog

Before we can deploy any projects to a SQL Server instance, we first need to create the Integration Services Catalog. Think of this catalog as the container for all deployed projects, their settings and historical versions.

There is only one Catalog per SQL Server instance; it is represented by a separate SQL Server database called SSISDB, which contains the deployment’s versioning, settings, and even statistical performance data. It uses SQL Server encryption; This is why we are asked, during the catalog creation, to provide a password, which will be used to create the database master key which will be used to encrypt sensitive data.

In order to use the Project Deployment model, SQLCLR needs to be enabled on the SQL Server instance.  Here is a script which enables the SQLCLR:

In order to create the catalog, we need to connect to the SQL Server instance and right-click on the ‘Integration Services Catalogs and click ‘Create Catalog.

2086-30f1a133-d5ee-4892-8a87-7dd2d550c6c

In the next screen we are asked for the encryption password and whether we would like to run the Catalog.startup procedure every time the SQL Server instance starts (the Catalog.startup stored procedure fixes the status of the packages in the catalog if there were packages running when the SQL Server instance went down):

2086-d6efe4f0-325a-4801-aac8-8c3e883816b

After we have created the Catalog, we can create a new folder under it, which will contain our first project. To create the folder, simply right-click on the SSISDB catalog and click ‘Create Folder:

2086-9f1d919a-1e38-479f-bdcc-d15a88bdf16

Then we are presented with a dialog to enter the folder name:

2086-e77431bf-2cb7-4a59-88ac-fc921b514d4

Under every newly-created folder we have two sub-folders: ‘Projects and ‘Environments‘.

2086-77d1af7c-104f-4b3f-97e3-d6b5cbd1d1a

The ‘Projects folder will contain the deployed SSIS project and the ‘Environments‘ folder will contain the different sets of environment settings we will be creating later on. These will include such things as the settings for Development server, for Test server, Production server.

Creating the SSIS project

The process for the creation of development projects in BIDS has not changed, except for the fact that the default setting in SQL 2012 onwards is the Project Deployment model. There is, of course, a way to right-click on the project solution and convert it back to the Package Deployment model.

For the purpose of this article, however, I will create a database ProjectDeployment_Test and two tables called InfoSource and InfoDest.

Here is the script for creating the objects:

After I have created the database and the objects, let’s create a simple SSIS package in our project. The package will truncate the destination table and copy the data from the InfoSource table to the InfoDest table.

The package and project look like this:

2086-e94aa5d4-6058-4491-8f24-9e6dfa2fa81

Now let’s create a project parameter by double-clicking the Project.params. The newly created parameter looks like this:

2086-37630674-c668-4f27-8fc2-cf8a0e90269

Project parameters are accessible by any package in the project and are very useful for dynamic configurations of the deployed projects.

I will go in detail about how to use project parameters in the next article on Project Deployment: Configuration and Management of the SSIS, but for now all we need to do is to put this project parameter in use.

We can do this by clicking once on the connection in our SSIS package and then editing the Expressions in the Properties:

2086-dc4bcc6c-7907-4035-821a-362768f1642

Deploying the project

Now let’s deploy the project to our SQL Server instance. By right-clicking on the project solution in BIDS and clicking Deploy, we get to the following wizard:

2086-fc41e72c-94c9-4753-88f0-c36cc06b020

We have to fill in the Server name, in my case it is the localhost. Then we click ‘Next‘and then ‘Deploy‘.

Another way to deploy our project, if we do not have sufficient rights to do so, is to go to the bin folder of our SSIS project after we have built the solution and deliver the *.ispac file which is found there to a system administrator who can deploy the project for us. (in this case my file is called ProjectDeploymentSSIS.ispac)

The system administrator then has to login to the SQL Server instance, go to the SSIS Catalog, right-click on the Projects folder in the correct folder and click ‘Deploy Project:

2086-d4c4ddaa-5829-4661-af8b-940f458ee26

Then the following wizard appears:

2086-4270318b-bf06-4881-8088-118dd104d0d

The deployed project now looks like this:

2086-fe1ef43d-a321-449a-9f40-f9f9d03b51d

If you encounter an error “A .NET Framework error occurred during execution of user-defined routine or aggregate “deploy_project_internal“: System.ComponentModel.Win32Exception: A required privilege is not held by the client“, then make sure that the SQL Server service account is added to the Local security policy “Replace a process level token” in ‘User Rights Assignments in the secpol.msc console. A restart of the SQL Server service is required to change the permissions.

From this point on, we can execute the deployed package by scheduling an SQL Server Agent Job and using the package in it.

Here are a few caveats of the Project Deployment model:

  • keep in mind, that in the project deployment model there is no way to deploy only changes to a single package. Instead, the entire project has to be deployed every time.
  • The SQLCLR is used to deploy the packages to the SSIS catalog, but not needed further on for running the packages. So, even in organization with strict security policies, the SQLCLR can be enabled while setting up the SSIS catalog and while deploying, after which it can be disabled again
  • Finally, if you are using Transaction Option: Required in your SSIS packages, you might run into trouble with the permissions of the MSDTC. Here is a very useful article on how to solve the problems with the SSIS and the distributed Transactions Coordinator: Troubleshooting MSDTC Permission Issues when a Distributed Transaction Starts

Conclusion:

So far we have seen how easy it is to deploy packages by using the new Project deployment model. This really speeds up the deployment of large scale environments (in large organizations there may be 100s of SSIS packages per project).

In the next article of the series ‘SSIS 2012 Projects: Deployment Configurations and Monitoring’, we pay special attention about dynamic configurations, the use of environment variables and large scale deployments. I’ll also explain how easy it is to monitor the packages.

Load comments

About the author

Feodor Georgiev

See Profile

Feodor has a background of many years working with SQL Server and is now mainly focusing on data analytics, data science and R.

Over more than 15 years Feodor has worked on assignments involving database architecture, Microsoft SQL Server data platform, data model design, database design, integration solutions, business intelligence, reporting, as well as performance optimization and systems scalability.

In the past 3 years he has expanded his focus to coding in R for assignments relating to data analytics and data science.

On the side of his day to day schedule he blogs, shares tips on forums and writes articles.