Link to home
Start Free TrialLog in
Avatar of dummie_q
dummie_q

asked on

Capture dts errors in activex script

Hello,

I am a problem with the DTS that I have created and I am not sure how to solve this. Basically what I have is a DTS which performs multiphase datapump. I have used activex scripting for doing the data transformation. During the transformation, if there is an error then I initialize global variables to certian values based on which I am sending emails in an activexscript that is defined to execute upon the completion of each phase of transforms. My problem now is how to get/capture the actual error description within the activex script. Is it possible to do this? If not, what is the correct approach I need to take in order to capture the error?

Thanks a lot in advance,

regards
Avatar of curlypinhead
curlypinhead
Flag of United States of America image

what language are you using for the activex script?
Avatar of dummie_q
dummie_q

ASKER

Hello,

I am using VBScript

Thanks

Avatar of Anthony Perkins
You basically have two options:
1. Use ActiveX script and add Error Handling with the archane On Error Resume Next construct and check for Errors after each likely statement.
2. Don't use ActiveX script and instead use a staging table.  Here you validate the data prior to importing.
Hello,

Thanks a lot. But I am not quite sure how to go about doing this. I guess I can get some examples for the first method you suggested. I will try and search for them. But, can u tell me how to go about tackling the problem using the second approach? Can I still captuare the errors and email myself these errors?

Thanks a lot again!
>>I will try and search for them<<
The "On Error Resume Next" construct has been around for the last 20 years (and it shows) It goes some thing like this:

On Error Resume Next

' Statement that could fail
IF Err.Number <> 0 Then            
   On Error GoTo 0
    ' Your code to handle when there are problems

Else
    ' Your code to handle when OK
End If


As you can see this can get very ugly, very fast.  A better approach as I mentioned previously is to use a staging table and import all the rows into this table.  You can then run some validation on it, reporting any errors or if all is OK import to the production tables.
Hello acperkins,
I am actually using a buffer table to which I import data from various tables. Thats the reason I am having to use multiphase datapump. After I import data to this buffer/staging table, based on the values I either update or add new records to the main table. This is where I am having a problem. What I want to do is to capture an error that might occur when I am importing/transforming the data from src table to staging table. So what is happening now is that if there is an error I intialize global variable like this:

(This is the ActiveXScript for data transformtion)

Function Main()
      DTSDestination("qty") = DTSSource("Expr2")
      DTSDestination("order_date") = DTSSource("Expr1")
      DTSDestination("order_nums") = DTSSource("ord_num")
      DTSDestination("store_id") = DTSSource("stor_id")
      Main = DTSTransformStat_OK
End Function

Function TransFailureMain()
      DTSGlobalVariables("gFailure").Value = 1
      TransFailureMain = DTSTransformstat_OK
End Function
Function InsertSuccessMain()
      DTSGlobalVariables("gSucc").Value = 1
      InsertSuccessMain = DTSTransformstat_OK
End Function

Function InsertFailureMain()
      DTSGlobalVariables("gFailure").Value = 1
      InsertFailureMain = DTSTransformstat_OK
End Function

Then once the step is completed, the next step is an ActivexScript task where in I check the globalvariable and if gFailure is set to 1 I send an email. Its something like this:

Function Main()
if  DTSGlobalVariables("gFailure").Value = 1 then
SendMail
      Main = DTSTaskExecResult_Success
End Function

But how can I capture and send a detailed error message?

Thanks a lot again for the help!


If there is an error than set Main = DTSTaskExecResult_Failure

You can then branch out using a workflow On Failure to another process.
Well actually, I tried that. What I did was instead of using an activex scripting object on task completion, i redirected to sql task after failure. But what I still don't understand is how to actually get hold of the detailed error message. I have the rest of the DTS working well. Also, when there is an error, the mail is sent. Only thing lacking is the detailed error description. I don't know how to capture this. Can you help me with that?

Thanks again..
ASKER CERTIFIED SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks a bunch ac!!!That works like a charm! As u might have guessed I am new to this!!Thanks!