error column

Hi,

Using SSIS, while importing (source i.e. .csv into destination i.e. sql server table), how is it possible to log the source COLUMN which causes the error in the row?

Thanks

Hello Arkiboys,

Many data flow components, including most sources and destinations, support an "Error Ouput" concept whereby you can work with the individual rows of data that could not be successfully processed by a component. This error output includes an ErrorCode and ErrorColumn that you can use in many cases to determine the column that is causing the error.

For more information, I'd suggest taking a look at this:

http://msdn2.microsoft.com/en-us/library/ms141679.aspx

-David

|||

Hi,

I followed the lonk.
Not sure why I do not get the error columns expanded as it says in the link.

Still do not know how to pull the column out i.e. I have a stored procedure which I would like to record the error columns. How do I get these error columns name and rows?

Thanks

Error catching in SSIS

I have a package that is failing parsing a CSV file. I want to write the failing line to an error file and continue processing. I've added error output to the flat file source, that didn't work. I've added an onerror event handler to the data flow task, that didn't work. I've added an event handler to the package, that didn't work. How can i output the offending line but not have the process fail?

Go to the Error Output page on the Flat File Source editor and set appropriate Error and Truncation values to Redirect. After this connect your error output to a Flat file destination where you can take care of erroneous rows.

Thanks,

Bob

|||Thanks, but that isn't it. I've got it set to redirect. The error is

Error: 0xC0202055 at Data Flow Task, Flat File Source [1]: The column delimiter for column "Column 3" was not found.

It is being caused by an embeded double quote in the source CSV file. It's escaped to "" and the parser blows up. It dosent go to the error output. and i can't seem to catch the error, it kills the process.

|||

The flat file parser cannot handle embedded quotes. You will have to load qualified data and post-process it using script or derived columns.

Thanks,

Bob

Error catching in SSIS

I have a package that is failing parsing a CSV file. I want to write the failing line to an error file and continue processing. I've added error output to the flat file source, that didn't work. I've added an onerror event handler to the data flow task, that didn't work. I've added an event handler to the package, that didn't work. How can i output the offending line but not have the process fail?

Go to the Error Output page on the Flat File Source editor and set appropriate Error and Truncation values to Redirect. After this connect your error output to a Flat file destination where you can take care of erroneous rows.

Thanks,

Bob

|||Thanks, but that isn't it. I've got it set to redirect. The error is

Error: 0xC0202055 at Data Flow Task, Flat File Source [1]: The column delimiter for column "Column 3" was not found.

It is being caused by an embeded double quote in the source CSV file. It's escaped to "" and the parser blows up. It dosent go to the error output. and i can't seem to catch the error, it kills the process.

|||

The flat file parser cannot handle embedded quotes. You will have to load qualified data and post-process it using script or derived columns.

Thanks,

Bob