title: SQL Server 2014, SSDT : deploying .dtsx packages to live, problems with connection managers & variables
body: <p><strong>Summary:</strong> A "master" database houses a set of connection strings. A <code>.dtsConfig</code> XML file is used to point the packages to this database. Dynamic connection assignment accomplished by using package variables and expressions on the connections. Works flawlessly in the development environment, but once deployed to live it falls over.</p>
  
  <p>I'm currently running into issues when deploying <code>.dtsx</code> packages to a production environment. The issue is specifically related to the connection manager when the jobs run. The history log reports a <code>... network ...</code> error with a <code>Login timeout</code> error as the reason.</p>
  
  <p>(For reference, I'm using Visual Studio 2013 with SQL Server Data Tools)</p>
  
  <ol>
  <li><p>I have a table in a master database that holds the connection strings that it needs to process.</p></li>
  <li><p>The packages check for a configuration file, that points them to the database as stipulated in (1).</p></li>
  <li><p>The connections are retrieved and are placed in an object variable.</p></li>
  <li><p>The variable is mapped to a <code>foreach</code> loop container, where a set of connection string variables are mapped to the relevant columns.</p></li>
  <li><p>The packages then progress as normal.</p></li>
  </ol>
  
  <p>Some notes:</p>
  
  <ol>
  <li><p>When I did the development, I provided default values on my network for the connection strings.</p></li>
  <li><p>I have checked the connection string parameters and formatting inside the database, and they conform to Microsoft's specification.</p></li>
  <li><p>Our implementers installed SSDT on a client's QA server, where I altered the connection variables to point to their network. This solved the problem, but it is not sustainable (in my mind at least).</p></li>
  </ol>
  
  <p><strong>So my question is:</strong> how do I get my production deployments to work correctly with dynamic connection management assignment without having to alter the connection string variables inside each package on a per client basis?</p>
  
  <p>Any help will be appreciated.</p>