Raw File Destination for errors?

I've been following the Kimball sample SSIS projects and all errors are dumped to Raw file destinations, which I assume is because Raw files are the fastest to use because they are basically SSIS's in-memory representation of the data.

However it seems that Microsoft doesn't have a tool to open these files (there is a third party tool available here), but that seems like a pain just to see the contents of an error to determine why certain rows were rejected.

Is there a reason why I shouldn't dump my errors to something a little more user friendly like excel or a delimited fllat file format?

thanks,
-Matt

Also, according to this post: Performance of Raw Files vs. Flat Files, writing to raw files doesn't seem all the much faster than delimited files, especially for errors which you hope is on the order of thousands, not millions of rows.

You hope for thousands of errors??? I usually hope for none.

As far as performance goes, it really doesn't matter... choose what works best for you.

Well, hope for not more than thousands. Actually trying to hook up a flat file destination, I realize why Kimball's samples might use the raw file destination, because you can use "File name from variable" option, which I don't see how to do using a flat file destination.

Actually, it looks like you can use a variable in the Flat File Connection Manager: Dynamic Flat File Destinations. I'll have to try this out.

Hi Matt,

For my projects, I always write errors requiring tracking/tracing to SQL tables. The minor inconvenience of having to create and maintain tables quickly pays back as soon as you have to interrogate, analyse or process them.

That said, for a Datamart/Datawarehouse project I use what's often called the "Aggressive Load" style and try to load every row, regardless of bad data. In this scenario, separate error tables are not required as the data appears in the standard dimensions and facts, categorised to indicate it is an "error".

Good luck!
Mike

Mike,
Thanks for the reply, your pattern of "aggressive load" is interesting, however the data I'm working with has some problems that prevent it from going into the target DW table, i.e. violates primary key constraints or whatever, so I need a way to write error rows out to either a file or a table as you suggest. One problem I'm having now is that my error rows aren't very helpful because my batch size for the bulk load is 1000 rows which makes it difficult to tell which row had the problem because the whole batch (1000 rows) is redirected to the error output.

thanks for your input,
-Matt

Hi Matt,

To get around your batch issue, you might try the "double-destination-with-errors-redirected" technique. Here's a description (you might find better examples with more research):

http://code.commongroove.com/2011/09/16/ssis-use-two-ole-db-destination-adapters-to-catch-insertionconstraint-errors/

Good luck!
Mike

Mike's suggestions make a lot of sense. For the "aggressive load" thing, just define a target table with nothing but unedited text fields (one for each input column) and bulk load everything into that. You shouldn't get any errors at all, any you process from that table. When problems come up, you have something to look at in the DB.

Thanks for the pointers and help guys, looks like I've got more research and playing around to do, good thing I'm still just trying to work up a prototype, the learning curve on SSIS is pretty steep!

-Matt