SSIS : Setting a database connection with parameters in Denali

Parameters is a new feature in Denali and they are a replacement of package configurations. Package configurations will stay in Denali but now there is another approach available. They are a fundamental new way of how SSIS will handle, manage and execute packages  inside the catalog.  They look al lot like variables but they are slightly different. There are two types of variables: Project- and package parameters. For this blog w'll use the projectparameters.

1. First w'll create a demopackage in SSIS. In this package i've dragged an Execute SQL Task on the control flow and named it something like this:

 I created an connection and added the following SQL code at the Execute SQL task.

CREATE TABLE YES(
    [YES] [int]
) ON [PRIMARY]

And i added an project parameter to the project:

Assigned the project parameter to the connection in the expression builder:

2. I created four databases in SSMS (for this demo we won't be using them all, just Dev and Test):

3. Now let's deploy the package at the Catalog. First build it and then Deploy this project. Below you can see that the parameter is deployed together with the project.

 

 4. Okay now lets run SSMS. and go the catalog and now the project/solution and the package is deployed in the catalog:

 5. Add the variable (now its called a variable) parCONN to the environment properties of the Dev environment (in the snippet below you can see parConn; i changed it later to parCONN. At this moment i'm not sure if it's case sensitive)
 

 
 6.  Add the variable (now its called a variable) parConn to the environment properties of the Test environment (in the snippet below you can see parConn; i changed it later to parCONN. At this moment i'm not sure if it's case sensitive).

 7. Add references to the project properties:

 8.  The next step is to set the parameter value with the environment variable that is created earlier (in the snippet below you can see parConn; i changed it later to parCONN. At this moment i'm not sure if it's case sensitive)

9. We are are now ready to test this package. So when i run this package against the Dev environment the table should be created in the Dev environment. Let's check this first:

And the results are shown below:

And the table is created in the Dev database:

10. The next step is to check out whether the package created the table 'YES' in the Test database. Lets found out.

Running the package will show the following details:

 

And here are the results. The table is created in the Test database!!

Conclusion:
So this is an example of setting a connection with parameters and enviroments. We have created an projectparameter in the package, created environments, pointed the projects to the enviroments and finally i ran the packages against two environments.

Greetz,
Hennie