Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Failure of SSIS Package when Executed from SQL Server Agent


Failure of SSIS Package when Executed from SQL Server Agent

Author
Message
mackie
mackie
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 219
Good day,

We’ve created a basic SSIS package that connects to an Oracle Database, retrieves information, and dumps the info into a SQL Server Database Table. We’ve tested the package within BIDS, and it works 100%.

We then created a SQL Server Agent job to run this package every 30 minutes. The owner of the SQL Agent Job has sysadmin rights, and for confidentiality purposes is called [MYDOMAIN]\Bob. The first step in the Job was set to type “SQL Server Integration Services Package”. The package source is set to File System.

Initially there were issues with authentication to Oracle, so we created a credential (Using [MYDOMAIN]\Bob), and then assigned a proxy called “SSIS_Runner” to that credential. After this was done we set the first step in the job to run under the “SSIS_Runner” proxy.

The issue comes in when the scheduled job runs. While we are logged in to the server under the Bob’s credentials, the SQL Server Agent Job runs without any issues. The moment we log off the SQL Server Agent job fails to run. The error message we get from SQL Log Viewer is: "Executed as user: [MYDOMAIN]\Bob. The package execution failed. The step Failed."

Due the vagueness of the SQL Agent error message, we've enabled logging on the package. We ran the SQL Agent job again, and the log file says that there were issues validating the steps where SSIS connects to Oracle. My question is why would this SSIS package work when we are logged on to the server, and not run when we are logged off?

We’ve tried to change the owner of the package to the Service Account that runs the SSIS package, and that makes no difference.

Below is an excerpt of the error messages in the log file:

************************************************************
OnError,SERVERNAME,SERVERNAME\Bob,Load WORL Transactions,{37C87481-0A2D-4A36-ADAB-DAA463BA1CE3},{1A2B35D6-5C07-410E-BA77-09AEF9724758},2008/10/28 04:35:18 PM,2008/10/28 04:35:18 PM,-1071611876,0x,The AcquireConnection method call to the connection manager "CR4JINA”" failed with error code 0xC0202009.

OnError,SERVERNAME,SERVERNAME\Bob,Load WORL Transactions,{37C87481-0A2D-4A36-ADAB-DAA463BA1CE3},{1A2B35D6-5C07-410E-BA77-09AEF9724758},2008/10/28 04:35:18 PM,2008/10/28 04:35:18 PM,-1073450985,0x,component "WORL" (1) failed validation and returned error code 0xC020801C.

OnError,SERVERNAME,SERVERNAME\Bob,Load WORL Transactions,{37C87481-0A2D-4A36-ADAB-DAA463BA1CE3},{1A2B35D6-5C07-410E-BA77-09AEF9724758},2008/10/28 04:35:18 PM,2008/10/28 04:35:18 PM,-1073450996,0x,One or more component failed validation.
************************************************************

Your help would be greatly appreciated.

Kind Regards,
Ignacio.
CozyRoc
CozyRoc
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1454 Visits: 2235
Ignacio,

I have seen this before. And this is a tricky one. What is happening is that by default the SQL Job Agent is executing with your proxy account, using light-weight execution without loading the user profile. That's why you see this weird behavior where the execution is working when the user is logged on to the machine. You can verify that I'm correct by doing the following experiment. Run your SSIS package using the "runas" command. There is an option "/noprofile". If you run it with it, you will most probably see the same behavior as what you see now.

Now what you need to do is to find a way and execute your package with complete user profile. If you find information about this, please post it back.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/


mackie
mackie
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 219
Hi CozyRoc,

Thanks for the reply. Yes, very odd behaviour. I'll look into loading the full user profile, and as soon as I find something I'll post it here.

I'm not working on this particular issue this week, so as soon as I have time I'll test your theory and provide feedback after the test has been completed.

Have a good weekend.
mike.spraggett
mike.spraggett
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 18
Same issue. has anyone found an answer to this yet??
Randy Anthony
Randy Anthony
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 148
Definitely hope someone has come to a conclusion on this issue, a fellow developer has encountered this exact same problem. Looking forward to a resolution. Thanks!
mackie
mackie
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 219
Hi Randy,

I've been looking everywhere for a solution to this but have found nothing. I'm wondering if a call should be logged with Microsoft to resolve this issue?

Kind Regards,
Ignacio
Randy Anthony
Randy Anthony
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 148
Ignacio,
My developer has indicated that this issue is very similar, but that he cannot run it locally as indicated above. He'd like to pursue another tangent and didn't want to confuse the issue. Hope someone comes up with something, these issues have to be related. Good luck.
mike.spraggett
mike.spraggett
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 18
Found a solution!! w00t

Basic problem I think is that the packages security level is set to EncryptSensitiveWithUserKey which when you are logged off the server the job will not execute as the userkey cannot be fully loaded.

My solution was to change the package protectionlevel to EncryptSensitiveWithPassword, set the password and then set the job owner to my server admin account.

For me this has worked and I hope it works for you guys as well.

Mike
virtualeffect
virtualeffect
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 71
Hi, I've been working with Randy On this. The package encryption level was only set to EncryptSensitiveWithUserKey while it was being edited and for testing. I tried a couple of different settings there ( both DontSaveSensitive and EncryptSensitiveWithPassword ).

Originally what appeared to be happening is that the password for a connection string was not being looked up in the ConfigDB by the package. So the package errored saying login for the userid failed.

Then we switched to using EncryptSensitiveWithPassword. And then the errors were related to decryption. I believe thats when I started using a Proxy user.

To setup the proxy we created a local user, added it to the administrators group and also the sqlagentuser group in active directory. Then we created the credential in sqlserver based on that login ID. And that credential was then used by the Proxy account. On the rights to the proxy account I basically ended up giving it ever right I could..

So when all that was done, It went back to failing on the userid login; again suggesting it couldn't lookup the password in the configdb.

So we are kind stuck right at that point.
timothy_kramer
timothy_kramer
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 95
I have seen this issue and the SQL Server Agent user profile is the one that is used in execution of the scheduled jobs. So if user1 creates an SSIS package, you can run it in BIDS and everything looks good. However, if the owner of the scheduled job is user2, then they have to be granted specific rights to run other packages that they do not create.

I have mine set up this way and everything works.



Tim
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search