SQL Server: Find out what row caused the TSQL to fail (SSIS)

Question

SQL Server 2005 Question:

I'm working on a data conversion project where I'm taking 80k+ rows and moving them from one table to another. When I run the TSQL, it bombs with various errors having to do with converting types, or whatever. Is there a way to find out what row caused the error?

=====================

UPDATE:

I'm performing an INSERT INTO TABLE1 (...) SELECT ... FROM TABLE2 Table2 is just a bunch of varchar fields where TABLE1 has the right types.

This script will be put into a sproc and executed from an SSIS package. The SSIS package first imports 5 large flat files into TABLE2.

Here is a sample error message: "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

There are many date fields. In TABLE2, there are data values like '02/05/1075' for Birthdate. I want to examine each row that is causing the error, so I can report to the department responsible for the bad data so they can correct it.

Answer 1

This is not the way to do it with SSIS. You should have the data flow from your source, to your destination, with whatever transformations you need in the middle. You'll be able to get error details, and in fact, error rows by using the error output of the destination.

I often send the error output of a destination to another destination - a text file, or a table set up to permit everything, including data that would not have been valid in the real destination.

---

Actually, if you do this the standard way in SSIS, then data type mismatches should be detected at design time.

Answer 2

What I do is split the rowset in half with a WHERE clause:

INSERT MyTable(id, datecol) SELECT id, datecol FROM OtherTable WHERE ID BETWEEN 0 AND 40,000

and then keep changing the values on the between part of the where clause. I've done this by hand many times, but it occurs to me that you could automate the splitting with a little .Net code in a loop, trapping exceptions and then narrowing it down to just the row throwing the exception, little by little.

Answer 3

I assume you do the update with the INSERT INTO ...

Instead try to do the update with the cursor, use exception handling to catch the error and log all you need: the row number it failed on etc.