Running SQL Server DTS Package from ASP.NET page problem

I'm having some problems running a DTS package from my ASP.NET appllication. It runs prefectly in debug from my local machine but as soon as I move it to the web server I receive the following error:

Error: -2147467259
Source: Microsoft JET Database Engine
Description: The Microsoft Jet database engine cannot open the file '\\serverB\PayFiles\Pay3.xls'. It is already opened exclusively by another user, or you need permission to view its data.

The file is not opened by anyone else and the share permissions are full control for everyone.

My set up is as follows

serverA - Webserver & DB Server
serverB - File Server

The DTS package pulls in the file from serverB to a database table on serverA. The sa account is used for the database connection and connecting to the DTS package from the application. My code for running the DTS package is as follows -

Private Sub ExecutePackage(ByVal serverAs String,ByVal packageAs String,ByVal usernameAs String,ByVal passwordAs String)Const DTSSQLStgFlag_Default = 0Const DTSStepExecResult_Failure = 1Dim oPKGAs DTS.Package, oStepAs DTS.Step oPKG =New DTS.PackageDim sServerAs String, sUsernameAs String, sPasswordAs String Dim sPackageNameAs String, sMessageAs String Dim lErrAs Long, sSourceAs String, sDescAs String' Set Parameter Values sServer = server sUsername = username sPassword = password sPackageName = package' Load Package oPKG.LoadFromSQLServer(sServer, sUsername, sPassword, _ DTSSQLStgFlag_Default, , , , sPackageName)' Set Exec on Main ThreadFor Each oStepIn oPKG.Steps oStep.ExecuteInMainThread =True Next' Execute oPKG.Execute()' Get Status and Error MessageFor Each oStepIn oPKG.StepsIf oStep.ExecutionResult = DTSStepExecResult_FailureThen oStep.GetExecutionErrorInfo(lErr, sSource, sDesc) sMessage = sMessage &"Step " & oStep.Name &" Failed<br><br>Error: " & lErr &"<br>Source: " & sSource &"<br>Description: " & sDesc &"<br><br>"Else sMessage = sMessage &"Step " & oStep.Name &" Succeeded<br>"End If Next oPKG.UnInitialize() oStep =Nothing oPKG =Nothing' Display Results 'MsgBox(sMessage) lblOutput.Text = sMessageEnd Sub

I'm using windows authentication with identity impersonate set to true. If I change to use basic authentication and enter my login when I go to the site the DTS package runs successfully. So I assume that I'm seeing this 'double hop' issue.

serverA is for testing so I moved the DTS package and web application to serverB so now everything is on the same server and should eliminate the double hop? No! Works perfectly from my PC in debug, but from the web server the same error occurs. Changing to basic authentication does not workaround the issue on the live server.

The DTS package always works when run from Enterprise manager.

I know this is a premissions issue - does anyone know what I'm doing wrong? Hopefully I've supplied enough info, if not just ask!

You may want to use a windows service instead of a web page, as when the web page stops rendering the process will also stop.

|||

If I use a windows service will I still be able to give the user control over when the package is run?

|||

I've decided to use a stored procedure and DTSRun to do this instead and then simply execute the stored procedure from my page. Just in case anyone else wants to use it this is my code.

CREATE PROC [dbo].[sp_DTSRun] @.ServerNameVARCHAR(30),@.PackageNameVARCHAR(50)ASBEGINDECLARE @.ReturnValueint, @.Cmdvarchar(500)SET @.ReturnValue = -1SET @.Cmd ='dtsrun /S ' + @.ServerName +' /N ' + @.PackageName +' /E'--' /U "LoginName" /P "password" 'EXECUTE @.ReturnValue = master..xp_cmdshell @.Cmd, NO_OUTPUT--RETURN @.ReturnValueSELECT @.ReturnValue [Result]END

Private Sub ExecutePackage(ByVal serverAs String,ByVal packageAs String)Dim ExecuteAdapterAs New PAY_DAL.RunDTSTableAdapters.sp_DTSRunTableAdapter() ExecuteAdapter.SetCommandTimeOut(0)Dim strResultAs String = ExecuteAdapter.Execute(server, package)Select Case strResultCase 0 lblOutput.Text ="The package executed successfully."Case 1 lblOutput.Text ="The package failed - contact support."Case 2 lblOutput.Text ="2"Case 3 lblOutput.Text ="The package was cancelled by the user."Case 4 lblOutput.Text ="The utility was unable to locate the requested package. The package could not be found."Case 5 lblOutput.Text ="The utility was unable to load the requested package. The package could not be loaded."Case 6 lblOutput.Text ="The utility encountered an internal error of syntactic or semantic errors in the command line."End Select lblOutput.Visible =True End Sub

Just pass the package and server name in. To use a username and password remove the /E (trusted connection) and add /U and /P.