SSIS 2005 push csv import errors to a table

I have created a package to import rows into a table from a CSV flat file. Everything works great. But now I have to push any rows that fail to import to the staging table into a error taable so that I can report which rows failed. I have been successful in pushing those error rows to another flat file but I would like to push to a table if possible.
gcrickmanVP Information Technology ServicesAsked:
Who is Participating?
 
Jason Yousef, MSConnect With a Mentor Sr. BI  DeveloperCommented:
that's the rows that didn't make it to the database for any reason, due to constraints or such

check that
http://agilebi.com/jwelch/2008/09/05/error-redirection-with-the-ole-db-destination/
huslayer-509973.flv
0
 
Jason Yousef, MSSr. BI  DeveloperCommented:
Just redirect the error rows to another OLEDB destination. a
0
 
Jason Yousef, MSSr. BI  DeveloperCommented:
Also if you want to redirect the OLEDB destination errors, this article talks about that

http://agilebi.com/jwelch/2008/09/05/error-redirection-with-the-ole-db-destination/
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
gcrickmanVP Information Technology ServicesAuthor Commented:
in your first post how did you add your destination columns to error output line?
When I open the OLE DB DEstination for the Error Output I see this?
 Image
So I can't seem to mapp the columns correctly and no way to add the columns
0
 
Jason Yousef, MSSr. BI  DeveloperCommented:
You do that from the flat file source, as in my screen shot and it'll redirect the error rows to the RED output and just connect a normal OLEDB source and map the columns to your separate table.
0
 
Jason Yousef, MSSr. BI  DeveloperCommented:
0
 
gcrickmanVP Information Technology ServicesAuthor Commented:
This is what I see when I click on the OLE DB destination when mappnig the columns.
I think the reason is that the data flow is only sending those three columns and not the source tables columns.
 oledb destination columns
0
 
gcrickmanVP Information Technology ServicesAuthor Commented:
Ok, so you destination table is holding those three columns. I think I get that now.
I was hoping that the destination table would hold the entire row data that errored out from the source. Is there a way I can see that?
0
 
Jason Yousef, MSSr. BI  DeveloperCommented:
No, because it;s an error column, or truncated column, means it task cannot read it, that's why no way to see it.

and it's the same in 2008 too, hopefully they can fix that or get a way around it.

But you could get the error rows ,out of the OLEDB destination because it's already in the pipe.
0
 
Jason Yousef, MSSr. BI  DeveloperCommented:
Ok, i'm thinking again, and I think the only way around it, is to read the file using a script component, that way you could redirect the whole rows to another output.
0
 
gcrickmanVP Information Technology ServicesAuthor Commented:
Cold you explain how I would get the error rows out of the OLEDB destination.
0
 
gcrickmanVP Information Technology ServicesAuthor Commented:
Thank you so much. You have been really helpful. I am sure that I will have further questions with this project I am working on.
0
 
Jason Yousef, MSSr. BI  DeveloperCommented:
Sure, anytime...

Also you could e-mail me at huslayer@hotmail.com when you post a question so I can get on it right away...

Thanks
Jason
0
All Courses

From novice to tech pro — start learning today.