Raw File Destination for errors?

View previous topic View next topic Go down

Raw File Destination for errors?

Post  mpalmerlee on Tue Nov 29, 2011 3:05 pm

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

mpalmerlee

Posts : 14
Join date : 2011-11-18

View user profile http://www.masteredsoftware.com

Back to top Go down

Re: Raw File Destination for errors?

Post  mpalmerlee on Tue Nov 29, 2011 3:10 pm

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.

mpalmerlee

Posts : 14
Join date : 2011-11-18

View user profile http://www.masteredsoftware.com

Back to top Go down

Re: Raw File Destination for errors?

Post  ngalemmo on Tue Nov 29, 2011 3:20 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Raw File Destination for errors?

Post  mpalmerlee on Tue Nov 29, 2011 3:23 pm

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.

mpalmerlee

Posts : 14
Join date : 2011-11-18

View user profile http://www.masteredsoftware.com

Back to top Go down

Re: Raw File Destination for errors?

Post  mpalmerlee on Tue Nov 29, 2011 3:48 pm

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.

mpalmerlee

Posts : 14
Join date : 2011-11-18

View user profile http://www.masteredsoftware.com

Back to top Go down

Re: Raw File Destination for errors?

Post  Mike Honey on Tue Nov 29, 2011 7:32 pm

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

avatar
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

View user profile http://www.mangasolutions.com

Back to top Go down

Re: Raw File Destination for errors?

Post  mpalmerlee on Tue Nov 29, 2011 7:37 pm

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

mpalmerlee

Posts : 14
Join date : 2011-11-18

View user profile http://www.masteredsoftware.com

Back to top Go down

Re: Raw File Destination for errors?

Post  Mike Honey on Tue Nov 29, 2011 8:38 pm

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
avatar
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

View user profile http://www.mangasolutions.com

Back to top Go down

Re: Raw File Destination for errors?

Post  ngalemmo on Tue Nov 29, 2011 8:40 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Raw File Destination for errors?

Post  mpalmerlee on Tue Nov 29, 2011 9:36 pm

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

mpalmerlee

Posts : 14
Join date : 2011-11-18

View user profile http://www.masteredsoftware.com

Back to top Go down

Re: Raw File Destination for errors?

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum