SpaceManagers
asked on
How to call DTS package in VBA and handle errors?
We are calling a SQL Server 2000 dts package from VBA code in AutoDesk's AutoCAD 2006.
We cannot use xpcmdshell on the server because we do not have the necessary rights within this corporate environment, so we call the package using the dtsrun utility on the client machine.
The code calls the package and passes the necessary parameters which are used as global variables within the package.
Our problem is that the VBA code, having called the package proceeds immediately and reports a successful completion. However, we need to have the dts package report back that it has completed successfully...or to report any errors it may have trapped.
I have attached the code we use in VBA.
Any help, even pointers to where there is a clear explanation of calling dts packages from VBA would be appreciated
We cannot use xpcmdshell on the server because we do not have the necessary rights within this corporate environment, so we call the package using the dtsrun utility on the client machine.
The code calls the package and passes the necessary parameters which are used as global variables within the package.
Our problem is that the VBA code, having called the package proceeds immediately and reports a successful completion. However, we need to have the dts package report back that it has completed successfully...or to report any errors it may have trapped.
I have attached the code we use in VBA.
Any help, even pointers to where there is a clear explanation of calling dts packages from VBA would be appreciated
Function RunRADPublishDTS()
'Must have the Microsoft DTSPackage Object Library (C:\Program Files\Microsoft SQL Server\80\tools\binn\dtspkg.dll)
'Run package stored in file C:\DTS_UE\TestPkg\VarPubsFields.dts.
On Error GoTo error
Dim oPackage As DTS.package
Dim oStep As DTS.Step
Dim oTask As DTS.Task
Dim oCustTask As DTS.ExecutePackageTask
Dim strUser As String
Dim strPass As String
Dim strPackageName As String
Dim strWinUserName As String
Dim varErrorCode As Variant
strWinUserName = Environ("USERNAME")
strPackageName = "dtsRADPositionRTU"
strUser = "xxxxxx"
strPass = "xxxxxx"
Set oPackage = New DTS.package
oPackage.LoadFromSQLServer strSQLserver, strUser, strPass, DTSSQLStgFlag_Default, strPass, , , strPackageName
oPackage.GlobalVariables("strDwgName").value = Left(ThisDrawing.Name, Len(ThisDrawing.Name) - 4)
oPackage.GlobalVariables("strCADUser").value = strWinUserName
oPackage.FailOnError = True
oPackage.Execute
Set oPackage = Nothing
RunPublishDTS = Err.Number
Exit Function
error:
RunPublishDTS = Err.Number
MsgBox Err.Description
End Function
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I would have to guess that you do not have the workflow set up correctly. I suggest you post an iamge of the DTS Package here with all the relevant explanations of their functions.
Did you manage to solve the problem SpaceManagers?
http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1137289,00.html#