This is what I am trying to do: I need to import data into a SQL 2000 table
from a txt file when a user clicks a button in a Visual Studio .NET VB form.
Both the file name and the path will change. There is a field on the VB form
that contains the info needed to change the path and file name. For example:

Form Field is named: SetBatchFile and contains a value such as: 000001

The location of the txt file is for example: C:\batchfiles\000001\000001.txt

I need to create a DTS package that uses a variable that is set by the
SetBatchFile field on the VB form in Visual Studio .NET.

I am new to SQL and know that this is not the correct syntax but for
explanation purposes, the DTS package should do something like this.
Something like:

Dim Path
Dim FileName

Path = C:\batchfiles\SetBatchFile
FileName = SetBatchFile

Import(Path+FileName)

How can I do this?

Number of ways but here are two

Get the path in VB

1.
Call the package through the object model.

Change the Connection DataSource on the text file connection

Execute the package

2.

Call DTSRun on the cmdline from VB

Using the /A switch you pass in the path. The /A switch maps your value
to a Global Variable.

In your package you would do something like this at the start in an
Active Script task

DTSGlobalVariables.Parent.Connections("Name of connection").DataSource =
DTSGlobalVariables("Variable Name").Value


HTH

Allan




"HollyylloH" <HollyylloH (AT) discussions (DOT) microsoft.com> wrote

Allan,
Thank you for your help. I hope you won't mind helping me further. I am new
at this and could use more detail if possable. Currently I am connecting to
and executing the DTS Package from VB with this code:

Dim pkg As DTS.Package
pkg = New DTS.Package
pkg.LoadFromSQLServer("SERVER", "", "", _
DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrus tedConnection, "", "", "",
"PackageName")
pkg.Execute()

In your first example you suggest that I call the package in a different way
which allows me change the path to the txt file. Can you give me an example
of this code?

Thanks
-------------------

"Allan Mitchell" wrote:

Ok So before you do this

pkg.Execute()

You need to do something like this

Pkg.Connections("Name of Connection").DataSource = "Whereever"



Allan




"HollyylloH" <HollyylloH (AT) discussions (DOT) microsoft.com> wrote

Allen,
I guess I am having some syntaxs problems here. I relize that this is
probably easy stuff but I am stuck; hope you can help.

Dim oPackage As DTS.Package
Dim oTask As DTS.Task
Dim BatchFileName
Dim Path As String

' Define the files to be imported
BatchFileName = Me.BatchNumber
Path = "C:\BatchFiles\"

' Create a new package
oPackage = New DTS.Package

' Load the DataLoader package from SQL Server
oPackage.LoadFromSQLServer("SERVER", "", "", _
DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrus tedConnection,
"", "", "", "PackageName")

' Get a reference to the Bulk Insert task in the Package and change
the source data file
oTask = oPackage.Tasks
oTask.Name = "NameOfBulkInsertTask"
oPackage.Connections("NameOfPackageConnection").Da taSource = Path +
BatchFileName.text

' Import the file
oPackage.Execute()

' Cleanup
oPackage.Uninitialize()
oTask = Nothing
oPackage = Nothing

---
The connection statement says that "the interface DTS.connections cannot be
index because it has no default property." I have tried variations of this
without success.

What am I doing wrong here?



"Allan Mitchell" wrote:

It looks to me as though you are trying to set some properties of the
BULK INSERT task itself. It also appears that the "connection" you want
to set is the text file from which you would BULK INSERT. This is not a
connection per se rather a property of the task itself. Nowhere in your
code do I see the usage of the Bulk Insert task properties

So you are in .Net

Right

Can you use something like this


object pVarPersistsStorageOfHost = null;

DTS.Package2Class p = new DTSPackage2Class();

DTS.BulkInsertTask tsk;

p.LoadFromStorageFile("c:\\MyPackage.dts",null,nul l,null,"Package",ref
pVarPersistsStorageOfHost);

tsk =
(DTS.BulkInsertTask)p.Tasks.Item("DTSTask_DTSBulkI nsertTask_1").CustomTask;

tsk.DataFile = "Enter the path to the file here.txt";

p.Execute();

p.Uninitialize();



Allan





"HollyylloH" <HollyylloH (AT) discussions (DOT) microsoft.com> wrote

Allen,

Thank you I was able to get this to work as follows:

Dim pkg As DTS.Package
Dim oTask As DTS.BulkInsertTask
Dim BatchFileName
Dim Path As String
Dim myDataFile
Dim extension
Dim oPackage2 As DTS.Package

' Define the files to be imported
BatchFileName = Me.SetBatchNumber.Text
Path = "C:\BatchFiles\"
extension = ".txt"
myDataFile = Path + BatchFileName + extension

' Create a new package
pkg = New DTS.Package

' Load the DataLoader package from SQL Server
pkg.LoadFromSQLServer("SERVER", "", "", _
DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrus tedConnection,
"", "", "", "FirstPackage")

' Get a reference to the Bulk Insert task and change the source data
file
oTask = pkg.Tasks.Item("DTSTask_DTSBulkInsertTask_1").Cust omTask
oTask.DataFile = myDataFile

' Import the file
pkg.Execute()

' Create a new package
oPackage2 = New DTS.Package

' Load the DataLoader package from SQL Server
oPackage2.LoadFromSQLServer("SERVER", "", "", _
DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrus tedConnection,
"", "", "", "SecondPackage")

oPackage2.Execute()

' Cleanup
pkg.UnInitialize()
oPackage2.UnInitialize()
oTask = Nothing
pkg = Nothing
oPackage2 = Nothing

Thanks again!

"Allan Mitchell" wrote: