Package to assign a drive works when testing but not on schedule

Question

Package to assign a drive works when testing but not on schedule

born2bongo

SSCrazy

Points: 2477
More actions
March 13, 2008 at 9:47 am

#154194

I have to place a file on a directory in a different domain. I have a generic user id and password to do this, and created a package to assign a drive letter prior to the package that copies the file.
This worked fine when I was testing it, but failed when I put it on a scedule. It makes sense when you think about it, but does anybody have any ideas how I might go about it another way?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Tim Mitchell SSCoach Points: 15685 More actions · Answer 1

How are you running your SQL Server Agent - is it running under a system account or a domain account? Does that account have access to write to the target location?

Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices

born2bongo SSCrazy Points: 2477 More actions · Answer 2

It's running on a domain account.

This isn't an area of expertise for me. I lifted the code to do this from an Access program that simply assigns a drive letter using the generic id and password, then goes on to copy files.

Of course, the Access code was always run in real time and the drive was mapped to the operator's id.

If it helps, the drive exists and points to the correct location. It is set to reconnect at logon, but when you open it you have to log on. The user id is remembered, but the password has to be re supplied.

We are not in a position to change the relationship between our files and the destination

Tim Mitchell SSCoach Points: 15685 More actions · Answer 3

If you're using a mapped drive (such as M:\my_folder), you will have problems because the account under which you run SQL Agent doesn't care about drives mapped by user logins. If you can assign permissions to the target folder for the SQL Agent domain account, change your path to a UNC path (such as \\server\folder) - I've never tried writing to UNC but I believe SSIS will support this.

hth,

Tim

Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

UNC paths are they way to go in SSIS. The only place I have had a problem with not being able to use a UNC so far is with an FTPS program we are using to transfer data. I had to use a physical address on the server in the execute process task for this.

All other file accesses use UNC's.

😎

born2bongo SSCrazy Points: 2477 More actions · Answer 5

But I can't pass the user and password to it, right? I have to have access?

That's not an option, I've been told.