Scheduled DTS + odbc with drive mapping

Hi, thanks for reading my message.

My company is running SQL Server 2000, and I need to have a DTS scheduled to run every night that extracts data from our other TimberLine database. The connection to this Timberline database is done through an ODBC which uses a mapped drive to the local database directory of the database.

Now, this mapping is performed only when somebody logs onto the server, therefore manually running the DTS is no problem.

The problem is that when I schedule the DTS, the system does not know where to find the local database directory of the TimberLine system, because the local database dir is not mapped to a drive. Therefore I get an error called "TimberLine ODBC drive is not activated".. This is because the ODBC cannot find the local dbase dir, because the drive is not mapped..

Does anyone know a solution for this? Maybe something like mapping a drive as a system service or something?Did you try UNC file path?

Why don't you have the drive mapped?

And if it's a database, aren't you using named pipes or tcp/ip?|||Originally posted by sirorange
Does anyone know a solution for this? Maybe something like mapping a drive as a system service or something?

If the Workstation you are running this from is a Windows NT/2000/XP box, you could login, let the login script map the drive, and then lock the computer. You can lock the computer by pressing CTRL + ALT + DEL and then clicking "Lock Computer."

This will not log you out, so your drives will still be present.|||1)configure the sql agent service to run under domain/user and not local system, create a sql job that execute the DTS using dtsrun utility, schedule the job.
or
2) run the DTS from windows schedule task as batch command or any other script|||Originally posted by sirorange
Hi, thanks for reading my message.

My company is running SQL Server 2000, and I need to have a DTS scheduled to run every night that extracts data from our other TimberLine database. The connection to this Timberline database is done through an ODBC which uses a mapped drive to the local database directory of the database.

Now, this mapping is performed only when somebody logs onto the server, therefore manually running the DTS is no problem.

The problem is that when I schedule the DTS, the system does not know where to find the local database directory of the TimberLine system, because the local database dir is not mapped to a drive. Therefore I get an error called "TimberLine ODBC drive is not activated".. This is because the ODBC cannot find the local dbase dir, because the drive is not mapped..

Does anyone know a solution for this? Maybe something like mapping a drive as a system service or something?

create a batch file with following

REM DELETE EXISTING MAP
net use z: /delete
REM CREATE Z: MAP DRIVE
net use z: \\server\path /user:YOUR_NETWORK_ID password /delete

Use a os command step in SQL AgentJob and call above batch file in it.
and run it as first step before DTS step

amit