Errorneous Row numbers in a SSIS task

Question

Hi,

I am importing a text file into SQL server table which has got number of constraints. I have created one package and associated tasks. At the end of a SSIS package execution, I want to know the erroenous row numbers which were not succefully exported to DB. Is any direct API or variable available in dts namespace to give this information?

Kindly share with me any knowledge to get this information.

Thanks, Rahul

Answer 1

The error (red line) output of your import step inside the data flow lets you redirect to an error table. This should list the information you are after.

http://msdn.microsoft.com/en-us/library/ms140083.aspx

Error Outputs ( http://msdn.microsoft.com/en-us/library/ms140080.aspx )

Sources, destinations, and transformations can include error outputs. You can specify how the data flow component responds to errors in each input or column by using the Configure Error Output dialog box. If an error or data truncation occurs at run time and the data flow component is configured to redirect rows, the data rows with the error are sent to the error output. By default, an error output contains the output columns and two error columns: ErrorCode and ErrorColumn. The output columns contain the data from the row that failed, ErrorCode provides the error code, and ErrorColumn identifies the failing column.

For more information, see Handling Errors in the Data Flow.

Answer 2

Redirect the error rows on the destination component, pipe them through a count operation and then log that to a log table or whatever.