Running DTS packages from ASP

Question

Running DTS packages from ASP

Graeme100

SSCrazy Eights

Points: 8054
More actions
December 9, 2003 at 5:42 am

#107395

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47796 More actions · Answer 1

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

Graeme100 SSCrazy Eights Points: 8054 More actions · Answer 2

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 "Executing " & sPkgName & " from " & sServer & ""

' 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 & " Step [" & oStep.Name & "] "

If oStep.ExecutionResult = DTSStepExecResult_Failure Then

sErr = sErr & " failed "

bSuccess = False

Else

sErr = sErr & " succeeded "

End If

sErr = sErr & "Task """ & _

oPkg.Tasks(oStep.TaskName).Description & """"

sErr = sErr & "Package [" & oPkg.Name & "] succeeded"

Else

sErr = sErr & "Package [" & oPkg.Name & "] failed"

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

Graeme100 SSCrazy Eights Points: 8054 More actions · Answer 3

Hello again,

Well everything pointed to permission issues and it turns out to be a permission on a table.

Thanks again

Graeme