title: SSIS amount of insert operations based on record value
body: <p>I'm migrating data from an old database to a new one in SSIS (2008 R2 Enterprise Edition). In the old database, I have a table called <code>[Financial]</code> and a column named: <code>[Installments]</code>. This column has a numeric value in it: 1, 2, 3 or 4. These are payments in installments. The old database only stores this numeric value and do not provide any more information about individual installments. The new database, however, provide more information of each installment, with columns like: <code>[InstallmentPaid]</code> (if the customer paid the installment), <code>[DateInstallmentPaid]</code> (when the customer paid the installment), <code>[InstallmentNumber]</code> (this is important to specify which installmentnumber it is. If the customer wants to pay in 4 installments, then 4 records will be created. 1 with InstallmentNr1, second with InstallmentNr2, third with InstallmentNr3 and fourth with InstallmentNr4.) and of course the <code>[InstallmentPrice]</code>. </p>
  
  <p>So the old database has the table <code>[Financial]</code> with the column <code>[Installments]</code>. The new database has the same <code>[Financial]</code> table, but instead of having a column called <code>[Installments]</code>, it has a new relationship called <code>[CustInstallments]</code> (<code>[CustInstallments]</code> has <code>FK FinancialID</code> (1-to-many relationship)</p>
  
  <p>So now that I'm migrating the data from the old database to the new one, I don't want to lose the information about the amount of installments. The following logic should  be executed in SSIS in order to prevent information loss:</p>
  
  <blockquote>
    <p>Foreach [Installments] in [Financial] from the old database, insert a
    new [CustInstallment] referencing to the corresponding [FinancialID]
    within the new database</p>
  </blockquote>
  
  <p>So if in the old database the numeric value within [Installments] is 3, then I need to <code>INSERT INTO CustInstallments (FinancialID, InstallmentNumber) VALUES (?, ?)</code> This <code>?</code> should be 1 at the first insert, 2 and the 2nd and 3 at the 3rd. So I need some kind of a loop here? Is that even possible within the data flow of SSIS?</p>
  
  <p>Below the visualization (figure) and description of my flow so far.</p>
  
  <ol>
  <li>I select the old database source <code>[Financial]</code></li>
  <li>I convert the data so it matches the current database data types</li>
  <li>Since I already migrated the old <code>[Financial]</code> database data to the new one, I can use the lookup on the <code>FinancialID's</code> in the new database, so the first variable <code>?</code> of the INSERT query can be linked to the lookup output.</li>
  <li>I split all the possibilities, like when the Installment contains NULL, 1, 2, 3 or 4.</li>
  <li>The 5th step is what I'm looking for. Some clue, some direction towards something useful. When NumberOfInstallments is 1, I need to <code>INSERT INTO CustInstallments (FinancialID, InstallmentNumber) VALUES (?, ?)</code> with the second <code>?</code> variable as 1. When the NumberOfInstallments are 2, then I need to do two inserts, one with InstallmentNumber 1, and one with InstallmentNumber 2. When NumberOfInstallmentNumber is 3, then 3 inserts with a counting NumberOfInstallmentNumber. When 4, then four. </li>
  </ol>
  
  <p>Is there any smart way to achieve this? Is there any built-in function available of SSIS that I am not aware of, and could be used here?</p>
  
  <p>I appreciate any input here!</p>
  
  <p>Thank you.</p>
  
  <p><img src="https://i.stack.imgur.com/sy8bW.png" alt="enter image description here"></p>
  
  <p><strong>EDIT 10/02/2014</strong></p>
  
  <p>I have tried the following code:</p>
  
  <pre><code> INSERT INTO CustInstallments (FinancialID, InstallmentNumber) values (?, X);
   WITH nums AS(select top 4 row_number() over (order by (select 1)) AS id
                   from sys.columns
                 ) SELECT f.* FROM CustInstallments f
    JOIN nums n on f.InstallmentNumber&gt;= n.id
  </code></pre>
  
  <p>But this query doesn't create X-amount of records, instead, the JOIN nums just replicates it X-amount of times, so I still can't track every installment individually.</p>
  
  <p>I have written my own code - toke me a while since I never worked with TSQL before - and this works like a charm in SQL Server:</p>
  
  <pre><code>DECLARE @MyCounter tinyint;
  SET @MyCounter = 1;                
  WHILE (SELECT COUNT(*) FROM CustInstallments WHERE FinancialID = #ID) &lt; 4
      BEGIN
         INSERT INTO CustInstallments (FinancialID, InstallmentNumber) VALUES (#ID, @MyCounter)
             IF (SELECT COUNT(*) FROM CustInstallments) &gt; 4
                BREAK
             ELSE
             SET @MyCounter = @MyCounter +1;
                CONTINUE
      END
  </code></pre>
  
  <p>Now in SSIS, I cannot change the #ID to a <code>?</code>-variable, and use the lookup FinancialID, because as soon as I do, I get the following error:</p>
  
  <p><img src="https://i.stack.imgur.com/fIbPA.png" alt="enter image description here"></p>
  
  <p>Could anyone explain me why SSIS doesn't like this?</p>
  
  <p><strong>EDIT 10/02/2014</strong></p>
  
  <p>My last and least preferable option would be to use multicast to cast an insert query X amount of times, where each X is an OLE DB Command. For example, when there are 3 [Installments] in the old column, I would create a multicast with 3 OLE DB commands, with their <code>SqlCommand</code>:</p>
  
  <pre><code>OLE DB Command 1: INSERT INTO CustInstallments (FinancialID, InstallmentNumber) values (?, 1);
  
  OLE DB Command 2: INSERT INTO CustInstallments (FinancialID, InstallmentNumber) values (?, 2);
  
  OLE DB Command 3: INSERT INTO CustInstallments (FinancialID, InstallmentNumber) values (?, 3);
  </code></pre>
  
  <p>This is an ugly approach, but with the small amount of data I am using, perhaps it's not a big deal.</p>
  
  <p><img src="https://i.stack.imgur.com/IPqWC.png" alt="enter image description here"></p>