DTS Package fails from CMD prompt

#1

Hello

I posted a couple of days ago about this issue that I am having. See
post titled "DTS Package error out when called from VB.NET" . I am
still having the same problem and have invested many hours into
troubleshooting and recreating this package only to be at square one.

I have referenced two Microsoft support documents 269074, and 242391. I
believe I have narrowed down the problem to the fact that in my DTS
package I have two connections an ODBC FoxPro connection to a UNC path,
and an OLEDB FoxPro Connection to a UNC path. I have included
authentication information into the properties of each of these
connections.

Does anyone have any knowledge of creating or executing DTS packages
from DTSRUN or VB.NET when the DTS package contains a connection to a
datasource that does not reside on the physical server where the DTS
package is being executed?

I feel I have given most of the relevant information if there are any
questions please post back I will try to answer them. I am really at my
wits end here, I KNOW someone has done this before.

I appreciate any help.

Thanks
john

#2

I guess I figured it would be obvious but just in case it isn't.

The DTS Package executes fine from the server when accessed at the
desktop or through PC Anywhere.

The DTS Package partially completes when I run it through DTSRUN from my
local workstation. In the package have a series of tasks that clean out
the existing data in the SQL tables before new data is queried and put
into those tables. The tasks that are performed against the SQL tables
complete, but the rest of the tasks that bring in the data from the
FOXPRO UNC connections fails. This happens also when I use VB.NET to
run the DTS package from my workstation.

The DTS package was created at the Server on which it runs. The owner
of the package is the DOMAIN\Administrator account. The Authentication
given in each of the FoxPro connections is the DOMAIN\Administrator
account and password. Both the SQL server and the FoxPro data server
are in the same domain, and as such should have access to each other
using the DOMAIN\Administrator account.

I'll include the error messages and code snippets below.

Thanks again for any help.

Quote:

ERRORS and CODE

DTSRUN OUTPUT
C:\Documents and Settings\jwildes>dtsrun /S USGLE98APPFX100 /U stmt /P

generator /N "REFRESH TAM DAT
A"
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_2
DTSRun OnError: DTSStep_DTSExecuteSQLTask_2, Error = -2147467259 (80004005)
Error string: Cannot open database requested in login 'TAM'. Login
fails.
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0

Error Detail Records:

Error: -2147467259 (80004005); Provider Error: 4060 (FDC)
Error string: Cannot open database requested in login 'TAM'. Login
fails.
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0

DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2
DTSRun: Package execution complete.

Quote:

CODE
Imports DTS

Module DataFunctions

Public Function refreshStatementData(ByVal progressbar As
System.Windows.Forms.ProgressBar, ByVal label As System.Windows.Forms.Label)
Dim pkg As New DTS.Package
Dim steps As String
Dim bStatus As Boolean
Dim errSource As String
Dim errDescription As String
Dim i As Integer
Dim oStep As [Step]
Dim sMessage As String

'load DTS package from server
pkg.LoadFromSQLServer("USGLE98APPFX100", "stmt", "generator",
, , , , "Refresh TAM Data", )
pkg.Execute()

For Each oStep In pkg.Steps
sMessage = oStep.Description
If oStep.ExecutionResult =
DTSStepExecResult.DTSStepExecResult_Failure Then
sMessage = sMessage & " failed."
bStatus = False
label.Text = sMessage
oStep.GetExecutionErrorInfo(i)
MsgBox("Refresh Statement Data step " & sMessage & "
with error code " & i, MsgBoxStyle.Information, "Refresh Statement

Generator Data")

Quote:

Else
sMessage = sMessage & " succeeded."
label.Text = sMessage
progressbar.PerformStep()
bStatus = True
MsgBox("Refresh Statment Data step " & sMessage,
MsgBoxStyle.Information, "Refresh Statement Generator Data")
End If
Next

End Function 'refreshStatementData

End Module

John Wildes wrote:

Quote:

Hello

I posted a couple of days ago about this issue that I am having. See
post titled "DTS Package error out when called from VB.NET" . I am
still having the same problem and have invested many hours into
troubleshooting and recreating this package only to be at square one.

I have referenced two Microsoft support documents 269074, and 242391. I
believe I have narrowed down the problem to the fact that in my DTS
package I have two connections an ODBC FoxPro connection to a UNC path,
and an OLEDB FoxPro Connection to a UNC path. I have included
authentication information into the properties of each of these
connections.

Does anyone have any knowledge of creating or executing DTS packages
from DTSRUN or VB.NET when the DTS package contains a connection to a
datasource that does not reside on the physical server where the DTS
package is being executed?

I feel I have given most of the relevant information if there are any
questions please post back I will try to answer them. I am really at my
wits end here, I KNOW someone has done this before.

I appreciate any help.

Thanks
john

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

DTS Package fails from CMD prompt

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

DTS Package fails from CMD prompt - 06-17-2004 , 03:06 PM

Re: DTS Package fails from CMD prompt - 06-17-2004 , 03:14 PM