I'm looking for some help in the best approach for the following solution. I need to do the following:

1. Create a DTS Package to run as a scheduled job.
2. The Package must do the following:
a. drop several tables on a local SQL Server database
b. create tables and copy their data from an Access database that
resides on a separate Windows NT/XP machine to a local SQL
Server database (effectively the tables that I just dropped).
3. The SQL Server machine and the Access machines are not
necessarily on a domain (they do not have access to domain accounts)

My understanding is that I can accomplish the above as a DTS package with connections to the remote access database, but these are dependent on the Access databases being available over a network share. This requires the SQL Server Agent running the job to be configured to run in a domain account that has access to the remote shared drive. Unfortunately I am not guaranteed that either machine will be on a domain and likewise that a common domain account will be available on either machines.

My main question: is there a better way to connect to the remote access database in DTS that is not dependent on network shares?

Any help would be appreciated,
Jeremy

In message <B31CE828-CB07-4DC1-8890-359DB2972C32 (AT) microsoft (DOT) com>, jkiffer
<jkiffer (AT) discussions (DOT) microsoft.com> writes
Access is a file based application, so you need to access the mdb file
to do anything with it, no way around this.

If you cannot reliably make a UNC share connection to the remote
machine, then you will need to find a way of copying the mdb file to
somewhere that you can access. FTP perhaps?

If you can make a UNC connection but the security is messy, then you can
use the NET USE commands directly to open the share, then let DTS do
it's thing, before closing it again. Wrap the NET USEs up in a batch
file.


--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Darren Green wrote:
Darren,

I am having a similar problem, in that my DTS package accesses DBF files
through a DSN connection to a UNC shared folder on my network. When I
run the DTS package while at the server console the package executes
flawlessly. When I try and run it using Visual Basic.NET code, it fails
when trying to access any of those DSN connections with UNC share
information in them. Am I doing something wrong? Should I be using
ActiveX scripts in my DTS to check for the UNC and make a connection to
the servers where the DBFs are before running those specific tasks? If
so how do I do that?

Please help me if you can.

Thanks
john

Test Edin
"John Wildes" <john_wildes (AT) ars (DOT) aon.com> wrote