Is there any way to pass a file name to a DTS Package? I'm using a
DTS.Package2 object in VB6 (using LoadFromSQLServer etc.). The package
imports an Excel file into a SQL2000 table. When I created the DTS import I
specified a file name, but I'd like to be able to override it in code.

Thanks-Tom

What about


oPkg.Connections("My Excel Connection").DataSource = "Path and file
name"




"Tom Gaughan" <tgaughan (AT) numail (DOT) org> wrote

You got me on the right trail. I'm getting this error though:

Step "Copy Data from Sheet1$ to
[Inventory].[dbo].[ManufacturerShortagesTemp] Step" Failed -
Error: -2147467259
Source: Microsoft OLE DB Provider for SQL Server
Description: [DBNETLIB][ConnectionOpen (ParseConnectParams()).]Invalid
connection.

Here's the code I'm using:

Set dtsConnect = dtsPackage.Connections.New("SQLOLEDB")
With dtsConnect
.ID = 1
.DataSource = "Connection 1"
.UseTrustedConnection = True
End With

Call dtsPackage.Connections.Add(dtsConnect)

dtsPackage.Connections(1).DataSource = FILE_PATH & "\Manufacturer
Shorts\VSPR_update.xls"

' Load Package
dtsPackage.LoadFromSQLServer "EUHRXINV2", "mrxgtm", ,
DTSSQLStgFlag_Default, , , , "Import Manufacturer Shorts"
dtsPackage.Execute

I figure my problem is either in:
Set dtsConnect = dtsPackage.Connections.New("SQLOLEDB")
or
.DataSource = "Connection 1"
"Connection 1" is what the DTS package is using (a wild stab on my part)

Thanks for your help...Tom

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

It looks as though you are passing a Provider of SQLOLEDB and setting
the properties for an XL connection. This would not work.

Easiest way to learn?

Put a package together that has an Excel connection.

Save As | VB

Have a look at what DTS generates for you.

Allan

"Tom Gaughan" <tgaughan (AT) no2spam (DOT) numail.org> wrote