Search code examples
sqlssispackagearchive

SSIS - Why won't my Data Flow Task fail?

I've got a simple SSIS package that runs a 'foreach' loop, checking a folder for .csv files. It imports the contents of the CSV into a staging table where the columns map. On success of this, it moves the file to an archive folder appending the date. Where it fails, it is supposed to put the file into a failure folder.

However, i've tested with a random csv, that doesn't have column headings that match the mappings, and the data flow task DOESN'T fail & the file goes to the archive folder (of course the table isn't updated either). Any ideas as to why this is happening?

Here is the package:

enter image description here

Here is the data flow:

enter image description here

Solution

  • OK, I can do this.

    Start with seven text files of input data, one of which contains error data.

    enter image description here

    The control flow executes like this.

    enter image description here

    The good files get moved to the ProcessedData folder.

    enter image description here

    The bad file gets moved to the ToReviewData folder.

    enter image description here

    The only setting you need to make is MaximumErrorCount on the Foreach Loop Container. Set this to a suitably high value.

    enter image description here

    I haven't changed any of the properties on the Load Cats task. In particular, you can see that FailPackageOnFailure is False; this is only required for checkpoints.

    enter image description here

    The precedence constraints are as you'd expect. Nothing clever here.

    enter image description here

    enter image description here

    See training kit 70-463 > Chapter 4: Designing and Implementing Control Flow.