Foreach Loop Container For Data Flow Task

问题:

I want to import nearly 12 million records from oracle DB but beacuse of the SSIS memory buffer problem i need to iterate my Data Flow Task for each year i.e 2005 to 2012 (7 times). How can i use foreach my oracle query to fetch the data year wise.

Query

SELECT * FROM EMP_RECORDS_DETAILS WHERE to_char(JOIN_DT,'YYYY')=2005

回答1:

I have solved the above problem using Sql Server . So only thing which you need to change is the Select query

1. First Create a variable to store the date for each year .

    Name: TimeValue  DataType=Int32
   

2. Use a ForEach Loop and select Foreach Item enumerator in the collection tab

3. Click on the Columns tab and create a new column of datatype int

4. In the variable mapping map the variable created above .

5. Inside the dataflow use an oledb destination and in the data access mode select sql command .The query in the where clause should be changed and try to select the columns instead of giving *.

6. Click on the parameters tab map the parameter created for the foreach loop

In case if the values of date are not known before then you can use execute sql task before foreach loop and select only the dateTime column values from the table and store it in a variable and in the foreach loop use Foreach from variable enumerator in the collection tab and select the variable created in the above execute sql task .

Hope this helps u.