SSIS 2012 is the best template for configuring connections between projects and packages
What is the best way to set up connections in an SSIS 2012 project (which will be deployed to the server)? The docs and google show several ways to achieve this, but which way takes full advantage of the 2012 project / deployment model and is easily customizable, supported, etc.?
Consider a project (NorthwindETL) with three packages, with each package referencing localhost.Northwind. The SSIS server has a NorthwindETL project and a Dev environment.
To set up a connection I have the following options
- IN BIDS : Hardcode the connection (via the connection manager) manually in each package. In SSIS . Configure SSISDB project "NorthwindETL" on the Connection Manager tab, change the Northwind connection string once for each package.
- BIDS . Using the Parameterize ... parameter, create a project parameter (or package) to specify the connection string (Northwind_Conn). In SSIS . Configure SSISDB project "NorthwindETL", specify a value for the Northwind_Conn parameter.
- BIDS . Create a project-level connection manager (Project_Northwind_Conn). In SSIS . Configure SSISDB project "NorthwindETL" in the Connection Manager tab, change the connection string "Project_Northwind_Conn".
- In SSIS : Create an SSISDB environment called "DEV". In the properties of the "Dev" property, under the variables, create the "Env_Northwind_Conn" variable. Configure the NorthwindETL project, set "Northwind_Conn" to the "Env_Nothwind_Conn" environment variable
(Also, I would prefer a solution that allows us to specify items separately, such as InitialCatalog and Server, but this is optional. While the Connection Manager allows you to change the InitialCatalog and Server properties, this does not appear to change the ConnectionString.)
source to share
For connection managers to be used in all packages, usually to connect to databases, the 2012 release gives us the Project Connection Manager (option 3). I find this would be very nice for connection managers, since when I go into configuration, I apply it once at the project level, not once for each package.
Unless you are running the dev / test / prod ETL file from the same dedicated server, I am not a fan of naming my SSIS environment variables as such. The main reason is that my deployment script then needs to have built-in intelligence to not only switch server names one level, but also the environment name. It just makes more room for the dumb to enter.
I'm also a fan of creating an empty folder in SSISDB, calling it Configs, and then setting SSIS environment variables there. Then all projects refer to folder variables. My clients used to have it so that they all reference the same Sales database, so it seems to me that I need to maintain N configurations more to cater for N projects, instead of a single repository of common configuration.
Script everything. Click the scroll icon while creating and assign your configurations. This will make it much easier than urine when you need to move from one environment to another.
source to share