SSIS Package fails when incoming file not Exist in Source path.

  • Saintmount.Sql

    Ten Centuries

    Points: 1023

    Hi All,

    I have a package which contains a data flow task to read Flat file and populate a SQL table. Flat File source task will read input file from the INPUT folder and process them and at the end it will move the processed input files to the Achieve folders. So, sometimes there will not be any input files present (if support team does not populate new input files) in the INPUT folder and my package throws error that source file does not exist.

    I've used Script task if file not present (i.e. already moved to Archive folder) in the source folder don’t process the Data Flow task. But still try trying to execute the Dataflow task, so getting an error. I want to pass to else block such situation.

    Any one knows how to fix this issue? I wanted to make package should NOT run such situation. Since we do not have the File in Source path.

    Thanks in advance.

    Vijay

  • Phil Parkin

    SSC Guru

    Points: 244732

    One easy way is to put your dataflow inside a Foreach (file) container - using the input file's name as the filespec. Then it will not execute if the file is not there.

    You may also have to set the delay validation property to true on your input file connection.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Saintmount.Sql

    Ten Centuries

    Points: 1023

    Yes,I've created Data flow task in the For each loop only.But Still it's throwing error in the For each loop Container.

    Pls find my package in the attachment. thanks.

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Vijay.Sql (6/4/2011)


    Yes,I've created Data flow task in the For each loop only.But Still it's throwing error in the For each loop Container.

    Pls find my package in the attachment. thanks.

    If the dataflow is inside the for each loop, how come you are starting the dataflow for a file that doesn't exist?

    If it doesn't exist, the for each loop shouldn't pick it up and that iteration of the loop should be skipped.

    You can try putting a script task in front of the dataflow. Check for existence of the file and set a boolean variable with the result (e.g. FileFound).

    Connect the script task to the dataflow and in the precedence constraint set it to success and expression. The expression should be FileFound == True.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Saintmount.Sql

    Ten Centuries

    Points: 1023

    Thanks for your reply. My requirement slightly changed.

    I need to unzip the file from source path, that is not a issue I’ve handled by Execute process task.

    Problem is If I do not have file any file(Zip file). I don’t want to execute the Execute process task.

    How can I handle in script task for the checking the incoming file not Exist in Source path.

    I have handled in the precedence constraint @[bolFileExists]==True ( will execute the execute process task for unzip the file)

    another path precedence constraint set to @[bolFileExists]==False( will execute the another part of script task for showing message(like file not source path)

    But package trying to execute the execute process task, even thought if i do not have file in source path. Finally throws error. It should not execute the process task.

    Could you please help out how handle this issue.

    Thanks.

  • SQLFanatic

    SSCommitted

    Points: 1640

    Hi Vijay,

    As Koen mentioned above, you may use a simple script task before any other process/task in your package to check the input file existence and hold the value in a variable accordingly. Let's say you use @File_Exist = 'YES' incase the input file is present.

    Then defiene the precedence constraint as following:

    Evaluation Operation: Expression and Constraint

    Value: Success

    Expression: @File_Exist == "YES"

    Select 'Logical And' operation.

    You can use another precedence constraint in a similar fashion for @File_Exist = 'NO' i.e. if there's no input file.

    🙂

    ________________________________________________________________
    "The greatest ignorance is being proud of your learning"

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply