Job Fails

I have a package that runs fine when manually executed, but if a job is created that executes the package the job fails. The job is run as the same account that runs sql server agent. The error message that appears is to see the job history but the job history just says the package failed. When I try to add logging no log is created.When you manually execute the package are you executing it under the same account that Agent is running. If not, then try and do that and see if the package runs. If it doesn't then it would most likely be a permissions problem for that account. If it does then you should try and post this in the Agent forum.

Thanks,
Matt|||

I get this exact same problem... but only with one specific package.
Other packages run fine - both manually and as jobs.

This particular package runs fine manually but NOT as job.
This package only has file tasks which copies file from a remote location to local disk. I have double-checked permissions. The account which has permission on the remote location is the same account under which SQL Server, and SQL Agent are running.

Any clue?

thanks,
Nitesh

|||So you are running both SQLServer and Agent as a domain account and the same domain acount has permissions on the remote server? Then I don't have any further thoughts but you should first verify that if you use runas to run a cmd shell under the agent account that the package still works. You should verify that the job isn't running under a proxy with a different account. If both these are true then you should post in the Agent forum since this is not a SSIS problem if the package runs when not run under Agent.

Sorry I can't be of more assistance,
Matt|||

Yes, I am running both SQLServer and Agent as a domain account and the same domain acount has permissions on the remote server.

I have 16 packages scheduled like this. 15 of them work fine (none of them copy files from remote location). Only this package breaks.

I will try to post this on Agent forum and update this thread with replies.

thanks,
Nitesh

|||Matt,

I found the problem.

I had mapped the network share (\\servername\sharename) to X:\ on the box running SSIS. So, my file source connection strings looked like X:\filename.txt

When I changed the file source connection string to \\servername\sharename\filename.txt , then it started working fine - both manually AND as a job.

Although my problem is solved, can you throw some light on why it happens like this?

thanks,
Nitesh|||

How are you mapping to X:? Are you executing the package on a different machine when you execute it under a job? Sounds like a problem with the context of the machine; doesn't have the mapping. So, if it doesn't have the mapping, it will be looking for the literal X: drive.
K

|||

Nitesh Ambastha wrote:

I had mapped the network share (\\servername\sharename) to X:\ on the box running SSIS. So, my file source connection strings looked like X:\filename.txt

When I changed the file source connection string to \\servername\sharename\filename.txt , then it started working fine - both manually AND as a job.

Although my problem is solved, can you throw some light on why it happens like this?

This is the way share security works in Windows 2003 - the network share mapped by user is private to this user session, and is invisible to other users running on same machine.

E.g. I mapped a disk using 'net share x: \\machine\share' and now have a drive x:. But when I (simultaneously) create another remote desktop connection to the same machine, I don't have x: drive.

The Agent creates a new user session, and thus this session does not see the share created by interactive user session.|||Thanks Michael.
That explains it.

regards,
Nitesh|||This is really not important but after reading your post, I created 1 desktop connection, mapped the network drive (X: drive to \\remoteserver\sharename) and then created another desktop connection to the same machine - and I found the mapped network drive (X: drive). Am I missing something or maybe network admins here carry over my profile and map it for me :(

regards,
Nitesh|||Did you check 'reconnect at logon' option (or /persistent:yes in net.exe command line)? In this case every logon creates a connection, but technically they are different :)

Is this Windows 2003? I'm not sure it worked the same way on Windows 2000.

This is simple to check with SysInternal's WinObj utility - \DosDevices object used to live in root, but now it is \Sessions\<N>\DosDevices.