CODEWITHSUNDEEP
sql-serverexcelssis
stoic_monk
stoic_monk

Reputation: 185

Bad Data in Excel source does not generate error in SSIS

I have a quick question regarding SSIS. I am developing a package that performs a Data Flow task from an Excel Source into OLE DB Connection. The columns in the database should allow nulls. However there is a problem in that when I enter bad data into the numeric columns in the excel spreadsheet, it will not cause the Data Flow task to fail as I would like it to. I tried to remedy this by explicitly trying to convert any numeric columns in the Derived Column step, however the same thing occurs-- if I enter abc into the Excel numeric column, if just turns out as NULL in the db after the package runs. I do want to allow for NULLS, but I'd like the package to fail if the data is corrupt.

Any advice would be appreciated :)

Upvotes: 0

Views: 1084

Answers (2)

Ciarán
Ciarán

Reputation: 3057

I've just tried this and Ignore/Redirect/Fail setting doesn't appear to have any effect, NULLs get updated into the database regardless.

If you didn't want NULLs I would suggest that you amend the definition of your destination table to specify a NOT NULL constraint on the columns you wish to be numeric. That way the database update and the package will fail.

But since you want null columns the only thing I can suggest is that you write a script task or script component to read and validate the data before accepting it.

Alternatively, read the Excel file into a staging area where all the columns are VARCHAR and then validate it via SQL

Upvotes: 1

YvesR
YvesR

Reputation: 6222

If you edit your SSIS task where you define the import you can choose the error handling for each column. There you can choose to set it to fail and stop, to ignore and go on, etc.

This links should help you to handle it on your needs:

http://sqlblog.com/blogs/rushabh_mehta/archive/2008/04/24/gracefully-handing-task-error-in-ssis-package.aspx

and

http://sqlserver360.blogspot.de/2011/03/error-handling-in-ssis.html

and

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

Upvotes: 1

Related Questions