I created a dts package to import data from an access database. I will need
to do this nightly. I saved the packge, but when I try to run this with the
SQL Server Agent, It fails. I think one reason is because the Access
database is not on my workstation, but on a shared drive.

I also tried writing this query to execute the same thing:
USE Combination_Table

GO
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
returned message "'W:\MASTER DOCUMENTS\BizChair-2.mdb' is not a valid path.
Make sure that the path name is spelled correctly and that you are connected
to the server on which the file resides.".
Msg 7303, Level 16, State 1, Line 4
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

This however does run it I just execute the package alone, but I have other
queries that have to be run at the either before or after this and it needs
to be done at a certain time at night.

Any help on this matter?
Thank you
Dee

SELECT * INTO [GG Products]

FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'W:\MASTER DOCUMENTS\BizChair-2.mdb';'admin';'', [GG Products])

go

I received the following with the above application:

Dee,

For DTS packages, they run where ever you launch them. If you start a DTS
package running, it runs on your workstation, with your drive mappings, and
your domain privileges. If you schedule the job to run on the SQL Server,
it runs on the server, with the server's drive mappings, and the server's
domain privileges.

The DTS package will (on 2005) run in one of 3 security contexts from the
server:
If owned by a sysadmin, runs as the SQL Server account.
If owned by a sub-sysadmin, runs as the SQL Agent proxy account
OR, if specific SQL Agent Proxies are defined (see credential, proxies,
and principals), it will run as the account that the DTS step is directed to
use.

See the section on Proxy Accounts in
http://technet.microsoft.com/en-us/m.../cc160788.aspx then read the
necessary BOL topics.

So, decide what domain rights you need, what security context you want to
run in, and then get the proper rights granted.

RLF

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

Dee,

For DTS packages, they run where ever you launch them. If you start a DTS
package running, it runs on your workstation, with your drive mappings, and
your domain privileges. If you schedule the job to run on the SQL Server,
it runs on the server, with the server's drive mappings, and the server's
domain privileges.

The DTS package will (on 2005) run in one of 3 security contexts from the
server:
If owned by a sysadmin, runs as the SQL Server account.
If owned by a sub-sysadmin, runs as the SQL Agent proxy account
OR, if specific SQL Agent Proxies are defined (see credential, proxies,
and principals), it will run as the account that the DTS step is directed to
use.

See the section on Proxy Accounts in
http://technet.microsoft.com/en-us/m.../cc160788.aspx then read the
necessary BOL topics.

So, decide what domain rights you need, what security context you want to
run in, and then get the proper rights granted.

RLF

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

Dee,

For DTS packages, they run where ever you launch them. If you start a DTS
package running, it runs on your workstation, with your drive mappings, and
your domain privileges. If you schedule the job to run on the SQL Server,
it runs on the server, with the server's drive mappings, and the server's
domain privileges.

The DTS package will (on 2005) run in one of 3 security contexts from the
server:
If owned by a sysadmin, runs as the SQL Server account.
If owned by a sub-sysadmin, runs as the SQL Agent proxy account
OR, if specific SQL Agent Proxies are defined (see credential, proxies,
and principals), it will run as the account that the DTS step is directed to
use.

See the section on Proxy Accounts in
http://technet.microsoft.com/en-us/m.../cc160788.aspx then read the
necessary BOL topics.

So, decide what domain rights you need, what security context you want to
run in, and then get the proper rights granted.

RLF

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

Dee,

For DTS packages, they run where ever you launch them. If you start a DTS
package running, it runs on your workstation, with your drive mappings, and
your domain privileges. If you schedule the job to run on the SQL Server,
it runs on the server, with the server's drive mappings, and the server's
domain privileges.

The DTS package will (on 2005) run in one of 3 security contexts from the
server:
If owned by a sysadmin, runs as the SQL Server account.
If owned by a sub-sysadmin, runs as the SQL Agent proxy account
OR, if specific SQL Agent Proxies are defined (see credential, proxies,
and principals), it will run as the account that the DTS step is directed to
use.

See the section on Proxy Accounts in
http://technet.microsoft.com/en-us/m.../cc160788.aspx then read the
necessary BOL topics.

So, decide what domain rights you need, what security context you want to
run in, and then get the proper rights granted.

RLF

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

Dee,

For DTS packages, they run where ever you launch them. If you start a DTS
package running, it runs on your workstation, with your drive mappings, and
your domain privileges. If you schedule the job to run on the SQL Server,
it runs on the server, with the server's drive mappings, and the server's
domain privileges.

The DTS package will (on 2005) run in one of 3 security contexts from the
server:
If owned by a sysadmin, runs as the SQL Server account.
If owned by a sub-sysadmin, runs as the SQL Agent proxy account
OR, if specific SQL Agent Proxies are defined (see credential, proxies,
and principals), it will run as the account that the DTS step is directed to
use.

See the section on Proxy Accounts in
http://technet.microsoft.com/en-us/m.../cc160788.aspx then read the
necessary BOL topics.

So, decide what domain rights you need, what security context you want to
run in, and then get the proper rights granted.

RLF

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

Dee,

For DTS packages, they run where ever you launch them. If you start a DTS
package running, it runs on your workstation, with your drive mappings, and
your domain privileges. If you schedule the job to run on the SQL Server,
it runs on the server, with the server's drive mappings, and the server's
domain privileges.

The DTS package will (on 2005) run in one of 3 security contexts from the
server:
If owned by a sysadmin, runs as the SQL Server account.
If owned by a sub-sysadmin, runs as the SQL Agent proxy account
OR, if specific SQL Agent Proxies are defined (see credential, proxies,
and principals), it will run as the account that the DTS step is directed to
use.

See the section on Proxy Accounts in
http://technet.microsoft.com/en-us/m.../cc160788.aspx then read the
necessary BOL topics.

So, decide what domain rights you need, what security context you want to
run in, and then get the proper rights granted.

RLF

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

Dee,

For DTS packages, they run where ever you launch them. If you start a DTS
package running, it runs on your workstation, with your drive mappings, and
your domain privileges. If you schedule the job to run on the SQL Server,
it runs on the server, with the server's drive mappings, and the server's
domain privileges.

The DTS package will (on 2005) run in one of 3 security contexts from the
server:
If owned by a sysadmin, runs as the SQL Server account.
If owned by a sub-sysadmin, runs as the SQL Agent proxy account
OR, if specific SQL Agent Proxies are defined (see credential, proxies,
and principals), it will run as the account that the DTS step is directed to
use.

See the section on Proxy Accounts in
http://technet.microsoft.com/en-us/m.../cc160788.aspx then read the
necessary BOL topics.

So, decide what domain rights you need, what security context you want to
run in, and then get the proper rights granted.

RLF

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

Dee,

For DTS packages, they run where ever you launch them. If you start a DTS
package running, it runs on your workstation, with your drive mappings, and
your domain privileges. If you schedule the job to run on the SQL Server,
it runs on the server, with the server's drive mappings, and the server's
domain privileges.

The DTS package will (on 2005) run in one of 3 security contexts from the
server:
If owned by a sysadmin, runs as the SQL Server account.
If owned by a sub-sysadmin, runs as the SQL Agent proxy account
OR, if specific SQL Agent Proxies are defined (see credential, proxies,
and principals), it will run as the account that the DTS step is directed to
use.

See the section on Proxy Accounts in
http://technet.microsoft.com/en-us/m.../cc160788.aspx then read the
necessary BOL topics.

So, decide what domain rights you need, what security context you want to
run in, and then get the proper rights granted.

RLF

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