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
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
what language are you using for the activex script?
ASKER
Hello,
I am using VBScript
Thanks
I am using VBScript
Thanks
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.
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.
ASKER
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!
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.
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.
ASKER
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("gFailu re").Value = 1
TransFailureMain = DTSTransformstat_OK
End Function
Function InsertSuccessMain()
DTSGlobalVariables("gSucc" ).Value = 1
InsertSuccessMain = DTSTransformstat_OK
End Function
Function InsertFailureMain()
DTSGlobalVariables("gFailu re").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("gFailu re").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!
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
DTSDestination("order_nums
DTSDestination("store_id")
Main = DTSTransformStat_OK
End Function
Function TransFailureMain()
DTSGlobalVariables("gFailu
TransFailureMain = DTSTransformstat_OK
End Function
Function InsertSuccessMain()
DTSGlobalVariables("gSucc"
InsertSuccessMain = DTSTransformstat_OK
End Function
Function InsertFailureMain()
DTSGlobalVariables("gFailu
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("gFailu
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.
You can then branch out using a workflow On Failure to another process.
ASKER
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..
Thanks again..
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a bunch ac!!!That works like a charm! As u might have guessed I am new to this!!Thanks!