Insert Query return result

[sardinka2]

In my DTS I have thhi line:
Main= DTSTransformstat_InsertQuery
However sometimes my insertQuery is failing.
How do I find if Insert was sucess or fails?

 

[JayKusch]

If you have SQL Mail set up on the server you could set a "workflow" of "On Failure" from your insertquery task to a "Send Email Task".

Thanks

J. Kusch

 

[sardinka2]

I don't have a server set up tp handle email. I just wanted to skip that row and write to the error.file

 

[JayKusch]

double-click on your "Transform Data Task"/insertquery and in the properties of this task you will find the Options tab.

In this tab you are able to set the error/exception file to log to as well as set a number of failures you can accept by setting the default value of 0 in the "Max Error Count" box to your desired number.

Thanks

J. Kusch

 

[sardinka2]

WHAT IF MY ERROR RICH THE MAX MAX ERROR? MY DTS WILL STOP ONES IT RECHES THE MAX OF ERROR COUNT. I WANTED TO DTS RUN EVEN IT REACHES THE MAX OF ERROR COUNT.

 

[JayKusch]

I am not sure how you override the max value. What use is the data if you do not care what is imported and what is not.

Thanks

J. Kusch

 

[MDXer]

If your data quality is so poor that you anticipate a large number of records to not insert you are better off importing to some type of temp table then running some data cleansing routines to improve the data, and possible suspend bad records. Like Jay said what good is it if you don't know what was not inserted or why it wasn't.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[sardinka2]

I do care about data, just I can't assumed that the max error count will be the max error. I am getting the data external and I have no idea what they can give.
I've notice when Main= DTSTransformstat_InsertQuery and it fails Main=64 andon susecc Main=16.
can I rely on this assuption?

 

[MDXer]

since the issue is in your data you can't really rely on anything, because today's issue may not be the same as tomorrows. If you know that 100% of the time the data will contain the same errors in the same columns you can use ActiveX transforms to handle the issues during import if not your best bet is to import it into a generic table and write stored procs to move the data following a set of criteria specified in you insert statement. Using this technique you could filter out or handle bad data.


"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[sardinka2]

I am using activex to transfer the data inoto generic table with possible error I know of which I am hadaling, however back to my original question I wanted to have more error support.