title: Parametrizing SSIS connection expression to DB2 in Integration Services Catalog environment
body: <p>I have simple package, which extracts data from DB2 to SQL Server database. DB2 connection uses Microsoft OLE DB Provider for DB2 driver. DB connection string is made as an expression, because solution will have to be deployed to different environments of Integration Services Catalogs (further ICS) where different configurations will be made. For example in TEST environment TestUser will be configured to connect to DB2 test database, and so on with Acceptance and Prod environments.</p>
  
  <p>Solution's ProtectionLevel is set as <em>DontSaveSensitive</em>.<br>
  Solution's variables for the connection are declared in project parameters. Password is set as <em>Sensitive</em>, so value is not stored in package and has to be provided by ICS Environment Properties where password value is stored as <em>Sensitive</em> as well.</p>
  
  <p>Connection's expression:</p>
  
  <pre><code>"Data Source=" + @[$Project::DB2DataSource] + ";User ID=" + @[$Project::DB2UserId]+ ";Initial Catalog=" + @[$Project::DB2InitialCatalog] + ";Provider=DB2OLEDB.1;Persist Security Info=True;Network Address=" + @[$Project::DB2NetworkAddress] + ";Network Port=" + @[$Project::DB2NetworkPort] + ";Package Collection=NULLID;"
  </code></pre>
  
  <p>Which evaluates to:</p>
  
  <pre><code>Data Source=DB2test;User ID=myUser;Initial Catalog=​DB2test;Provider=DB2OLEDB.1;Persist Security Info=True;Network Address=DB2test;Network Port=99999;Package Collection=NULLID;
  </code></pre>
  
  <p>Password is provided separately in DB2 Connections properties as Password expression. I've separated it from ConnectionString, because password is set as <em>Sensitive</em>, so ConnectionString expression fails to evaluate, picture:
  <a href="https://i.stack.imgur.com/sFxbl.png" rel="nofollow noreferrer">DB2 Connection properties</a>
  Well, at least I want it to work like that. While developing, I'm changing password as not Sensitive, so I could test the flow via Visual Studio and it works. When deploying to ICS I change password to Sensitive, because ICS environment password variable is set to Sensitive and statuses has to match both in package and ICS environment.</p>
  
  <p>And in ICS flow is not working, I'm always getting some connection errors. DB2 connection variables in ICS env are the same like in solution properties, so values are not the reason of failure. Example of one run errors:</p>
  
  <blockquote>
    <p>Error: SSIS Error Code
    DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The
    AcquireConnection method call to the connection manager "DB2_user"
    failed with error code 0xC0202009.  There may be error messages posted
    before this with more information on why the AcquireConnection method
    call failed.</p>
    
    <p>Extract_DB2_Db2PasyIsoExtract:Error: SSIS Error Code DTS_E_OLEDBERROR.
    An OLE DB error has occurred. Error code: 0x80040E73. An OLE DB record
    is available.  Source: "Microsoft OLE DB Service Components"  Hresult:
    0x80040E73  Description: "Format of the initialization string does not
    conform to the OLE DB specification.".</p>
  </blockquote>
  
  <p>So it looks like there's something with connection string. Maybe password not passed properly? I didn't find proper way how to debug generated ConnectionString via ICS.<br>
  Maybe there are some security settings which of I should be aware of? Other ProtectionLevel options are not possible to due specific deployment process. Any comments or approach suggestions are welcome!</p>