none
For each lop error ssis

    Question

  • I have a pakacge with foreach loop ADO.net enumerator to process multiple excel files. Now package is failing when the the generated file is not available in shared drvie. So can anyone please advise me how to solve this issue?

    Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "Excel" failed with error code 0xC0202009.

    Thursday, November 29, 2012 4:06 PM

Answers

  • I assume you read the excel files names somehow and you use foreach loop for each file.

    IN the loop you must have a data flow task to read the excel file, my understanding is that the data flow fails because the file is not in the folder where it should be and you want to avoid that.

    I will check the  existence of the file before  the data flow, if the file exists then go to the data flow else display a message or whatever you wanna do. FOr this I will add a scrip task in the foreach loop, the script task will return a status that indicates the existence of the file, next I will connect the script task to the data flow when status is true.

    • Proposed as answer by Eileen Zhao Tuesday, December 04, 2012 8:21 AM
    • Marked as answer by Eileen Zhao Thursday, December 06, 2012 2:23 PM
    Thursday, November 29, 2012 11:46 PM

All replies

  • set Delay Validation Property of Data Flow tasks to TRUE.  and try

    Please mark as helpful and propose as answer if you find this as correct!!! Thanks,Miss Never Giveup .

    Thursday, November 29, 2012 4:16 PM
  • I assume you read the excel files names somehow and you use foreach loop for each file.

    IN the loop you must have a data flow task to read the excel file, my understanding is that the data flow fails because the file is not in the folder where it should be and you want to avoid that.

    I will check the  existence of the file before  the data flow, if the file exists then go to the data flow else display a message or whatever you wanna do. FOr this I will add a scrip task in the foreach loop, the script task will return a status that indicates the existence of the file, next I will connect the script task to the data flow when status is true.

    • Proposed as answer by Eileen Zhao Tuesday, December 04, 2012 8:21 AM
    • Marked as answer by Eileen Zhao Thursday, December 06, 2012 2:23 PM
    Thursday, November 29, 2012 11:46 PM