locked
Please Help! Variable in a For Each Loop RRS feed

  • Question

  • Hello,
    I have a For Each Loop that inserts many CSV files to a table.
    I want to insert as a new column the CSV file name for the rows it just inserted.
    For example, file 111.csv contains 5 columns and 20 rows. When it inserted to the table in the database, the database will contain 6 columns and 20 rows. The sixth column will contain the value 111. (from 111.csv)

    I know it is possible with file name variable but HOW to do it?!
    No information or real step by step guide. In every forum I read that I have to "'use variable" and no more instructions...

    Please help!!
    Thanks!'


    lekfir
    Saturday, January 10, 2009 7:31 PM

Answers

  • Hi,
    You can add the value stored in a variable into the pipeline (i.e. the dataflow) using a Derived Column component. Hopefully should be fairly self-explanatory - if not, you need to go and learn about Derived Column components :)

    -Jamie
    http://jamiethomson.spaces.live.com/ | http://blogs.conchango.com/jamiethomson
    • Proposed as answer by Todd McDermid Saturday, January 10, 2009 11:58 PM
    • Marked as answer by lekfir Tuesday, January 13, 2009 2:24 PM
    Saturday, January 10, 2009 8:10 PM
  • Why not just insert var_FileJustFound via the derived column as Jamie stated above.  It IS the file name after all... 

    Another way to add the file name to the data pipe would be by just typing in a column name (example: "myFileName") in the advanced editor of the flat file source for the propery FileNameColumnName under the component properties tab.

    Please mark answered posts. Thanks for your time.
    • Marked as answer by lekfir Tuesday, January 13, 2009 2:24 PM
    Monday, January 12, 2009 3:12 PM
  • If you are already looping thru the files, then that means you've successfully set up the foreach loop, which also means you are storing the current file name in a package variable.... right?

    So, then use this package variable to create a derive column (and perhaps do some string manipulation to get the file name to appear exactly the way you want it because this variable will probably contain the full path to the file.)
    • Marked as answer by lekfir Tuesday, January 13, 2009 2:24 PM
    Monday, January 12, 2009 5:34 PM
  • Absolutely... Check what the meta data is for that column that you have just added via the derived column transformation.  If you look to the right of the expression it will tell you what type it is (DT_STR or DT_WSTR), what code page, etc... Most important, it will tell you how long the string can be.  As you are getting a truncation error, you are overflowing the destination with the source... i.e., your destination accepts an input of up to 100 characters whereas you input, the derived column, is bigger than that.  Check to see how large and adjust your column length accordingly.
    Please mark answered posts. Thanks for your time.
    • Edited by Eric Wisdahl Tuesday, January 13, 2009 1:00 AM wrote wrong side
    • Marked as answer by lekfir Tuesday, January 13, 2009 2:24 PM
    Tuesday, January 13, 2009 1:00 AM
  • Try casting your variable to a specifically-defined type, such as:

    (DT_WSTR, 100)@User::var_FileJustFound

    Or

    (DT_STR, 100, 1252)@User::var_FileJustFound

    The SSIS team did away with allowing you to edit the type and length directly (to the right of the expression) due to (what they said) were copious complaints of shooting oneself in the foot with illegal conversions.  No opinion on that claim - but it's relatively easy to circumvent.
    Todd McDermid's Blog
    • Marked as answer by lekfir Tuesday, January 13, 2009 2:23 PM
    Tuesday, January 13, 2009 6:25 AM

All replies

  • Hi,
    You can add the value stored in a variable into the pipeline (i.e. the dataflow) using a Derived Column component. Hopefully should be fairly self-explanatory - if not, you need to go and learn about Derived Column components :)

    -Jamie
    http://jamiethomson.spaces.live.com/ | http://blogs.conchango.com/jamiethomson
    • Proposed as answer by Todd McDermid Saturday, January 10, 2009 11:58 PM
    • Marked as answer by lekfir Tuesday, January 13, 2009 2:24 PM
    Saturday, January 10, 2009 8:10 PM
  • Thanks Jamie.
    I declared a file name variable (In the Data Flow that inserts the files in a loop) which called FileName and the default Value is: file100.csv
    I managed to insert as a column the variable but I want it to dynamically insert the file names (file100.csv, file101.csv, ,file102.csv...)

    Thanks,
    Kfir



    lekfir
    Sunday, January 11, 2009 5:12 PM
  • Right, well the ForEach File Enumerator in the ForEach Loop allows you to push the enumerated filename into a variable.

    You can use a combination of variables and property expressions to update the property of the package object with the enumerator collection value. First you map the collection value to a user-defined variable, and then you implement a property expression on the property that uses the variable. For example, the collection value of the Foreach File enumerator is mapped to a variable called MyFile and the variable is then used in the property expression for the Subject property of a Send Mail task.
    (
    http://technet.microsoft.com/en-us/library/ms141724.aspx)

    You might also want to take a read of this which suggests a different techniique, using the Multiflatfile adapter:

    SSIS: Processing data from multiple files all at once
    (http://blogs.conchango.com/jamiethomson/archive/2006/10/14/SSIS_3A00_-Processing-data-from-multiple-files-all-at-once.aspx)

    -Jamie


    http://jamiethomson.spaces.live.com/ | http://blogs.conchango.com/jamiethomson
    Sunday, January 11, 2009 6:02 PM
  • Thanks Jamie for the quoting.
    Is any one can help me than send me to links?
    I have a For Each Loop that inserts all files into db using a variable - var_FileJustFound. In the Connection Manger Expressions I mapped the variable property as  a Connection String.

    Now, I want for each file that I inserted the rows to the database to insert also the File Name of the file.
    I declared another 'Package' level variable called var_FileName as String.

    How can I do that the variable will get the file name?
    Later, I can Use  a Derived Column to insert the value to the table.
    But I don't understand how to send to the variable the File Name.


    Thanks!

    lekfir
    Monday, January 12, 2009 1:36 PM
  • Why not just insert var_FileJustFound via the derived column as Jamie stated above.  It IS the file name after all... 

    Another way to add the file name to the data pipe would be by just typing in a column name (example: "myFileName") in the advanced editor of the flat file source for the propery FileNameColumnName under the component properties tab.

    Please mark answered posts. Thanks for your time.
    • Marked as answer by lekfir Tuesday, January 13, 2009 2:24 PM
    Monday, January 12, 2009 3:12 PM
  • If you are already looping thru the files, then that means you've successfully set up the foreach loop, which also means you are storing the current file name in a package variable.... right?

    So, then use this package variable to create a derive column (and perhaps do some string manipulation to get the file name to appear exactly the way you want it because this variable will probably contain the full path to the file.)
    • Marked as answer by lekfir Tuesday, January 13, 2009 2:24 PM
    Monday, January 12, 2009 5:34 PM
  • Thanks  fftw_ayi and Eric
    Before inserting teh Flat File to db I added a derived column and added as a new column the expression:
    @[User::var_FileJustFound].
    In the destination table I declared the target column "[FileName]" as varchar 100.

    But the task fails with the following errors:

    <EM>[Insert FileName [240]] Error: The "component "Insert FileName" (240)"   
    failed because truncation occurred, and the truncation row disposition on   
    "output column "FileName" (653)" specifies failure on truncation. A truncation   
    error occurred on the specified object of the specified component.</EM> 

    AND

    <EM>[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.&nbsp; The   
    ProcessInput method on component "Insert FileName" (240) failed with error code   
    0xC020902A while processing input "Derived Column Input" (241). The identified   
    component returned an error from the ProcessInput method. The error is specific   
    to the component, but the error is fatal and will cause the Data Flow task to   
    stop running.&nbsp; There may be error messages posted before this with more   
    information about the failure.<BR></EM

    Any idea???
    Thanks!


    lekfir
    Monday, January 12, 2009 10:20 PM
  • Absolutely... Check what the meta data is for that column that you have just added via the derived column transformation.  If you look to the right of the expression it will tell you what type it is (DT_STR or DT_WSTR), what code page, etc... Most important, it will tell you how long the string can be.  As you are getting a truncation error, you are overflowing the destination with the source... i.e., your destination accepts an input of up to 100 characters whereas you input, the derived column, is bigger than that.  Check to see how large and adjust your column length accordingly.
    Please mark answered posts. Thanks for your time.
    • Edited by Eric Wisdahl Tuesday, January 13, 2009 1:00 AM wrote wrong side
    • Marked as answer by lekfir Tuesday, January 13, 2009 2:24 PM
    Tuesday, January 13, 2009 1:00 AM
  •  Thanks ERIC,
    The derived column that gets my variable is defined as WSTR with length of zero (0) and no code page.
    I cannot change it.
    How can the derived column knows the size to allocate for the the variable if it doesnt have it as an input from the beginning?
    How can I change the Derived Column size?


    lekfir
    Tuesday, January 13, 2009 5:49 AM
  • Try casting your variable to a specifically-defined type, such as:

    (DT_WSTR, 100)@User::var_FileJustFound

    Or

    (DT_STR, 100, 1252)@User::var_FileJustFound

    The SSIS team did away with allowing you to edit the type and length directly (to the right of the expression) due to (what they said) were copious complaints of shooting oneself in the foot with illegal conversions.  No opinion on that claim - but it's relatively easy to circumvent.
    Todd McDermid's Blog
    • Marked as answer by lekfir Tuesday, January 13, 2009 2:23 PM
    Tuesday, January 13, 2009 6:25 AM
  • Thanks Todd!!!
    That worked great!

    lekfir
    Tuesday, January 13, 2009 2:23 PM