Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>The best way in my opinion is to functionally partition your data. A date column is in most cases appropriate to do this. Let's take an order date as an example.</p> <p>For that column, find the best denominator, for example each year of your order date produces about a million rows.</p> <p>Instead of a for each loop container, use a <a href="http://msdn.microsoft.com/en-us/library/ms139956.aspx" rel="nofollow">for loop container</a>. </p> <p>To make this loop work, you'll have to find the minimum and maximum year of all order dates in your source data. These can be retrieved with SQL statements that save their scalar result into SSIS variables.</p> <p>Next, set up your for loop container to loop between the minimum and maximum year that you stored in variables earlier, adding one year per iteration.</p> <p>Lastly, to actually retrieve your data, you'll have to save your source SQL statement as an expression in a variable with a where clause that specifies the current year of produced by your for loop container:</p> <pre><code>"SELECT * FROM transactions WHERE YEAR(OrderDate) = " + @[User::ForLoopCurrentYear] </code></pre> <p>Now you can use this variable in a data flow source to retrieve your partitioned data.</p> <p><strong>Edit:</strong> </p> <p>A different solution using a for each loop container would be to retrieve your partition keys with a Execute SQL Task and saving that resultset in a SSIS variable of type <code>Object</code>:</p> <pre><code>SELECT YEAR(OrderDate) FROM transaction GROUP BY YEAR(OrderDate) </code></pre> <p>With a for each loop container you can loop through the object using the ADO enumerator and use the same method as above to inject the current year into your source SQL statement.</p>
    singulars
    1. This table or related slice is empty.
    1. POHow to use for each loop to help load large dataset
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USJeroen Bolle
    1. USJeroen Bolle
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POHow to use for each loop to help load large dataset
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COthx for your answer! I need to merge the selected data each time with some other tables, that's why I have to use for each loop. Otherwise, it would be even more slower to do the processing.
      singulars
      1. PO
      1. USEcho
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload