locked
set variable in foreach loop RRS feed

  • Question

  • I have a foreach loop to import files in a folder. How do you set a variable to the current file name to update a field in the database?
    Wednesday, October 26, 2016 3:10 AM

Answers

  • If you have your package structured as a ForEach Loop Container that's configured to iterate over the files in the folder, and to fill a variable ("CurrentFileName") on each iteration, yes, you can add that file name as a column value in your table in a data flow.

    You want to add a Derived Column transformation between your source and your destination component.  Inside that Derived Column, you'll want to add a new column.  For the expression for the column, you can expand the "variables" node in the upper-left section of the editor - you should see your "CurrentFileName" variable there - drag and drop it into the Expression.


    Todd McDermid's Blog Talk to me now on

    • Proposed as answer by RyanAB Wednesday, October 26, 2016 11:54 PM
    • Marked as answer by Todd McDermid Thursday, October 27, 2016 9:44 PM
    Wednesday, October 26, 2016 11:11 PM

All replies

  • have you set it to a File enumerator?

    If so, it has options for full path and file, just filename or filename and extension, take your pick. Then set your variable within the Variable Mappings tab

    Wednesday, October 26, 2016 3:39 AM
  • I did that. Can something like this be done in the Data Flow? The number of files vary. Each time the SSIS package is run each file is imported in the folder. But one of the field in the table needs to be updated with the file name.
    Dts.Variables["User::CurrentFileName"].Value = Dts.Variables["User::VariableMapping"].Value;



    • Edited by MTL2014 Wednesday, October 26, 2016 3:54 AM
    Wednesday, October 26, 2016 3:53 AM
  • Hi ,

    I hope I got your needs , Have you tried the expression task to update a temp variable and later in the container use data flow (expression property for connecion with the temp variable) .


    Regards, David .

    Wednesday, October 26, 2016 7:58 AM
  • Have you set your connection manager connection string to be the same variable as what you're setting in the enumerator?
    Wednesday, October 26, 2016 7:19 PM
  • If you have your package structured as a ForEach Loop Container that's configured to iterate over the files in the folder, and to fill a variable ("CurrentFileName") on each iteration, yes, you can add that file name as a column value in your table in a data flow.

    You want to add a Derived Column transformation between your source and your destination component.  Inside that Derived Column, you'll want to add a new column.  For the expression for the column, you can expand the "variables" node in the upper-left section of the editor - you should see your "CurrentFileName" variable there - drag and drop it into the Expression.


    Todd McDermid's Blog Talk to me now on

    • Proposed as answer by RyanAB Wednesday, October 26, 2016 11:54 PM
    • Marked as answer by Todd McDermid Thursday, October 27, 2016 9:44 PM
    Wednesday, October 26, 2016 11:11 PM
  • If you have your package structured as a ForEach Loop Container that's configured to iterate over the files in the folder, and to fill a variable ("CurrentFileName") on each iteration, yes, you can add that file name as a column value in your table in a data flow.

    You want to add a Derived Column transformation between your source and your destination component.  Inside that Derived Column, you'll want to add a new column.  For the expression for the column, you can expand the "variables" node in the upper-left section of the editor - you should see your "CurrentFileName" variable there - drag and drop it into the Expression.


    Todd McDermid's Blog Talk to me now on


    Aha... now that I've read your response, the initial question makes a bit more sense...
    • Marked as answer by MTL2014 Thursday, October 27, 2016 12:34 AM
    • Unmarked as answer by Todd McDermid Thursday, October 27, 2016 9:44 PM
    Wednesday, October 26, 2016 11:54 PM
  • Marked the wrong answer there, should be Todd's response
    Thursday, October 27, 2016 1:02 AM