June 29, 2004 at 10:16 am
Hi. I have an Excel spreadsheet of EmployeeID and Employee Address that I have to import into the Address Table. I do my right lookups and set the Destination Columns from the Source Columns in my Transformation Script and then execute
Main = DTSTransformStat_InsertQuery.
But, I have found that there are a few rows in the Excel spreadsheet with EmployeeID NULL which is unacceptable. I want to generate a Message box whenever the Insert query fails. How do I do that? THanks. Here is the VB Script Main() function.
Function Main()
dim EmployeeID
EmployeeID = DTSLookups("EmployeeID Lookup").Execute(DTSSource("BarID"))
DTSDestination("EmployeeID") = EmployeeID
DTSDestination("ContactTypeCode") = "CT"
DTSDestination("EmailAddr") = DTSSource("Atty Email Address")
DTSDestination("AddrStreet1") = DTSSource("Street Address 1")
DTSDestination("AddrStreet2") = DTSSource("Street Address 2")
DTSDestination("City") = DTSSource("City Name")
DTSDestination("State") = DTSSource("State Code")
DTSDestination("Zip") = DTSSource("Postal Code ID")
Main = DTSTransformStat_InsertQuery
End Function
June 29, 2004 at 11:04 am
Try one of these flavors:
If Len(EmpID) < 1 Or IsEmpty(EmpID) Or IsNull(EmpID) Then
MsgBox "EmpID was not found."
End If
Don't use a msgbox on the Server! That's where it'll appear if you run this as a job. Just write the message to a global string in your Active X. Use an Execute Process Task instead and net send yourself. You can use the Dynamic Properties Task to create the net send message on the fly.
Active X > Dynamic Properties Task > Execute Process Task
Or write all the failures to an error file or a table and SendMail yourself.
[font="Courier New"]ZenDada[/font]
June 30, 2004 at 8:35 am
I am running the Package on my local SQL Server. Why am I not seeing any Msgbox? Since this is only a test, I will remove the MsgBox when I move the Package to the test environment. Thanks for responding.
June 30, 2004 at 9:33 am
Because you didn't go into the IF.
Test it:
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
If 1=1 Then
MsgBox "one equals one."
Else
MsgBox "hell froze over."
End If
Main = DTSTaskExecResult_Success
End Function
Since none of your conditions were met, you need to discover what's really in the field that you are testing for.
[font="Courier New"]ZenDada[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply