Execute DTS package from ASP failed

I am trying to run DTS Package from ASP 3.0 with the following codes:

Set objDTSPackage = server.CreateObject("DTS.Package")
objDTSPackage.LoadFromSQLServer "serverName", "", "", 256,,,,"pkgName"
objDTSPackage.FailOnError = true
objDTSPackage.Execute

It failed with this message, "Package failed because Step 'DTSStep_DTSDataPumpTask_1' failed".

I am using NT Authentication in both IIS and SQLServer, and both Web and SQLServer are in the same machine.

Can someone help? Thank you.The DTS package must be executed in the same security context as the web page. In ASP web pages are executed under the IIS account that begins with IUSR_. You can find this account name by right clicking My Computer and clicking Manage. Expand Local Users and Groups and look in the users folder.

Then set up a SQL server account for this login like 'MyComputerName\IUSR_...'.

Be careful to only give this account public access to the database the DTS package is accessing and I even go to the trouble of restricting permission to the specific tables.|||ps In the SQL account change the default database to the affected database (not master) and do not give it a server rol for security reason|||Thanks for the prompt reply.

I am using NT Authentication in IIS for all my web applications, does this matter?

I've also tried creating SQL Server login ID with 'myMachineName\IUSR_...', but my company's system wouldn't allow me do this - for security reason.

Any alternatives?|||No it should'nt matter.

are you not allowed to create SQL logins?

or is it giving an error?

try 'yourdomain\youriusraccount'. It might work.

The only other alternative using DTS I know creates a big ugly security hole for hackers to climb in. It invloves firing the DTS package from a shell command and going into the SQL agent properties and setting up a proxy account under the job system tab. The ramifications are ugly.|||I do have the rights to create login ID, but this is something to do with different domain in the system, and I guess not all users in certain domain can be created.

Let me clarify a bit, when you say, create login ID for 'MyComputerName\IUSR_...', you do mean to say the computer that I am personally using now, correct? Not the computer name with IIS installed, right?

I have to find a way to create login ID for this 'IUSR_...', so if this is created, I can use the ASP codes I showed to execute DTS?

Thanks.|||MyComputerName is the name of the computer where your IIS install is.

If you do this your code will work. I've incorporated DTS into 3 ASP projects in the last 4 years.|||Thank you.

I'm still tryihg find "IUSR_" for my web server in SQL Server login creation. Is there any possible reason you can think of that I can't find the "IUSR_" on SQL Server, "New Login"?|||Just want to update and close this:

I've found the IUSR account for the new login to SQL Server. The domain of this IUSR account will be the name of the server (since IUSR is a local user). I've found it there. Thanks.|||did it work?|||Yes and No, Sorry for this terrible answer. Thank you for keep interests in this.

I have used client-side VBscript to make it work - instead of server-side ASP code, I guess this bypasses the IIS issues (?). I am a bit concerned of the security, other than all the stuffs I am still learning. I think I will come back with ASP server-side code to make it work.

HOWEVER, I am having another problem. After I executed successfully the DTS Package, my query and subsequent SQL operations to the destination table don't work anymore. It gives no error messages, but it is not doing anything. A simply query to check the row count, comes back with 0 rows, even though the DTS has transform records to the table.

Help me, if you have any idea? I am not sure where the problem is coming from?!|||how do you know the DTS package executed successfully?

does it work when you fire it from the EM (i.e. rows in the table)?

what is the code you are using to execute the DTS?

i am betting this is another permissions issue and has to do with this client side script you have.

here is some vbscript that should tell you where the package is failing. Depending on how you are doing the package will not always throw errors.

http://www.sqldts.com/default.aspx?t=6&s=104&i=207&p=1&a=7

I converted this script to VB 6 that I wrapped in a COM object which is BTW how I accomplish this. See below.

Option Explicit

Private m_sError As String

Public Function Execute(ByVal sServer As String, ByVal sPackageName As String) As Boolean
On Error GoTo Err_Handler
Dim oPKG As DTS.Package, oStep As DTS.Step
Set oPKG = New DTS.Package

Dim lErr As Long, sSource As String, sDesc As String

Execute = True

' Load Package
oPKG.LoadFromSQLServer sServer, , , _
DTSSQLStgFlag_UseTrustedConnection, "!J1LLYB3@.N!", , , sPackageName

