SQL Server Performance Forum – Threads Archive
Problems Executing DTS from ASP
I have a problem with executing a DTS package from ASP.<br /><br />The network configuration is as follows:<br /><br />Svr1 WEB<br />IIS<br /><br />Svr3<br />(Domain Controller)<br />SQL Server 2000 Std.<br /><br />I have some existing scripts that i have been running manually to perform administrative tasks – Eg add users to Active Directory, Create directories etc.<br /><br />Rather than run these scripts manually, I would like to convert them to DTS Active X scripts which can then be executed via ASP pages on the Web Server.<br /><br />I have created an AD Group account, and AD User which is a member of that Group. <br /><br />I have configured a web site on Svr1 which is set to use Basic Authentication and given the AD Group account access to the site.<br />I have configured a SQL server login for that Group account also.<br /><br />I successfully gain access to the web site and execute DTS packages via the ASP code. My problem appears to be relating to permissions as when the package is run from <br /><br />Enterprise Manager it is successfull. Also simple scripts don’t try to access any resources succeed. An example script that is failing is this simple attempt to connect to <br /><br />a database:<br /><br /><br />Function Main()<br />on error resume next<br /><br />Dim rs,db<br /><br /><br />Const ConnStr = "provider=SQLOLEDB;Server=Svr3;Trusted_Connection=yes;database=Logins;"<br /> <br /> Set db = CreateObject("ADODB.Connection")<br /> db.CursorLocation = 3 ‘adUseClient<br /> db.Open ConnStr<br /><br />if err.number <> 0 then<br />Main = DTSTaskExecResult_Failure<br />Exit Function<br />end if<br /><br /><br />rs.Close<br />set db=nothing<br /><br />Main = DTSTaskExecResult_Success<br />End Function<br /><br />The same DTS package executed manually from Enterprise Manager (Logged in as the Domain Administrator account – which is the owner of the package) will execute <br /><br />successfully.<br /><br />I have also tried logging in to the web site using the Domain Administrator account however the package still fails.<br /><br />I have also tried using SQL authentication to pass the SA account credentials in the call to the DTS package however same result.<br /><br />I have turned logging on for the DTS package, however no logs are created when the call to DTS has come from ASP.<br /><br /><br /><br /><br />The ASP code used is as follows:<br /><br />dim objDTSPackage <br /><br />dim objDTSStep <br /><br />dim strResult <br /><br />dim blnSucceeded<br /><br />const DTSSQLStgFlag_Default = 0 <br />const DTSSQLStgFlag_UseTrustedConnection = 256<br /><br />const DTSStepExecResult_Failure = 1<br /><br />set objDTSPackage = Server.CreateObject("DTS.Package") <br /><br />blnSucceeded = true<br /><br />objDTSPackage.LoadFromSQLServer "Svr3", "", "", DTSSQLStgFlag_UseTrustedConnection, "", "", "", "rnPkg" <br /><br /><br />objDTSPackage.Execute<br /><br />for each objDTSStep in objDTSPackage.Steps <br /><br />if objDTSStep.ExecutionResult = DTSStepExecResult_Failure then <br /><br />strResult = strResult & "Package " & objDTSStep.Name & " failed.<br>" <br /><br />blnSucceeded = false <br /><br />else <br /><br />strResult = strResult & "Package " & objDTSStep.Name & " succeeded.<br>" <br /><br />end if <br /><br />next<br /><br />if blnSucceeded then <br /><br />Response.Write "<h1><img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ackage Succeeded</h1>" <br /><br />else <br /><br />Response.Write "<h1><img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ackage Failed</h1>" <br /><br />end if<br /><br />Response.Write strResult <br /><br /><br />Any help greatly appreciated.<br /><br /><br /><br />Best Regards,<br /><br /><br />Richard.<br /><br />Create a job to run the DTS package and dont schedule the job but leave it enabled.
Then call that job FROM ASP to run the DTS.
Try doing this and im sure this will run perfectly.
Ranjijain, What is the best way to have ASP initiate the execution of the job – via stored_proc? Thanks again for your help.
You can also write a procedure to run the DTS using DTSrun utility.
Then simply call the procedure from ASP end which in turn will run DTS using DTSRun utility.
Refer this also
www.sqldts.com Madhivanan Failing to plan is Planning to fail
http://www.sqlteam.com/item.asp?ItemID=19595 – link about ASP calling. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
]]>