title: How to ensure consistency with multiple SSIS-packages filling the same tables?
body: <p>I have a SSIS package with 2 data flow tasks. The first data flow task is filling values into a dimension table. The second data flow task is filling surrogate keys into the fact table. The fact table is referencing the previous filled dimension table via surrogate key. However, a further SSIS package is doing exactly the same, but with data from another data source. Both SSIS packages are fired by SQLServer Agent in low frequency (each 20 - 40 seconds).
  I am worrying about consistency. If I had a single SSIS package that loads the data into the dimension table and fact table, I wouln't have to because it would be possible to create the control flow to enforce the following sequence:</p>
  
  <ol>
  <li>Fill the Dimension table with data from data source 1</li>
  <li>Fill the Fact table with data from data source 1 (correct surrogate key to Dim)</li>
  <li>Fill the Dimension table with data from data source 2</li>
  <li>Fill the Fact table with data from data source 2 (correct surrogate key to Dim)</li>
  </ol>
  
  <p>So in this case the primary key of the Dimension table as well as the corresponding surrogate key in the fact table could be auto-incremented simply in SQL Server DB and everything would be fine. 
  But since I have 2 SSIS packages, each running independently on a multi-core ETL server in low frequency, I am worrying about the case when the following will happen:</p>
  
  <ol>
  <li>Both packages are starting approximately at the same time</li>
  <li>Fill the Dimension table with data from data source 1</li>
  <li>Fill the Dimension table with data from data source 2</li>
  <li>Fill the Fact table with data from data source 2 (surrogate key to wrong Dim record)</li>
  <li>Fill the Fact table with data from data source 1 (surrogate key to wrong Dim record)</li>
  </ol>
  
  <p>Are there any common best practises or, on the other hand, is such a handling necessary or does SQL Server handle such situation by default e.g. by forbid packages to be processed in parallel? Maybe a <code>Write Lock</code> on both tables during the start of each SSIS-package could be satisfactory but in this case I am worrying that this could result in a failure thrown by the other SSIS-package if it cannot reach the destination tables. I am new to SSIS and I would like to know my options about any good techniques to avoid this situation (if necessary).</p>