DTS Error checking

Hello,
What would be good syntax to add to a DTS package to make sure that all of
the records from a live system made it into a warehouse table? Would it be
as simple as a COUNT(*) - and if so, how would I use that to notify or fail
if all of the records do not make it into the warehouse?
Thanks!Something like count would suffice but is not necessary. The dts either take
s
all the rows or it does not. How you log what fails is more importante. See
step2.
If I was working with a datawarehouse i would seriously entertain the idea
of a rock solid logging system. Here are some guidelines that could work for
y0ou.
Step1: For each transformation Step in the DTS, have an 'On Success' & 'On
Fail' workflow. The Success could goto a SQL Task and do a count and log
through a sProc For eg:
EXEC sp_lo_insert_DTSlog ?, 'DTSEXECUTESTEPS', 'STATUS', 'DTS Package :
DTSName Completed Successfully'
If successgul the DTS will execute the next transformation step in the
sequence and the failure would execute an ActiveX Script Task.(see step2)
Step2: ActiveX Script Named Fail Step1
Function Main()
Dim oPackage
Set oPackage = DTSGlobalVariables.Parent
DTSGlobalVariables("gvPkgStatusDesc").Value = "ERROR : DTS Package " &
DTSGlobalVariables("gvPackageName").Value & _
" - Failed to load STAGING table TableName"
oPackage.Steps("DTSStep_DTSExecuteSQLTask_1").DisableStep = 0
Set oPackage = Nothing
Main = DTSTaskExecResult_Success
End Function
All you need is on http://www.sqldts.com/

DTS

I used the DTS to transfer data to a new server, I received an error message "Bulk copiy failure", but in checking my destination datanbase data was indeed transfered.
Is it safe to assume that there are no errors, or what is the source of the error?
Thanks
You can receive this error if there are blank lines in the end of the
file you are transferring and the destination has non-nullable columns.
You should try to eliminate blank rows from the source file, use an
ActiveX task to skip rows that are blank or bring the data into a
holding table with all nullable columns then copy the data from there to
your real tables.
Regards,
William D. Bartholomew
http://blog.bartholomew.id.au/
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

DTS

I used the DTS to transfer data to a new server, I received an error message "Bulk copiy failure", but in checking my destination datanbase data was indeed transfered
Is it safe to assume that there are no errors, or what is the source of the error
ThanksYou can receive this error if there are blank lines in the end of the
file you are transferring and the destination has non-nullable columns.
You should try to eliminate blank rows from the source file, use an
ActiveX task to skip rows that are blank or bring the data into a
holding table with all nullable columns then copy the data from there to
your real tables.
Regards,
William D. Bartholomew
http://blog.bartholomew.id.au/
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

DTS

I used the DTS to transfer data to a new server, I received an error message
"Bulk copiy failure", but in checking my destination datanbase data was ind
eed transfered.
Is it safe to assume that there are no errors, or what is the source of the
error?
ThanksYou can receive this error if there are blank lines in the end of the
file you are transferring and the destination has non-nullable columns.
You should try to eliminate blank rows from the source file, use an
ActiveX task to skip rows that are blank or bring the data into a
holding table with all nullable columns then copy the data from there to
your real tables.
Regards,
William D. Bartholomew
http://blog.bartholomew.id.au/
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!