Changing Path to Access file in DTS pkg

Changing Path to Access file in DTS pkg

Post by Rebecca Freema » Sun, 03 Mar 2002 04:14:21



I have a series of packages running on my SQL server.  The
first two packages are responsible for bringing over data
from Access databases on remote machines.  For a temporary
period of time, I need the packages to pull data from the
Access databases at a different path.  However, when I use
the "properties" function of the Access package icon...and
alter the path to the new location, the package no longer
imports data from the Access file.  I know the permissions
are set and the path is correct...using UNC filenames,
with no spaces in any directories.

Also, after I change the path I'm asked if I want to reset
the transformations that have reconfigured on that
connection.  If I answer "yes" or "no", I still can't
import the data from the new location.

What to do?

 
 
 

Changing Path to Access file in DTS pkg

Post by Paritosh Patha » Mon, 04 Mar 2002 15:54:08


Rebecca

There are a few solution to your problem.  Look for dynamic properties task
and UDL in online help.  You can use the dynamic properties task to change
datasources with no user intervention.  Search for dynamic properties at
www.sqldts.com.

-Paritosh


Quote:> I have a series of packages running on my SQL server.  The
> first two packages are responsible for bringing over data
> from Access databases on remote machines.  For a temporary
> period of time, I need the packages to pull data from the
> Access databases at a different path.  However, when I use
> the "properties" function of the Access package icon...and
> alter the path to the new location, the package no longer
> imports data from the Access file.  I know the permissions
> are set and the path is correct...using UNC filenames,
> with no spaces in any directories.

> Also, after I change the path I'm asked if I want to reset
> the transformations that have reconfigured on that
> connection.  If I answer "yes" or "no", I still can't
> import the data from the new location.

> What to do?


 
 
 

1. path to Access file is lost during scheduled DTS import

I have struggled with this one for a while.

I have a DTS task that imports an Access file into a table in SQL Server.
The Access file has security setup since it is the backend for a HR
application. (it uses a workgroup file)

I defined my Access file connection in DTS by setting the path the mdb file,
supplying user and password and under advanced properties I add the
workgroup file to JetOLEdb:System Database. Once set it works fine.

This is the scenario:

Access file is copied from another server nightly to the server running DTS
DTS task is scheduled and runs each night opening the file and importing
some of its data into SQL Server

Problem:
as soon as a new copy of the Access file is put on the SQL Server, the DTS
task fails, saying the file mdb file is already in use or the user set in
the connection properties is not valid. The only way I can get it back to
work is to open the connection properties, re-set the path (it does show the
right path, but when you 'Browse' to it, it ends up showing the My Documents
folder. Then I have to go into advanced properties, remove the workgroup
file, click OK, go back into advanced properties and add the workgroup file,
click OK and then it works again.

It is not a network/share permission etc. issue as outlined in MS Knowledge
Base Article: How to Run a DTS Package as a Scheduled Job

I think it is either related to the fact that the file is copied each night
(new update date?) or something with the workgroup file.

I added an ActiveX script to set the path before running the import, but it
doesn't matter.
Anyone seen this before? I am clueless and can't run this job
automatically...

AvV

2. Oracle Stored Procedure

3. Setting Connection property for a Text File (FileName/path) from a Parent DTS to Sub DTS

4. Java app left sessions in Oracle

5. DTS BUG with text source file when pkg imported from VB

6. PostgreSQl or BerkeleyDB ??

7. Changing path to /tmp/.s.PGSQL.5432 file and the .lock file

8. Need help to Update an ACCESS 2000 Database using ADO and VB

9. DTS pkg access issue

10. Getting Long File Path from short Path / File Name

11. Change path in DTS..?

12. single DTS for changing text file(column number changes)

13. Change the default data and log file path