Running DTS from ASP.NET vb code

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Running DTS from ASP.NET vb code

Author

Topic

pug2694328
Posting Yak Master

166 Posts

Posted - 2007-12-06 : 15:03:34

Hello there,

We have a DTS package that runs just fine from SQL server, or from a procedure (exec master..xp_cmdshell 'dtsrun /S SERVERNAME /N dts_name /E ').

I'm trying to get the DTS package to execute via ASP.NET vb code per this documentation:
http://support.microsoft.com/?kbid=315661

The LoadFromSQLServer step is working fine, no errors and I can see it being defined, however, the dts_package.Execute() command causes a significant delay and errors are populated (note most of my code is error handling logic). Each DTS step is resulting in the same error:

Error: -2147467259
Source: Microsoft OLE DB Provider for SQL Server
Description: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

The server is 2000.

Here's my code:

Dim dts_package As DTS.Package, oStep As DTS.Step
dts_package = New DTS.Package
dts_package.LoadFromSQLServer("SERVERNAME", , , DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection, , , , "DTS_PACKAGENAME")
For Each oStep In dts_package.Steps
oStep.ExecuteInMainThread = True
Next

' Get Status and Error Message
Dim sPackageName As String, sMessage As String
Dim lErr As Long, sSource As String, sDesc As String
dts_package.Execute()
For Each oStep In dts_package.Steps
' If oStep.ExecutionResult = DTSStepExecResult_Failure Then
If oStep.ExecutionResult = DTS.DTSStepExecResult.DTSStepExecResult_Failure Then
oStep.GetExecutionErrorInfo(lErr, sSource, sDesc)
sMessage = sMessage & "Step """ & oStep.Name & _
""" Failed" & vbCrLf & _
vbTab & "Error: " & lErr & vbCrLf & _
vbTab & "Source: " & sSource & vbCrLf & _
vbTab & "Description: " & sDesc & vbCrLf & vbCrLf
Else
sMessage = sMessage & "Step """ & oStep.Name & _
""" Succeeded" & vbCrLf & vbCrLf
End If
Next
dts_package.UnInitialize()
dts_package = Nothing

Thanks for any help you can provide!

pug2694328
Posting Yak Master

166 Posts

Posted - 2007-12-06 : 15:16:36

Update:
I've also tried using our 'sa' account with the exact same result:
dts_package.LoadFromSQLServer("ICOMMTMA1", "sa", "password", DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection, , , , "dts_package_name")

pug2694328
Posting Yak Master

166 Posts

Posted - 2007-12-07 : 11:48:38

I got it working,

Little weirdnesses in the DTS package definition seem to have been the cause.
1. Changed target server from 'local' to explicitly be SERVERNAME
2. Changed source file foldername from using drive letters to the fully qualified network address ie (\\SERVERNAME\foldername\).

I think both of these fixes are required because the .NET application is running on the IIS web server, not the sql server. So you have to use the web server's perspective when defining the DTS package. Does that make sense? Let me know if I'm missing the boat on causation.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-12-07 : 13:56:59

Yes, that is the way it works. You have to take into account what machine is physically processing the DTS package.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources