I am going nuts trying to get this to work. Maybe someone can help me. I am running sql server 2000 and am using a dts package. The package runs fine on sql server. When I access it using asp.net I get the following error:
------
The execution of the following DTS Package succeeded:
Package Name: MapsImport
Package Description: Import Excel to Maps table
Package ID: {C56FF415-CD35-461E-98E4-BB2430163413}
Package Version: {30415D05-B121-4C4B-991C-43496EA47090}
Package Execution Lineage: {EAF14EB3-F6C1-4D9B-A4B9-46E31AF4B608}
Executed On: NS23
Executed By: ASPNET
Execution Started: 6/7/2006 3:33:12 PM
Execution Completed: 6/7/2006 3:33:17 PM
Total Execution Time: 5.11 seconds
Package Steps execution information:
Step 'Copy Data from Sheet1$' to [dbname].[dbo].[Maps] Step' failed
Step Error Source: Microsoft JET Database Engine
Step Error Description:Failure creating file.
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:5003436
Step Execution Started: 6/7/2006 3:33:12 PM
Step Execution Completed: 6/7/2006 3:33:17 PM
Total Step Execution Time: 5.015 seconds
Progress count in Step: 0
----
I searched through several forums and found that this seems to be a permissions problem. I set the IIS process to Low, I also added the <identity impersonate="true" /> tag to my web.config file. After adding the tag I get a new error message:
----
No Steps have been defined for the transformation Package.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Runtime.InteropServices.COMException: No Steps have been defined for the transformation Package.
------
I am at a stand-still trying to get any further. Can anyone teel me what else I might be able to try to resolve this problem?
Here is my code for executing the package:
------
Sub Page_Load(Src As Object, E As EventArgs)
Dim cnnstring as String="Data Source=NS32;Initial Catalog=dbname;Pooling=False;Min Pool Size=100;Max Pool Size=200;User ID=userid;Password=password"
Dim cnn as SqlConnection
Dim cmd as SqlCommand
Dim rs as SqlDataReader
Dim sql as String="Truncate Table Maps"
'Empty Equipment Contract Pricing table
cnn=New SqlConnection(cnnstring)
cnn.Open()
cmd=New SqlCommand(sql, cnn)
sql="DELETE FROM Maps WHERE Name IS NULL"
cmd=New SqlCommand(sql, cnn)
rs=cmd.ExecuteReader()
'check to see if table is empty
If rs.HasRows Then
Response.Write("<p><b>Failed to empty table.</b></p>")
Else
Response.Write("<p><b>Table successfully emptied.</b><br><br>Importing Data...<br>Please wait...</p>")
End If
'declare variables for DTS
Dim objDTSPackage, objDTSStep, strResult, blnSuccess
Const DTSSQLStgFlag_Default = 0
Const DTSStepExecResult_Failure = 1
'Use stored procedure on sql server to import data
objDTSPackage = Server.CreateObject("DTS.Package")
blnSuccess = True
'Load package from sql server
objDTSPackage.LoadFromSQLServer ("NS32", "user", "pass", DTSSQLStgFlag_Default, "pass", "", "", "MapsImport")
'Explanation: LoadFromSQLServer ("ServerName", "Username", "Password", "Flags", "PackagePassword", "PackageGUID", "PackageVersionGUID", "Package Name", "PersistsHost")
objDTSPackage.Execute
'walk through steps and check for errors
For Each objDTSStep in objDTSPackage.Steps
If objDTSStep.ExecutionResult = DTSStepExecResult_Failure Then
strResult = strResult & "Package " & objDTSStep.Name & " failed.<br><br>"
blnSuccess = False
Else
strResult = strResult & "Package " & objDTSStep.Name & " succeeded.<br><br>"
End If
Next
'display success or failure message
If blnSuccess Then
Response.Write ("<p><b>Package Succeeded.</b></p>")
Else
Response.Write ("<p><b>Package Failed.</b></p>")
End If
Response.Write ("<p>"& strResult &"</p>")
rs.Close
cnn.Close
'Response.Redirect("list.aspx")
End Sub
---------
The code fails on the line where it says:
objDTSPackage.Execute
Thanks in advance!
-Mikeis the user name and password defined here...
objDTSPackage.LoadFromSQLServer ("NS32", "user", "pass", DTSSQLStgFlag_Default, "pass", "", "", "MapsImport")
..the owner of the dts package?|||Yes, the user and pass are defined properly. I changed them throughout the script for the post. I also added the package guid and the package version guid and I still recieve the same error message.
My scripts are running off a different server than where sql server is located. Could this have something to do with it? I am pretty sure this is some sort of permissions problem, but I dont know what it is. Any ideas?
Thanks.
graemeo wrote: