locked
Need help with a ForEach Loop Container RRS feed

  • Question

  • I've got an SSIS package that works that will loop through a directory, and import each csv file. The files are named "AcContractInfoDwn030709.csv", with the date changing daily. I need the date in the filename as I extract it from there and load it into the database with each record.

    My ForEach Loop is a file enumerator, and the file it looks for is "AcContractinfoDwn*.csv". I have a variable uvFilename, with an index of 0, which is passed into a script task that gets me the date of the file (to be mapped into a derived column).

    The problem I'm having is the flat file source file name. If I set it to "AcContractInfoDwn030709.csv", it will process this and each other file in the directory. If this file is then removed after import, my package will no longer run, stating it can't find the file, even though there may be other similarly named files (with different dates).


    Tuesday, March 10, 2009 6:40 PM

Answers

  • PReardon said:

    So does this file remain there, and get imported every time the package runs? Is it only needed for running the package in development mode, or will it have to be there when the package is deployed and running in a production environment?

    the file that eric referrs to is merely a dummy placeholder file that never gets processed.  the actual filename for the source is generated at run-time via an ssis expression.  essentially, what happens is that the foreach loop container would store the file name in an ssis variable.  then, the file source would use this variable's value in an expression contained within another ssis variable as its connection string.  so, the file source's connection string would dynamically change with each iteration of the foreach loop container.  bear in mind that this method only works when the DelayValidation property of the data flow task is set to true.

    hth



    Duane Douglas, MCAD | http://www.ssisbi.com | Please mark the post(s) that answered your question.
    • Proposed as answer by Duane Douglas Friday, March 13, 2009 8:29 AM
    • Marked as answer by Tony Tang_YJ Friday, March 20, 2009 10:01 AM
    Friday, March 13, 2009 8:28 AM

All replies

  • There are a few ways around this issue, but the one that I have seen used most often is to store a template version of this data with one or two sample rows outside of your normal processing folder and point to that with your connection manager as the base line.  This will allow you to develop without having to worry about your processing affecting the file your initial connection manager points to.

    Please mark answered posts. Thanks for your time.
    Tuesday, March 10, 2009 8:22 PM
  • So does this file remain there, and get imported every time the package runs? Is it only needed for running the package in development mode, or will it have to be there when the package is deployed and running in a production environment?
    Wednesday, March 11, 2009 2:25 PM
  • PReardon said:

    So does this file remain there, and get imported every time the package runs? Is it only needed for running the package in development mode, or will it have to be there when the package is deployed and running in a production environment?

    the file that eric referrs to is merely a dummy placeholder file that never gets processed.  the actual filename for the source is generated at run-time via an ssis expression.  essentially, what happens is that the foreach loop container would store the file name in an ssis variable.  then, the file source would use this variable's value in an expression contained within another ssis variable as its connection string.  so, the file source's connection string would dynamically change with each iteration of the foreach loop container.  bear in mind that this method only works when the DelayValidation property of the data flow task is set to true.

    hth



    Duane Douglas, MCAD | http://www.ssisbi.com | Please mark the post(s) that answered your question.
    • Proposed as answer by Duane Douglas Friday, March 13, 2009 8:29 AM
    • Marked as answer by Tony Tang_YJ Friday, March 20, 2009 10:01 AM
    Friday, March 13, 2009 8:28 AM