Pakcage Execution Error under SQL server agent

Hi SSIS experts!

I have been trying to schedule a package I design to run off hour, but unable to do so. Here is a strange issue:

1. I am able to run and excute the package successfully through VSS. After I finished designing all my flows and containers, my exceution was successful to all my data sources.

2. I was able to deploy and run the actual package by sending to my local file system and it runs successfully through Execute Package Utility.

HOWEVER!!! when I tried to schedule this package through file system under sql server agent to run at night or through start job within SQL agent always failed...

I am puzzled so I added some logging on the package. The error message shows the following....

<message>The connection "{087B883F-D188-440A-9501-FF38CF5CEC28}" is not found. This error is thrown by Connections collection when the specific connection element is not found.

<message>Failed to acquire connection "10.0.2.2.LogDB.jhwang". Connection may not be configured correctly or you may not have the right permissions on this connection.

But I thought if I had set the connection correctly to remember my passwords and using SQL server standard login within my package (connection manager) and should resolved the connection issues....

Why did it failed when I try to run it under sqlagent? But not through Execute Package Utility? Is there is a special setting I need to do for it to run under sql agent?

I notice within the job step when I choose the file source to point to my package... there was a tab called data sources where it has the connections I defined in my package. Does it matter if I put a check box on them or not? Either way they failed to connect.

Please help!

JON

Not sure what the problem is but you may want to debug it by using this technique: http://blogs.conchango.com/jamiethomson/archive/2005/10/10/2253.aspx

-Jamie

Pakcage Error...How to read files on a different domain?

So did some troubleshooting on the my previous post -http://forums.microsoft.com/forums/ShowPost.aspx?PostID=272319&SiteID=1

Thanks to everyone who tried to help... So I got some insight into what is happening.

My package was having trouble reading files on a remote domain although I mapped the drives locally. This does not cause either running the package to failed either through directly running under VSS, or Execution Utility. But it failed when I try to schedule it through SQL server agent.

I wonder if it is the security context problem and asking for help of how to get around that issue.

So I have a for each file loop container that retrieve filenames on a remote domain which has a different security account then my local account. So how I got around that was to map that drive locally to a drive letter...example W:\

then I use W:\ as my path within my package. I was trying to figure out where I can declare the connection within my package specifically, but don't know if I could do that.

If I run this through VSS or exectuion utility, it works okay. I was able to see W:\ and all the files on that mapped drive and read in the data.

However, when I schedule it under SQL agent, it doesn't see any files then exited the package as success right the way, because it has nothing to do....

So is there a different between the security context on mapped drive between VSS and my SQL agent? If so...How do I get around that challenge?

BTW, the two domain do not have share accounts between each others. I had to specified and map the drives explicitly.

THANKS!
JON

When you map a drive it is for that session on that machine for that account. So unless you are the service itself, it will have no effect on SQL Server Agent.

Ideally you would sort out trusts between the domains, maybe mapped accounts or such like.

A slightly less elegant method is to map the drive programatically. You can do this with the "net use" command from the command line, or just wrap it in a batch file. You can specify credentials on the command as well. Note that you do not need to assign a drive letter, and I prefer not to as it eliminates the risk of drive letter clashes in what is after all an unattended environment. You just map and reference the drive using the UNC path only.

|||

So can you pelase clearify your comment please?

What do you mean by unless you are the service itself, it will have not effect on SQL server agent?

So I run SQL server agent using my domain logon account, which is the same account I used to logon to my session which mapped the drive.... However the drive is in a differnt domain.

I still unable to use the SQL server agent to work. It still doesn't see the content of the mapped drive... yet the package works through VSS or Excute Utility Manager.

Why is that? Also Any link or command help of this "net use" you suggested?

Thank you very much for your helps!
Jon

|||What is meant is that just because you mapped the drive during your desktop session, that drive mapping is not available from within the executing SSIS package. It is a different "session". The package itself would need to do its own drive mapping in order for it to gain access. Then when the package is complete, the drive will be unmapped. (Of course, you desktop session drive mapping will be unaffected throughout this.)|||That is indeed what I meant. "The package itself would need to do its own drive mapping in order for it to gain access", and to do this you can use the NET USE command.