SSIS : Catalogs and Denali

Today, i want to talk about the catalog.The catalog is used in the new deployment model of Denali. There are four stages of the project deployment life cycle:

1. Build: Prepares your project for deployment.
2. Deploy: Adds your project to the Integration Services catalog on an instance of SQL Server.
3. Import: Loads a project into BIDS from the Integration Services catalog or a project deployment file.
4. Migrate: Converts legacy packages and configurations so that they can be used in the project deployment model.

As Jamie Thomson states in his blog, the SSIS server is no longer a separate service that you connect to, it now appears as a node in Object Explorer when connecting to a SQL Server database instance:

Before using the catalog you need to enable the CLR:

EXEC sp_configure 'show advanced options' , '1';
reconfigure;
EXEC sp_configure 'clr enabled' , '1' ;
reconfigure;
EXEC sp_configure 'show advanced options' , '0';
reconfigure;

and now it's possible to create the Catalog in the SSMS:

You can create only one catalog per SQL Server instance. The database is always called SSISDB. It would be great if this could be changed in the future release of Denali. This would create flexibility in corporate naming convention.

It's possible to create multiple environments and according to the Technet wiki  an environment is a container of variables that can be referenced by Integration Services projects. Each project can have multiple environment references, but a single instance of package execution can only reference variables from a single environment. Environments allow you to organize the values that you assign to a package. For example, you might have environments named "Dev", "Test", and "Production". Something like this:

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. Okay lets build this thing! An..ispac file is created in the bin folder.

So how do deploy your solution and packages into the catalog. Well there is a deployment option in BIDS. Right click on the project and choose 'deploy'. You have to walktrhough the wizard and following screens will appear:

Choose the deployment file, that is created during the build, in the bin folder. When the project is deployed for the first time the first option should be choosed. The second time you can take the catalog deployment.

Choose integration services project where the deployment should take place. The message below indicates that project already exist ( i deployed it earlier)

 

In this window you can choose the parameters for the project. I'll look into this in the future.

Press next and following window showed that everything was deployed succesfully.

 The review window:

And now you can see that the project is deployed in the SSMS tree in the object explorer.

Conclusions:

• I have enabled the CLR because the catalog uses CLR functions.
• I have created the catalog.
• I understand what environments are.
• I have created a couple of environments in SSMS.
• I have deployed a project from BIDS to SSMS catalog.

Greetz,
Hennie