Running DTS packages from ASP
-
Hello everyone,
I wonder if you could offer some much needed advice.
I am attempting to run a DTS package from an ASP page..simple enough. I have found the Microsoft KB article that clearly shows the code...
I am trying to run the ASP page locally on my workstation with my local SQL (to test..).
It is a simple package (export some data to excel) but it fails at the DataPump task.
I've also read about the permission issues when running a package from a web page. But I'm stumped. It runs ok in EM.
Are there any specific permission settings I need to deal with ????
Many Thanks
Graeme
-
What is the error message that the package fails with? Do you have Package Logging enabled? How does your ASP page connect to the SQL Server?
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
-
Here's the code that connects to the database
Const DTSSQLStgFlag_Default = 0
Const DTSStepExecResult_Failure = 1
Dim oPkg, oStep, sErr, bSuccess
Dim sServer, iSecurity, sUID, sPWD, sPkgName, sPkgPWD
' Get Form Variables
sServer = Request.Form("txtServer")
iSecurity = CInt("0" & Request.Form("optSecurity"))
sUID = Request.Form("txtUID")
sPWD = Request.Form("txtPWD")
sPkgName = Request.Form("txtPkgName")
sPkgPWD = Request.Form("txtPkgPWD")
' Validate Variables
If Len(sServer) = 0 Then sErr = "<li>Invalid Server Name"
If iSecurity = 0 And Len(sUID) = 0 Then _
sErr = sErr & "<li>Invalid Username for SQL Security"
If Len(sPkgName) = 0 Then sErr = sErr & "<li>Invalid Package Name"
If Len(sErr) = 0 Then
Response.Write "<p>Executing " & sPkgName & " from " & sServer & "</p>"
' Load the Package
Set oPkg = Server.CreateObject("DTS.Package")
oPkg.LoadFromSQLServer sServer, sUID, sPWD, iSecurity , sPkgPWD, "", "", sPkgName
' Execute the Package
oPkg.Execute
bSuccess = True
' Report Step status
For Each oStep In oPkg.Steps
sErr = sErr & "<p> Step [" & oStep.Name & "] "
If oStep.ExecutionResult = DTSStepExecResult_Failure Then
sErr = sErr & " failed<br>"
bSuccess = False
Else
sErr = sErr & " succeeded<br>"
End If
sErr = sErr & "Task """ & _
oPkg.Tasks(oStep.TaskName).Description & """</p>"
Next
If bSuccess Then
sErr = sErr & "<p>Package [" & oPkg.Name & "] succeeded</p>"
Else
sErr = sErr & "<p>Package [" & oPkg.Name & "] failed</p>"
End If
End If
All I'm geeting is the page returning the status failed.
I haven't got package logging turned on! When I try to turn it on, there is a message saying it only runs on an instance of SQL2000. I am running the 2k on my machine?? I am connecting to a SQL7 server with the package.
Thanks again
Graeme
-
Hello again,
Well everything pointed to permission issues and it turns out to be a permission on a table.
Thanks again
Graeme
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply