'DTSStep_DTSDataPumpTask_1' failed

[rwei]

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 steer me to the right direction? Thank you.

 

[pkailas]

I don't see a username or password to execute it.. try this.

set objDTSPackage= Server.CreateObject("DTS.Package")
objDTSPackage.LoadFromSQLServer "serverName","USERID","password",DTSSQLStgFlag_Default,"","","","pkgName"
objDTSPackage.Execute

 

[rwei]

No, I had tried that with username and password, but same result.

I am focusing on the fact that "this is running within the security context of IIS", and I think it has something to do with setting up "IUSR_servername" login for SQL Server. But I don't know the detail of this yet.

 

[pkailas]

ahh yes. You might try changing the anonymous user to a valid user on the server or a domain user if you actually have a domain.

 

[EdwinGene]

Turn on Logging in the DTS package. To do this, open the package in design mode, then click on Properties and Logging. After the error occurs, check the log by right-clicking on the package name and then "Package Logs..." from the Local Packages list. Double-click on the errored step. This will give you a more detailed error message.

 

[rwei]

I am using client-side VBScript to get it work, seemingly bypassing the IIS issue related to IUSR account.

But I am experiencing another issue related to DTS execution: after the DTS is done, the destination table involved seems to be "locked" still - even though, I have issued "Uninitialize" and have set the object to "Nothing".

I simply cannot run a simple "Select" statement on the destination table. I mean, it runs, but no results come back (and no error either), even though, I see rows and rows of record using EM.