SSIS - Writing Exceptions to Flat File

Question

SSIS - Writing Exceptions to Flat File

jw5

Old Hand

Points: 309
More actions
July 1, 2010 at 4:18 am

#234623

What is the best way to ensure that a flat file isn't created if no exceptions are found in the data.
For example. Loop over a set of files, read and process through a conditional split (with business logic applied). Output for the conditional split is to write to a new flat file (valid data) or an exception file.
An exception file is always created even if no rows go down the exception stream.
Can someone advise me on how I can stop an empty file being created rather than deleting it after witha file system task.
Thank you

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

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

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 1

I'm pretty sure that it can't actually be done. I believe you're stuck with just deleting the empty file. As a side bar, though, I wouldn't delete the empty file... think of it as an audit log that says there were no problems with that particular import (or whatever). SOX folks will love you!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 2

You could write the exception stream to a temp/staging table.

Then, check the rowcount of this table and store the result in a variable. Use this variable in a precedence constraint to a task that will write the data from the temp table to the flat file.

The expression should be something like this:

@exception_rowcount != 0

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

jw5 Old Hand Points: 309 More actions · Answer 3

Thank you for the responses. I have a solution now.

The issue I found with the row count was setting and using it in the same data flow.

Solution:

I wrote the exceptions to a table in 1 data flow.

Took a row count from the exception table in a 2nd data flow.

Then had an expression and success constraint on the link to the 3rd data flow.