Hi,

I have written an ETL application with SQL Server 2000 DTS that, among other
thing, loads sequential files into an ODS database. I have used Data Driven
Query component, with Data Pump Phases turned on, and Microsoft Data Link
Component to insert or update rows in tables.

When database server catch an exception during sql statement execution, for
example a constraint violation, On Insert Failure event is fired; I have
provided a event handler that logs the error condition for the sql command.

My problem is that I don't know how to capture error reason; I have looked
for a solution in SQL Server 2000 documentation, but I haven't found a good
one. Documentation suggests to check , inside the event handler , causes of
well known errors (violation of primary or foreing keys, not null fields
etc. ) using sql commands via ADO. This kind of management introduces
useless complexity to event handler just for getting what database server
has already sent: the error message.



Does anyone know how to catch database error return code and message inside
handlers of Post Row Transformation Phase ???

I am having the same problem. Have you found a solution that works

Not a good solution, I have just realized a function that does again the
query (this time with ADO) that caused exception then I capture the message.
I have seen that DTS components expose some OLEDB interfaces (see
Disconnected Edit context menu item of the Package) but I don't known OLEDB
specifications.

This is the JScript function that I have used.
Patameters:
- fileUDL = UDL file for connection to database
- ddqTask = reference to CustomTask property of Data Driven Query component
where the script runs (example:
DTSGlobalVariables.Parent.Tasks("DTSTask_DTSDataDr ivenQueryTask_1").CustomTask)
- transformStatus = TransformStatus property of script object
DTSTransformPhaseInfo
- defaultMessage = can be null

Function:
function ADOErrorMessage(fileUDL, ddqTask, transformStatus, defaultMessage)
{
var _adoConnection = new ActiveXObject("ADODB.Connection");
var _adoRecordSet = new ActiveXObject("ADODB.Recordset");
var _command = new ActiveXObject("ADODB.Command");


//Recupero del messaggio ADO rieseguendo il comando che ha generato
l'errore

var query = null;
var columns = null;
var errMsg = defaultMessage;

if ((transformStatus & DTSTransformStat_InsertQuery) ==
DTSTransformStat_InsertQuery){
query = ddqTask.InsertQuery;
columns = ddqTask.InsertQueryColumns;
}
else if ((transformStatus & DTSTransformStat_UpdateQuery) ==
DTSTransformStat_UpdateQuery){
query = ddqTask.UpdateQuery;
columns = ddqTask.UpdateQueryColumns;
}
else if ((transformStatus & DTSTransformStat_DeleteQuery) ==
DTSTransformStat_DeleteQuery){
query = ddqTask.DeleteQuery;
columns = ddqTask.DeleteQueryColumns;
}
else if ((transformStatus & DTSTransformStat_UserQuery) ==
DTSTransformStat_UserQuery){
query = ddqTask.UpdateQuery;
columns = ddqTask.UpdateQueryColumns;
}

_adoConnection.Open("file name=" + fileUDL);
try {
_command.ActiveConnection = _adoConnection;
_command.CommandText = query;

for(n=1; n<=columns.Count; n++)
_command.Parameters.Item(n-1).Value = DTSDestination(columns(n).Name);

_command.Execute();
}
catch(e){
errMsg = e.description;
errMsg = errMsg.replace(/'/g,"''");
}
finally{
_adoConnection.Close();
}

return errMsg;
}

Advise me if you find a better way, maybe through OLEDB interfaces.

"curttun" <curt.teunissen (AT) associatedfinancialgroup-dot-com (DOT) no-spam.invalid>
ha scritto nel messaggio news:9rWdne4JXKeP-7jfRVn_vQ (AT) giganews (DOT) com...