Hi , i need an help .
I need to import data from database access to sql server database , and this
is not a problem . The really problem is that the database access is
variable .
My idea is to insert database location on an sql server table and use this
to change dynamically datasource on my dts .
I try to made a dts but i cannot succeed in .
Can anyone help me and gine me some indication .

I have an sql server 2000 sp3a

Thanks a lot

If the structure of the access table is always the same then you can do
this, by changing the connection -

How can I change the filename for an Access Connection?
(http://www.sqldts.com/default.aspx?201)

You could also use the Dynamic Properties Task to do this instead, just use
query assignment to retrieve the value and set the DataSource property of
the MDB connection.

If the table name changes as well, but the structure is the same, then you
could use similar method to above, but change the SourceObjectName property
of the DataPump task as well.

--
Darren Green
http://www.sqldts.com


"Import data from multiple acces db" <Import data from multiple acces
db (AT) discussions (DOT) microsoft.com> wrote

Thanks a lot Darren , i found the site that u show to me , but i have one
problem , the first round i change the datasource of the mdb connection and
the import goes well , the second round i try to change the datasource with
an another mdb file name but the dts import data from the first mdb file .
I insert an msgbox to verify that the secon round find the correct filename
and it does well but data come from the first file .
Probably dts keep the first connection open . what can i do to solve this
problem .

Thaks another time .

"Darren Green" wrote:

There is an option "Close connection on completion" that you need to set so
it close stegh onnection to enable it to be (re)opened withe teh new
filename you have set.

Right-click the DataPump task, Workflow Properties, Options.


--
Darren Green
http://www.sqldts.com
"Import data from multiple acces db"
<Importdatafrommultipleaccesdb (AT) discussions (DOT) microsoft.com> wrote

Now it goes well .
Thanks a lot !

"Darren Green" wrote: