title: SSIS Woes: Dynamic OData Connection for Same SharePoint List on Multiple Sites
body: <p>I am having an issue using an <code>OData</code> SharePoint List Source with a dynamically changing connection string (inside the <code>OData</code> Connection Manager). The OData Source inside of my Data Flow Task fails to validate with the error message, “Cannot acquire a managed connection from the run-time connection manager” when executing the DFT from a parent package. </p>
  
  <p>I have done some extensive Googling, and combed the forums relentlessly; however, I have not found anything that seems to offer a solution to this problem. Any help figuring out a solution would be greatly appreciated!</p>
  
  <p>Here is the general flow of the main SSIS package:</p>
  
  <ol>
  <li>Truncate staging table</li>
  <li>Get all Site Collection URLs and their GUIDs from SQL Table</li>
  <li>Execute Package Task for each site collection (foreach ADO loop container)
  
  <ol>
  <li>Extract data from UserInformationList (OData source)</li>
  <li>Add a column for the GUID of this site collection</li>
  <li>Load the data into staging table</li>
  </ol></li>
  <li>etc. . .</li>
  </ol>
  
  <p>Main Package:
  <img src="https://i.stack.imgur.com/RFuZt.png" alt="SSIS Main Package Control Flow"></p>
  
  <p>Child Package Control FLow:
  <img src="https://i.stack.imgur.com/3JVwN.png" alt="SSIS UserInformationList Package Control Flow"></p>
  
  <p>E-L UserInformationList DFT:
  <img src="https://i.stack.imgur.com/beMg3.png" alt="SSIS UserInformationList Data Flow Task"></p>
  
  <p>Package Output With Error Message:
  <img src="https://i.stack.imgur.com/75d2O.png" alt="SSIS Package Output Containing Error Message"></p>
  
  <p>When testing the entire solution, everything (tasks, parameters, variables, etc.) behaves properly until 3.1(see above), when the <code>OData</code> Source fails during validation. The only aspects of the source and connection manager that change are the URL and ConnectionString for the connection manager; the specific SharePoint list that I access on each site never changes. When the solution enters the child package, the URL and ConnectionString for the Connection Manager are properly set, prior to entering the DFT. </p>
  
  <p>When testing the child package via the Execute Package Task, using hard-coded parameter values, the child package fails to validate.</p>
  
  <p>When testing just the child package, there are no errors and the list information is stored in the database, as expected. However, with individual testing, the <code>OData</code> Connection Manager uses the default value of the package parameters.</p>
  
  <p>Things I have tried so far:</p>
  
  <ul>
  <li>Set <code>DelayValidation</code> to True</li>
  <li>Changing the debugging runtime from 64 to 32 bit (and back again) <br></li>
  <li>Use collection to specify the list (in the <code>OData</code> Source Editor) <br></li>
  <li>Use resource path to specify the list (in the <code>OData</code> Source Editor) <br></li>
  <li>Running the child package as a Farm Admin <br></li>
  <li>Running the solution as Farm Admin <br></li>
  </ul>
  
  <p>Other information:</p>
  
  <ul>
  <li>SharePoint 2013</li>
  <li>Data Tools for Visual Studios 2012</li>
  <li>Microsoft’s <code>OData</code> Source for SQL Server 2012</li>
  </ul>