locked
For each loop for Derived Column RRS feed

  • Question

  • I am creating a package to import 6 flat file on the same directory.

    I plan to use For Each Loop to import them.

    The only thing is there are 3 extra fields I need to use Dervived Columns and Scripts Components for each flat file.

    I need find a way to pass the information at run time in order to using For Each Loop.

    I can create a user name variable for file name to pass for For Each Loop for varFileName, but I need pass other 3 paramters.

    I create a variable for For Each Loop, but I need have value for the variable, I just need to know how I can pass this variable and change value at run time.

    Your help and information is great appreciated,

    Regards,

    Souris,

    Friday, November 1, 2013 3:26 PM

Answers

  • Thanks for the information and help,

    I do use Derived Column, but I need fill the data base on the file name.

    I am tring to pass file name information to Dervied Column or Script Component to feed my tables in the database,

    Thanks again for helping,

    Regards,

    Souris,

    If you want to store the filename in the column, then you can use the variable from the foreach loop. If it contains the complete filepath and you only want the filename then use an expression like:

    RIGHT(@[User::FilePath],FINDSTRING(REVERSE(@[User::FilePath]),"\\",1) - 1) 
    

    Or you can go to the advanced editor of the Flat File Connection Manager and use the FileNameColumnName  property


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    • Proposed as answer by Harry Bal Friday, November 1, 2013 8:04 PM
    • Marked as answer by sourises Tuesday, November 5, 2013 10:57 AM
    Friday, November 1, 2013 7:56 PM

All replies

  • Do those 6 files have the same metadata (nr. of columns, columnnames and datatypes)?

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Friday, November 1, 2013 3:34 PM
  • yes, they do,

    The only thing is destination has 24 fields, source has 21 fields.

    The extra 3 fields need to feed different value for different files.

    Thanks again for helping,

    Regards,

    Souris,

    Friday, November 1, 2013 3:40 PM
  • Use derived column to create additional columns and map to destination column.

    http://sqlserverrider.wordpress.com/2011/07/07/adding-business-logic-layer-in-the-data-flow-with-less-effort/

    You have mentioned that you need to get those 3 columns from other file. So, you merge task too. Give us some additional details about your source file and the other file you want to upload.

    http://sqlserverrider.wordpress.com/2011/07/13/join-two-datasets-using-merge-join-tansformation/


    Thanks Ayyappan Thangaraj, http://SQLServerRider.wordpress.com



    • Edited by [Ayyappan] Friday, November 1, 2013 5:19 PM additional info
    Friday, November 1, 2013 5:14 PM
  • yes, they do,

    The only thing is destination has 24 fields, source has 21 fields.

    The extra 3 fields need to feed different value for different files.

    Thanks again for helping,

    Regards,

    Souris,


    Ok how do you determine the values of those three columns that should be different for all 6 files?

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Friday, November 1, 2013 5:27 PM
  • I can determin the value by file name.

    I was looking for a way to pass file name to data flow.

    I can pass file name as varFileName to Control Flow. If I can pass trhe file name to my data flow control Derived Column or Script Components then it should work using For Each Loop.

    Thanks again for helping and information,

    Regards,

    Souris,

    Friday, November 1, 2013 5:41 PM
  • Thanks for the information and help,

    I do use Derived Column, but I need fill the data base on the file name.

    I am tring to pass file name information to Dervied Column or Script Component to feed my tables in the database,

    Thanks again for helping,

    Regards,

    Souris,

    Friday, November 1, 2013 5:44 PM
  • If you use For Each Loop to iterate through files using file enumerator, you'll automatically get filename inside loop. You just need to use required variable inside loop to map it with correct index to get filename for each iteration. Then its just a matter of using them inside data flow task inside expression builders.
    • Edited by Visakh16MVP Friday, November 1, 2013 5:51 PM
    Friday, November 1, 2013 5:49 PM
  • Thanks for the information and help,

    Yes, I use Derived Column, but it does not accept variable, only constant.

    I can use lookup if Lookup control accept the file name as variable then I just use lookp up.

    I tried to use Script Component control and edit script, if Script Components accept the file name as varibale.

    If Derived Column, Lookup and Script Component can not accept varFileName as a variable whole iterate then I think that I do not have choice that I have to create one package per each file.

    Thanks again for the information and help,

    Regards,

    Souris,

    Friday, November 1, 2013 5:58 PM
  • Thanks for the information and help,

    I do use Derived Column, but I need fill the data base on the file name.

    I am tring to pass file name information to Dervied Column or Script Component to feed my tables in the database,

    Thanks again for helping,

    Regards,

    Souris,

    If you want to store the filename in the column, then you can use the variable from the foreach loop. If it contains the complete filepath and you only want the filename then use an expression like:

    RIGHT(@[User::FilePath],FINDSTRING(REVERSE(@[User::FilePath]),"\\",1) - 1) 
    

    Or you can go to the advanced editor of the Flat File Connection Manager and use the FileNameColumnName  property


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    • Proposed as answer by Harry Bal Friday, November 1, 2013 8:04 PM
    • Marked as answer by sourises Tuesday, November 5, 2013 10:57 AM
    Friday, November 1, 2013 7:56 PM
  • As SSISJoost responded, there will be no need to have derived column component if you use the flat file source's advanced editor to use the FileName. This column can be then mapped to the destination table. I think this will cover the one out of two fields. For the remaining two fields you have to use derived column component.

    Note: As per my guess if  the other two fields/columns are auditing columns such as DateTime and user info, you can create defaults on the table itself for these.


    Thanks, hsbal

    Friday, November 1, 2013 8:10 PM
  • Note: As per my guess if  the other two fields/columns are auditing columns such as DateTime and user info, you can create defaults on the table itself for these.

    And two more alternatives:
    use the Audit Transformation to fill new columns
    use SSIS System Variables in a Derived Column to fill new columns


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Friday, November 1, 2013 8:16 PM
  • Thanks a million for helping and information,

    I added Filename field and see the field name in the input column, but I do not see the data when I tried to preview data.

    I need to figure out if there is any issue with my packages.

    Thanks again for helping and informaiton,

    Regards,

    Souris,

    Monday, November 4, 2013 3:08 PM
  • Thanks a million for helping and information,

    I added Filename field and see the field name in the input column, but I do not see the data when I tried to preview data.

    I need to figure out if there is any issue with my packages.

    Thanks again for helping and informaiton,

    Regards,

    Souris,


    Perhaps you could use a Data Viewer instead of using the preview button...

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Monday, November 4, 2013 3:13 PM
  • OR you need to check if the variable that is saving the file name is populated with value...

    Thanks, hsbal

    Monday, November 4, 2013 4:52 PM