Hello!

On SQL 2000 I created with Import Data Wizard package "dtsTest". I get
data from Access file on location "C:\Temp\dbTest.mdb". Wizard created
"Connection 1" and "Connection 2" icons if I select Design package. I
call that package from VB.NET 2002 that way:
---
Dim pck As DTS.Package2Class

pck = New DTS.Package2Class()
pck.LoadFromSQLServer(strDataSource, strUserName, strPassword, , , , ,
"dtsTest")
pck.Execute()
pck.UnInitialize()
---

That work Ok until I have mdb file on location when dts was created. So
I would like to put in code with Dynamic properties Task path where
that Access will reside at runtime. I search on web but I can't find
any step by step example what I have to do in that existing dtsTest and
what I have to do in VB code.

I did that way:
- add new icon "Dynamic properties task" in Design package then
- I click "Add" button and for dtsTest on "Connection 1" and then click
on "DataSource" row then I select "Set..." button
- as source I select Global variable and then select "Create Global
Variables"
- I defined name "strPath" as string and then choose the variable on
Add/Edit Assignement
In Dynamic Properties Task Properties I have now:
DataSource | Global Variable | strPath

In VB Code I add before Execute line that:
pck.GlobalVariables.Item("strPath").Value = "C:\Temp\dbTest.mdb"

but dts don't import data. If I wrote my path in global variable then
works but that mean that assigment in VB is wrong.
What I did wrong here or what is missing?

Billy

The Dynamic properties task will read from a source and set the properties
you require. What you can also do is seeing this is through code anyway you
could get a handle to the MDB connection and then set its DataSource
Property before executing.





--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Billy" <abil2y (AT) yahoo (DOT) com> wrote

Thank's for help Allan!

I finally found a solution without any "Dynamic Properties Task" and
other mumble jumble. So if anybody else will have the same problem here
is the complete code tu run dts from server where you can assign path
for arbitrary path for source data:
---
Dim pkg As DTS.Package2Class
Dim cn As DTS.Connection

pkg = New DTS.Package2Class()
pkg.LoadFromSQLServer(strDataSource, strUserName, strPassword, ,
, , , "dtsName")
cn = pkg.Connections.Item("cnAccess")
cn.DataSource = "X:\Path\AcFile.mdb"

pkg.Execute()
pkg.UnInitialize()
pkg = Nothing
---

Regards,
Billy