Post reply

Ignore flat file availability errors

  • October 8, 2014 at 7:17 am

    #397478

    Hello Everyone

    I am building a SSIS package that:

    1) Executes a python script that alters flat files into a comma delimited format.

    2) Executes a data flow task that takes the altered flat files and loads them into a table. (There are 6 flat files that get loaded.)

    3) Deletes the flat file (using a batch command file) once all of the rows have been added to the database.

    Now we don't always have all 6 flat files.. Some times we only have 2/6 flat files available. How can I get SSIS to ignore a flat file source and move on if there's no files present? I will be setting up a SQL job to run this package and I am trying to avoid having the package fail and not completing or getting to step 3.

    Thanks for your help.

  • October 8, 2014 at 7:29 am

    #1750954

    Put the data flow loading the flat file inside a for each loop.

    Configure the for each loop to look only for the filename of that flat file. If it isn't there, the flat file will not execute.

    Another option is to have a script task that just checks if the file is there or not and writes the result to a variable.

    Connect the script task to the data flow with a precedence constraint using an expression that checks the value of that variable.

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

  • October 8, 2014 at 2:00 pm

    #1751102

    Thank you very much for the reply, I tried to get the for each loop but was unsuccessful. However I failed to tell you that each file has its own table that it is inserted into, my apologies.

    I was able to get a file check working by creating a script task and 2 variables in the control flow. The first variable called the file location, and the second one is a Boolean to set to True. I then put a constraint between the script task and the data flow task. I used an expression in the constraint. (see SSIS_Setup002) I then created a file system task which deleted the raw data file to keep things tidy.

    Thanks again!

Viewing 3 posts - 1 through 2 (of 2 total)

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