Showing posts with label For Each Loop Container. Show all posts
Showing posts with label For Each Loop Container. Show all posts

April 6, 2010

SQL SERVER SSIS: Get File Name with Flat File Source, Data Flow Component

As I explained earlier about For Each Loop Container, which process each file from selected folder.

There is a requirement to save File name along with record, so later on we can identify which record comes from which file.

Let me explain it how to achieve with FileNameColumnName property of Flat File Connection to get it easily.

1. Setup Flat File Connection with CSV file, as mentioned Basic Example of Data Flow Task.
2. Right click on Flat File Source, and click on Show Advanced Editor:
Derived FileName Show Advanced Editor
3. Click on "Component Properties" and go to FileNameColumnName, Custom properties:
  Derived FileName Component Properties

4. Setup FileNameColumnName value with desired column name. Let's say "File Name". Congratulations, This column is added to output list with actual filename of that connection.

Let me know your comments/questions.

January 10, 2010

SQL SERVER: SSIS - Derived Column Data Flow Transformation

As I explained earlier about Foreach Loop Container. One of regular reader of blog send me an email about one issue.

Let me share that problem with all readers.

With this example, Foreach Loop Container, What to do if we want to save file name along with each row, so we can come to know that which row is from which file ?

This is very practical problem that we need to fix.

To solve this, I come up with following solution.

1. I used "Derived Column", one of Data Flow Transformations in Data Flow Operations.

 SSIS For Each Loop Container II 

2. Configure Derived Column:

SSIS Derived Column Data Flow Transformation Configuration 
As we have variable, FileName, as defined in, SQL SERVER: SSIS - Foreach Loop Container. Here I used that variable as a new column. By dragging  that User variable to Expression.

By default it assign UNICODE STRING DataType to this new column. We need to change it by:

A. Right click on "Derived Column", Go to Show Advanced Editor
B. Set DataType to String as:

SSIS For Each Loop Container II Advanced Editor

3. That's it. Now just add it to Destination Column Mapping with your Database column.

Let me know your suggestions.


November 9, 2009

SQL SERVER: SSIS - Foreach Loop Container

Today, I am going to explain SSIS For Each Loop Task. I am sure that it will be easy to configure SSIS Package with Foreach Loop task, after you read this article.

The Foreach Loop container defines a repeating control flow in a package. The loop implementation is similar to Foreach looping structure in programming languages. In a package, looping is enabled by using a Foreach enumerator. The Foreach Loop container repeats the control flow for each member of a specified enumerator.
Many times, user asks that I want to process all files from my folder to database. This is my answer to all of those users. SSIS comes up with For Each Loop which did the same.
Now I'm assuming that you're familiar with using the SQL Dev Studio tools and building basic packages. If this isn't the case, I recommend working your way through the Integration Services using my earlier posts.

SSIS Foreach Loop Container

1. SSIS Foreach Loop Container - General : Here we need to assign unique name to this container and also we can specify brief description, so we will get idea why we need to design this container.

SSIS Foreach Loop Container General

2. SSIS Foreach Loop Container - Collection : Use the Collection page of the Foreach Loop Editor dialog box to specify the enumerator type and configure the enumerator.

SSIS Foreach Loop Container Collection

Select the enumerator type from the list. This property has the options listed as follows:
  1. Foreach File Enumerator: Enumerate files
  2. Foreach Item Enumerator: Enumerate values in an item
  3. Foreach ADO Enumerator: Enumerate tables or rows in tables
  4. Foreach ADO.NET Schema Rowset Enumerator: Enumerate a schema
  5. Foreach From Variable Enumerator: Enumerate the value in a variable
  6. Foreach Nodelist Enumerator: Enumerate nodes in an XML document
  7. Foreach SMO Enumerator: Enumerate a SMO object
Let me explain with Foreach File Enumerator

We can use this Foreach File enumerator to enumerate files in a folder.
The content of the folders and subfolders that the Foreach File enumerator enumerates might change while the loop is executing because external processes or tasks in the loop add, rename, or delete files while the loop is executing. This means that a number of unexpected situations may occur:

In Folder option, provide the path of the root folder to enumerate. In Files option, specify the files to enumerate.
If wildcard characters are specified in the Files option, then the fully-qualified paths that are returned match the filter.
  1. Fully qualified: Select to retrieve the fully qualified path of file names.
  2. Name and extension: Select to retrieve the file names and their file name extensions.
  3. Name only: Select to retrieve only the file names.
3. SSIS Foreach Loop Container - Variable Mappings : Use the Variables Mappings page of the Foreach Loop Editor dialog box to map variables to the collection value. The value of the variable is updated with the collection values on each iteration of the loop.
Variable: Select an existing variable, or click <New variable...> to create a new variable.
Index: If using the Foreach Item enumerator, specify the index of the column in the collection value to map to the variable. For other enumerator types, the index is read-only.
Once we configured, For Each Loop container, Lets add the process that we want. Here what I am going to do is:
NOTE: We can add any other tasks to this container. I took Data flow process, as its already explained by this BLOG.

Here lets see the logical scenario of For Each Container Task.

First, From the selected folder, fetch all the files and assign file path to the variable.
Second, For Data Flow Process, process the file from the folder and execute data flow.

NOTE: For Data Flow Process, we need to assign Connection from variable. ( as we assigned variable after each loop, we need to process new file from the folder). So How can we configure File connection to use new file?
Please refer to my next article for How to assign Connection from variable.

That's it. This is what we need to design for "For Each Loop".

Let me know if there is any difficulties you have to design this package.