Geek Answers Handbook

SQL Server: figuring out which row caused TSQL failure (SSIS)

SQL Server 2005 Question:

I am working on a data conversion project where I take 80k + rows and move them from one table to another. When I run TSQL, it bombes various errors related to type conversions or something else. Is there any way to find out which line caused the error?

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

UPDATE:

I am doing INSERT INTO TABLE1 (...) SELECT ... FROM TABLE2 Table 2 is just a bunch of varchar fields where TABLE1 has the correct types.

This script will be placed in sproc and executed from the SSIS package. First, the SSIS package imports 5 large flat files into TABLE2.

Here is an example error message: "Converting a char data type to a date and time data type resulted in a date and time value out of range."

There are many date fields. Table 2 contains data values ​​such as '02/05/1075' for the date of birth. I want to examine every line that causes an error, so I can tell the department responsible for the bad data so that they can fix it.

+5
source share
7 answers

What I'm doing is splitting a 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 ​​between the part of the where clause. I have done this manually many times, but it occurs to me that you can automate splitting with a small .Net code in a loop, eliminating the catch exceptions, and then narrowing it down to a line throwing an exception a little bit.

+3

SSIS. , , , . , , , .

- , , , .

, SSIS, .

+5

, INSERT INTO...

, , , : , , ..

+2

, - 4 . , , , , .

select row_number() over (order by TimeID) as rownum,timeID into #TestingTable from MyTableWithBadData

set nocount on
declare @row as int
declare @last as int
set @row=0
select @last = count(*) from #TestingTable
declare @timeid as decimal(24,0)
create table #fails (rownum int)
while @row<=@last
begin
    Begin Try
        select @timeid=cast(timeID as decimal(24,0)) from #TestingTable where rownum = @row 
    end try
    begin catch 
        print cast(@row as varchar(25)) + ' : failed'
        insert into #fails(rownum) values(@row)
    end catch
    set @row = @row+1
end
+2

, .

, WHERE . ( , ), . N , .

ADD CASE , ( NULL whetever) FlagColumn, :

CASE WHEN ISNUMERIC(x)!=1 then NULL ELSE x END as x
,CASE WHEN ISNUMERIC(x)!=1 then 'not numeric' else NULL END AS FlagColumn

, FlagColumn IS NOT NULL

select isnumeric() isdate()

EDIT

. 2, , '02/05/1075 ' . , , , , .

, :

SELECT * FROM YourTable WHERE ISDATE(YourDateColumn)!=1
+1

, . , , SQL ACID .

0

, ​​ SSIS. , SSIS , . . , , , . , . - :

 insert exceptiontable (field1, field2)
 select field1, field2 from table2 where isdate(field2) = 0

 insert table1 (field1, field2)
 select field1, field2 from table2 where isdate(field2) = 1

, , , .

0

More articles:


All Articles