March 2, 2015 at 3:14 pm
I have an SSIS job that is trying to insert rows into a database table. I have the Error Output for the OLE DB Destination set to "Redirect Rows", which redirects to a flat file. But when an error occurs, which is a constraint error due to trying to insert a duplicate row, it sends all the rows that follow after the error to the flat file as well.
March 3, 2015 at 1:26 am
This is strange.
Can you share a sample package and sample file you are using?
____________________________________________________________
APMarch 3, 2015 at 7:46 am
It would be hard for me to share the package and file because it is dealing with Employee data.
The package has a Data Flow Task which contains only three transformations:
OLE DB Source - which selects the data using a SQL query
OLE DB Destination - which inserts the data into the destination database table with Error Output set to "Redirect row"
Flat File Destination - which is a tab delimited file to capture the error records
Also the key for the Destination table is:
EmployeeId
OfficeId
EffectiveDatetime
March 3, 2015 at 8:09 am
I agree this is strange. Only records that are for whatever reason in error should be rejected. It is possible to add the error number to the output. Have you tried that? You may find that all the rows have an error associated with them.
March 3, 2015 at 8:41 am
OK figured it out.
I had the OLE DB Destination set to "Table or view - fast load".
Changing it to "Table or view" had it send only the error row. Changed it because the error message I was receiving previously was -1071607685 and read that I needed to change it to "Table or view" to get more useful error messages.
Thank you everyone.
March 3, 2015 at 8:43 am
You should have gotten a warning message when you set that that you wouldn't be able to use the rejected records feature.
March 3, 2015 at 8:47 am
No, no warning message came up.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply