how can i get the error no. of a certain sql statement?
i'm using sql query analyzer, here's my code:
declare @errorno as int
set @errorno='insert into table2 (select * from table1)'
for example the insert statement results to an error...can i assign the error no. to the variable @errorno? and view it by typing 'select @errorno'. and what if the insert statement does not result to an error? what will be the value of the @errorno?
The best way to do this is to move all that complex logic out of DTS. DTS sucks a$$ (lots of them).
Create an unconstrained staging table.
Use DTS to get data from the filesystem into the staging table.
Call a proc that cleans and screens the data in the staging table.
Insert the data into the production tables.
Seriously - trying to handle multi path evaluations and error handling in DTS is like throwing valuable development time down the drain. If that doesn't persuade you, DTS is deprecated in SQL 2005 (and that isn't even the current version of SQL Server) so you'll have to rewrite the thing one day anyway.
Keep business logic OUT of DTS and SSIS packages.
The best way to handle this is to drop the data into a staging table and run a sproc to verify it, cleanse it, parse it, and distribute it to production tables.
If it's not practically useful, then it's practically useless.