How do you use configurations in the new Project Deployment Model of SSIS 2012? The package configuration menu item is not available in the SSIS menu.
Solution
The package configurations are hidden because configuring a package is a little different in the 2012 Project Deployment Model. But if you open a package and click on the properties of the package, then you will find a Configurations property under miscellanies (see image below). For this example we are not using the old package configurations, but we are using parameters and server environments. I will create two configurations (=environments). One for running with a small dataset and one for a large dataset. Same can be done for a Development Environment and a Test Environment on the same machine.
 |
| Old package configurations |
1) Starting point
For this example I created a very basic staging package with a Flat File source and an OLE DB destination. I have created a package parameter and used it to parameterize the connectionstring of the Flat File Connection Manager. (Other items in this example are ignored for configuration)
 |
| Basic package with a parameterized Flat File Connectionmanager |
2) Deploy to SSISDB catalog
I have deployed my project (and its package) to the SSISDB by right clicking the project in the Solution Explorer and selecting Deploy.
 |
| Deploying the project |
The result in Management Studio:
 |
| The project in SSISDB catalog |
3) Create environment
We need to create an environment. You can compare an environment to a configuration file. First I will create an environment for the small dataset. Right click Environments and choose Create Environment. Fill in a name and description.
 |
| New environment |
4) Environment variables
In the environment we need to create a variable to store the path of the source file. Right click the newly created environment and select Properties. Go to the Variables pane and add a String variable for the filepath and enter a value.
 |
| Environment variables |
5) Connect environment to package
We now need to reference the newly created environment to the package. Right click the package and choose Configure. Then go to the references pane and add the environment to the package. You can either choose a relative path or an absolute path.
 |
| Referencing environment to package |
6) Connect variable to parameter
Next step is to reference the environment variable to the package parameter. This can be done in the Parameters pane of the same configuration screen.
 |
| Referencing variable to parameter |
7) Second environment
Repeat steps 3 to 6 for a second environment called Large dataset. Or use my Copy Environments stored procedure.
8) Select environment and execute package
Now you can choose between your two environments when executing a package. For this you need to check the Environment checkbox at the bottom of the Execute Package windows. You will see both environments in the list.
 |
| Choose environment and execute package |
Thanks for the post. I had not played around with doing this until just yesterday and I thought that maybe someone else had to have done this. So I did a quick google search and came upon this. Using your write-up, I was able to get my project configured and running in multiple environments in a matter of minutes. Much appreciated!
ReplyDeleteThanks! Also checkout how you could do this even faster with PowerShell. Here are a couple of scripts: http://microsoft-ssis.blogspot.com/2016/10/using-powershell-for-ssis.html
Delete