Foreach Loop Container For Data Flow Task

Question

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

Solution

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.

Comments

[Pratik]

Thanks Praveen. I think should work. I am modifying my package as per your suggestion

[rvphx]

I dont think this is a good idea unless you want to store all the 12 million records in memory.

[praveen]

Here im not storing the records in the memory .In the question OP has a problem with the SSIS memory so he is going in for FOREACH loop to process a set of records at a time

[Pratik]

@Rajiv: Have you ever encountered such situation to import large number of records.Do you think there is scope to the approach.

[rvphx]

The approach to loading 12 million records using this method is more of un-necessary work. Whats the logic behind loading each month/year at a time? SSIS will process only the amount of data that it can hold in its buffer. If it cannot, it doesnt mean that it will fail. It would just wait until the buffer gets cleared out so that it can load new data. Why do you think using a for-each loop container is efficient'

[praveen]

@RajivVarma : Please tell me a better method to resolve this issue .I will be more happy to learn about processing such high amount of data

[rvphx]

Why cant you have a simple DFT to that job for you? Whats the need for a for each loop? I am at a loss to understand the logic behind processing data year by year. There is no uniform distribution of the recordset over the years. So how would you justify using a for each loop container? If there's uniform distribution then it would mean approximately 1.71 million records each year. Is the OP sure of this? When he says SSIS memory buffer problem, what exactly does he mean?

[rvphx]

@PratikGupta I have created jobs which process approximately a million records in a single ETL cycle and I never encountered "SSIS Memory buffer problem". I am very curious to know about the buffer problem you guys are trying to solve.

[praveen]

SSIS process data in the pipeline wthin memory buffers .They have default values of 10,000 and 10,485,760 rows .Tasks like lookup and merge are all done in memory . So in case if the number of rows increases beyond the limit then we need to change the properties like DefaultBufferMaxRows to increase the performance .I have encountered errors like Buffer overflows sometimes.

[praveen]

One of the best practices in designing ssis as mentioned in msdn is "Any opportunity that you have to reduce the size of the source data set before operations begin saves memory resources" .This is wat i tried to achieve by getting chunks of data using foreach instead of processing the entire data into memory .I suggest you go through the articles to understand SSIS memory related problems and how to resolve them .technet.microsoft.com/en-gb/library/cc966529.aspx technet.microsoft.com/library/Cc966529

[Pratik]

Thanks Rajiv and Praveen for this discussion. The error while loading huge data is [DTS.Pipeline] Information: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. But i ran the same package on server where RAM is 8GB but same error everytime.

[Kumar Vaibhav]

This answer saved my day. I can explain where such a situation might be needed. Say you have to insert in a table using a select query but say the select query is so expensive that's it's unable to return a row to you for hours! So you decide to do select chunks of data using some where clause and then insert it. but the wheres might be uneven. it's better to do further batch the inserts - the purpose of such a question. in my case it was about 110 million records!

[Gerardo]

@praveen I have similar issue with my SSIS can you help me ?