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.


 
Posted by
Labels: , , , , , , ,

6 comments:

  1. jamietMarch 30, 2010 at 5:13 PM

    You could also use the FileNameColumnName property of the Flat File Source Adapter to get the name of teh file. Saves all that messing about with variables.

    Read more: http://msdn.microsoft.com/en-us/library/ms135923.aspx#flatfile

    Regards
    Jamie

    ReplyDelete
  2. BI_BuffMarch 31, 2010 at 8:26 PM

    Nice article, thank you jamiet for the info on FileNameColumnName property

    ReplyDelete
  3. UnknownJanuary 18, 2015 at 2:41 AM

    Please how do create the variable, what is the value ?

    ReplyDelete
  4. UnknownJanuary 18, 2015 at 2:43 AM

    how do you configure the variable(Filename) and what is the value in the Value column

    ReplyDelete
  5. Tejas ShahJanuary 19, 2015 at 12:41 PM

    Hi Thomas,

    Please review my another post: http://sqlyoga.com/2010/04/sql-server-ssis-get-file-name-with-flat.html. Here, I have explained how to get FileName while processing multiple files and assign it to the row.

    Hope this helps you. Let me know if there is any question.

    Thanks,
    Tejas

    ReplyDelete
  6. UnknownFebruary 13, 2015 at 12:53 AM

    thanks to Jamiet for the brilliant contribution for using the FileNameColumnName property of the Flat File Source Adapter to get the name of file names.
    Thanks

    ReplyDelete

Subscribe to: Post Comments (Atom)