Feb 7, 2013

SSIS - Foreach Loop Enumerators


A lot of times while taking interview I feel that most people know of 2 enumerators used in Foreach Loop Container and they are “Foreach File Enumerator” and “Foreach ADO Enumerator”
So lets talk about the enumerators available in SSIS. Unless we know them we will not be able to implement them right?

What are the different types of enumerators available in SSIS Foreach Loop Container?
  1. Foreach File Enumerator
  2. Foreach Item Enumerator
  3. Foreach ADO Enumerator
  4. Foreach ADO.NET Schema Rowset Enumerator
  5. Foreach From Variable Enumerator
  6. Foreach NodeList Enumerator
  7. Foreach SMO Enumerator

SSIS - Split single row to Multiple rows


Its been a long long time since I have blogged. So have I been away from forums hence no ideas to blog. I was back at forum today and got a nice scenario. How do you split a single row to multiple rows? The input file has only one row.
There can be 2 scenarios which arise:

Scenario 1. the number of columns expected for each output row are the same.

Input: 1*1*1~2*2*2~3*3*3~4*4*4
Treat “~” as the row delimiter and * as the pipe delimiter

Expected Output:
111
222
333
444

Scenario 2. The number of rows for each output row are different. We need to default values if we have less than expected columns and ignore the extra columns.

Input:
1*1*1*1~2*2~3*3*3*3*3*3*3~4*4
Treat “~” as the row delimiter and * as the pipe delimiter

Expected Output:
111
22
333
44

Notice the blanks in the 3rd column for 2nd and 4th row.

Continue reading here @ BeyondRelational.com.