' Set Exec on Main Thread
For Each oStep In oPKG.Steps
oStep.ExecuteInMainThread = True
Next

' Execute
oPKG.Execute

' Get Status and Error Message
For Each oStep In oPKG.Steps
If oStep.ExecutionResult = DTSStepExecResult_Failure Then
oStep.GetExecutionErrorInfo lErr, sSource, sDesc
m_sError = m_sError & "Step """ & oStep.Name & _
""" Failed" & vbCrLf & _
"Error: " & lErr & vbCrLf & _
"Source: " & sSource & vbCrLf & _
"Description: " & sDesc & vbCrLf & vbCrLf
Execute = False
Else
m_sError = m_sError & "Step """ & oStep.Name & _
""" Succeeded" & vbCrLf & vbCrLf
End If
Next

oPKG.UnInitialize

Clean_Up:
Set oStep = Nothing
Set oPKG = Nothing

Exit Function

Err_Handler:
Execute = False
m_sError = "Error in Object Execution" & vbCrLf & _
"Number: " & Err.Number & vbCrLf & _
"Source: " & Err.Source & vbCrLf & _
"Description: " & Err.Description
GoTo Clean_Up
End Function

Public Function GetErrorDetails() As String
GetErrorDetails = m_sError
End Function|||Before DTS starts, the destination table is cleared, then after the DTS, I went into EM to check, and they are rows in the tables. I assume that means DTS is working.

And this is my client-side VB code:

Dim objDTSPackage,
On Error Resume Next
Set objDTSPackage = CreateObject("DTS.Package")
objDTSPackage.LoadFromSQLServer "server", "", "", 256,,,,"pkgName"
objDTSPackage.FailOnError = true
objDTSPackage.Execute
objDTSPackage.UnInitialize()
Set objDTSPackage = Nothing

I will also study your codes. Thank for your help.|||Not sure if anyone would still read this long thread, but...

I've made this to work by putting codes into a VB DLL.

However, if I execute a simple SELECT statement against the destination table after the DTS execution is completed. It gives no error, but with no result either.

But if I reload the ASP page immediately, in other words, DTS is executed the second time, the SELECT statement produces the results from the FIRST DTS execution, instead of coming from the 2nd DTS execution!?

It seems to me the first DTS exectuion doesn't exist - as far as the SELECT statement is concerned.

Anyone has idea? Please help.|||rweide,
I am still here. watching this forum keeps me from working on my current ptoject which requires no effort or creativity and is boring me to death. So this is how I pass my days.

Might need to see some code here. Are you executing the DTS package and the select on the same post to the server?|||I'm so happy that you're bored!!! :)

I've decided just copy your codes into VB and created the DLL. But at the point, I'm only using client-side VBScript to call the DLL, which sits locally on my machine - I will move it to server whenl all these problems is resolved.

The DTS package is very simple - move the data from a flat text file to a holding/destination table, and it uses Windows authentication. And 2 SQL stored procedures are executed to check row count (to see if DTS works at all) and to update the main DB table using data from the temporary holding table after DTS is run.

As I've mentioend, the simply query give no result (row count = 0) and no error. But by checking thru EM, I know the DTS has populated the destination table. If I reload the ASP page (running DTS again), the query gives the result from the first DTS execution!!

Here is the abbreviation of the codes:

<Body>
<%
On Error Resume Next
Set Session("adoCnn") = server.CreateObject ("ADODB.Connection")
Session("adoCnn").ConnectionString = "connection string here"
Session("adoCnn").Open
%>

<Script Language=VBScript>
Set mc = CreateObject("mc.mcDTS")
mc.Execute
document.write mc.GetErrorDetails
set mc = nothing
</script>
<%
Response.Write "<BR><BR> Row Count in Holding Table: " & CountRows()
Call UpdateManual()

Response.Write "<BR><BR>Count Close Table: " & CountClose()
If Err.number = 0 Then
response.write "<BR><BR>Updated from Holding table to Main Table completed..."
End If
%>
</Body>
</HTML>
<%
Function CountRows()
Dim cmd, rs, Parm1

On Error Resume Next
Set cmd = Server.CreateObject("ADODB.Command")
Set rs = Server.CreateObject("ADODB.Recordset")

rs.CursorType = adOpenStatic
cmd.CommandText = "spCountHoldingRows"
cmd.CommandType = adCmdStoredProc
' cmd.CommandText = "SELECT COUNT(*) row FROM tbl_mc_Monthly_Close_Holding"
' cmd.CommandType = adCmdtext
Set Parm1 = cmd.CreateParameter("return", adInteger, adParamReturnValue)
cmd.parameters.append parm1
cmd.ActiveConnection = Session("adoCnn").ConnectionString
set rs = cmd.Execute
If err.number <> 0 Then
Response.Write "<BR>Count Rows - " & err.number & " - " & err.Description
End If
CountRows = cmd(0)
Set cmd = Nothing
set rs = nothing
End Function

Function CountClose()
Dim cmd, rs, Parm1

On Error Resume Next
Set cmd = Server.CreateObject("ADODB.Command")
Set rs = Server.CreateObject("ADODB.Recordset")

rs.CursorType = adOpenStatic
cmd.CommandText = "spCountCloseRows"
cmd.CommandType = adCmdStoredProc
Set Parm1 = cmd.CreateParameter("return", adInteger, adParamReturnValue)
cmd.parameters.append parm1
cmd.ActiveConnection = Session("adoCnn").ConnectionString
set rs = cmd.Execute
If err.number <> 0 Then
Response.Write "<BR>Count Close Rows - " & err.number & " - " & err.Description
End If

CountClose = cmd(0)
Set cmd = Nothing
set rs = nothing
End Function

Sub UpdateManual()
Dim cmd

On Error Resume Next
Set cmd = Server.CreateObject("ADODB.Command")
cmd.CommandText = "spUpdateMain"
cmd.CommandType = adCmdStoredProc
cmd.CommandTimeOut = 1800
cmd.ActiveConnection = Session("adoCnn").ConnectionString
cmd.Execute
If err.number <> 0 Then
Response.Write "<BR>UpdateManual - " & err.number & " - " & err.Description
End If
Set cmd = Nothing

End Sub

%>

And I don't really think the combination of client-side and server-side codes will mess up the operation. I remember at one time, I changed all codes to be executed on the client-side, and it still doesn't work.

I wonder if this has anything to do with memory?|||I am not sure where to start.

Where does your database reside? First, get you database, web server, and DTS package all on the same server. (professionally I reccomend having the web and database server on different machines with a firewall between the 2).

Change this back to server side script:

<Script Language=VBScript>
Set mc = CreateObject("mc.mcDTS")
mc.Execute
document.write mc.GetErrorDetails
set mc = nothing
</script>

Next hide your connection string in an include file in another directory that your IUSR account does not have access to.

Finally and this meant to be constructive criticism, you write too much code. Keep it simple and concise. do not use functions unless you have reptitive code. Look at this below. It works. I just did it.

<HTML>
<Head>
<Title>My Module</Title>
</Head>
<BODY>
<H2>Sourcing Module</H2>
<H3>Upload Bibliography Data to Sterling</H3>
<%
Dim mySmartUpload
Set mySmartUpload = Server.CreateObject("aspSmartUpload.SmartUpload")
Server.ScriptTimeout = 10000
mySmartUpload.Upload
mySmartUpload.Save("/downloads")
Set mySmartUpload = Nothing
Dim oExecDTS
Dim sServer, sPackageName
Dim bResult
sServer = "MySQLServer"
sPackageName = "Mydts"
Set oExecDTS = CreateObject("SQLDTS_ExecDTS8.ExecutePackage")
bResult = oExecDTS.Execute(sServer, sPackageName)
If bResult Then
Response.Write "<p>Package " & sPackageName & " succeeded</p>"
Else
Response.Write "<p>Package " & sPackageName & " failed</p>"
Response.Write "<p>" & Replace(oExecDTS.GetErrorDetails, vbCrLf, "<br/>") & "</p>"
End If
Set oExecDTS = Nothing
Dim con,rs,constring,sql
Set rs = Server.CreateObject("ADODB.Recordset")
Set con = Server.CreateObject("ADODB.Connection")
sql = "SELECT count(*) as thecount FROM DP_PRELIMBIB"%>
<!-- #include file="includes\sourcingDBconnection.inc" -->
<%con.Open constring
rs.Open sql, con
Response.Write rs("thecount")
rs.Close
con.Close
Set rs = Nothing
Set con = Nothing
%>
</BODY>
</HTML>


aspSmartUpload is a third party component I use to upload and download files for personal applications.