4

What is the 'best practices' way to configure connections in SSIS 2012 project (that will be deployed to the server)? Documentation and Google shows multiple ways to accomplish this, but which way takes full advantage of the 2012 project/deployment model and is easily customizable, maintainable etc?

Consider a project (NorthwindETL) with three packages with each package referencing localhost.Northwind. On SSIS server, there is a project 'NorthwindETL', and an Environment 'Dev'.

To configure the connection, I have the following options

  1. IN BIDS: Hard code the connection (via connection manager) manually in each package. IN SSIS: Configure the SSISDB 'NorthwindETL' project, under the connection manager tab, modify the Northwind connection string, once for each package.
  2. IN BIDS: Using the Connection Manager 'Parameterize…' option, create a project (or package) parameter to specify the connection string (Northwind_Conn). IN SSIS: Configure the SSISDB 'NorthwindETL' project, specify the Northwind_Conn parameter value.
  3. IN BIDS: Create a project level Connection Manager (Project_Northwind_Conn). IN SSIS: Configure the Configure the SSISDB 'NorthwindETL' project, under the connection manager tab, modify the 'Project_Northwind_Conn' connection string.
  4. IN SSIS: Create an Environment on SSISDB called 'DEV'. In the 'Dev' environment properties, under variables, create a variable 'Env_Northwind_Conn'. Configure the NorthwindETL project, set 'Northwind_Conn' to the environmental variable 'Env_Nothwind_Conn'

(Also, I would prefer a solution that allows us to specify items separately such as InitialCatalog and Server, but this is not necessary. Although the connection manager allows you to modify the InitialCatalog and Server properties, this does not seem to actually modify the ConnectionString.)

davewilliams459
  • 1,679
  • 2
  • 15
  • 24

1 Answers1

4

For connection managers that are to be used across all the packages, typically database connections, the 2012 release gives us Project Connection Manager (option 3). I find that to be the most enjoyable for connection managers as when I go to apply configuration, I'm applying it once at the project level instead of once per package.

Unless you run your dev/test/prod ETL from the same dedicated server, I am not a fan of naming my SSIS Environment Variables as such. The primary reason is that my deployment script then has to have the intelligence built into it to not only switch server names per tier, but also the environment name. Just makes more opportunity for the dumb to enter.

I'm also a fan of just creating an empty Folder within the SSISDB, call it Configs and then establish my SSIS Environment variables there. All projects then reference that folder's variables. At my clients, it's generally been the case that they're all referencing the same Sales database so it seems like more work for me to have to maintain N configurations to satisfy N projects instead of having a single shared configuration repository.

Script everything. Click the scroll icon as you create and assign your configurations. It's going to make it a far easier row to hoe when you need to migrate from one environment to the next.

billinkc
  • 59,250
  • 9
  • 102
  • 159
  • if you don't mind clarifying your third paragraph, are you talking about a file system folder (for a config file) or an SSISDB folder? And if the latter, do you use actual environment variables, or do you use project variables? – Tab Alleman Oct 05 '17 at 15:04
  • SSISDB for all of my answer. The Environment Variable I mention is the SSISDB based one and not a Windows one. Unfortunate choice of terminology on the part of the teams there. For Connection managers, I no longer take the approach of creating package parameters and then use Expressions on them to make my packages portable across environments. Instead, the catalog allows you to configure those properties directly. – billinkc Oct 06 '17 at 00:51
  • Wow, this is cool! I know this is from a while back but this looks like the exact thing I am trying to do - server-level config. I am going to try to read up more on this but would really appreciate if you have a moment to point me to a link in the right direction. – RiSt Feb 22 '21 at 20:33