title: SSIS - best practices for connection managers -- compose out of parameters?
body: <p>I've worked a lot with Pentaho PDI so some obvious things jump out at me.</p>
  
  <p>I'll call Connection Managers "CMs" from here on out.</p>
  
  <p>Obvious, Project CMs > Package CMs, for extensability/ re-usability. Seems a rare case indeed where you need a Package-level CM.</p>
  
  <p>But I'm wondering another best practice. Should each Project CM itself be composed of variables? (or parameters I guess).</p>
  
  <p>Let's talk in concrete terms. There are specific database sources. Let's call two of them in use Finance2000 and ETL_Log_db. These have specific connection strings (password, source, etc).</p>
  
  <p>Now if you have 50 packages pulling from Finance2000 and also using ETL_Log_db ... well ... what happens if the databases change? (host, name, user, password?)</p>
  
  <p>Say it's now Finance3000.</p>
  
  <p>Well I guess you can go into Finance2000 and change the source, specs, and even the name itself --- everything should work then, right?</p>
  
  <p>Or should you simply build a project level database called "FinanceX" or whatever and make it comprised of parameters so the connectoin string is something like @Source + @ credentials + @ whatever?</p>
  
  <p>Or is that simply redundant?</p>
  
  <p>I can see one benefit of the parameter method is that you can change the "logging database" on the fly even within the package itself during execution, instead of passing parameters merely at runtime. I think. I don't know. I don't have a mountain of experience with SSIS yet.</p>