Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Failure of SSIS Package when Executed from SQL Server Agent Expand / Collapse
Author
Message
Posted Wednesday, October 29, 2008 3:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 18, 2012 4:11 AM
Points: 27, 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.
Post #593442
Posted Wednesday, October 29, 2008 7:47 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 17, 2014 7:04 PM
Points: 1,114, Visits: 2,192
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/

Post #593610
Posted Friday, October 31, 2008 12:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 18, 2012 4:11 AM
Points: 27, 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.
Post #594786
Posted Friday, November 07, 2008 4:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 11, 2009 7:56 AM
Points: 2, Visits: 18
Same issue. has anyone found an answer to this yet??
Post #599340
Posted Tuesday, November 18, 2008 8:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 10, 2014 12:01 PM
Points: 10, Visits: 142
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!
Post #604441
Posted Tuesday, November 18, 2008 10:30 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 18, 2012 4:11 AM
Points: 27, 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
Post #604869
Posted Wednesday, November 19, 2008 7:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 10, 2014 12:01 PM
Points: 10, Visits: 142
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.
Post #605154
Posted Thursday, November 20, 2008 4:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 11, 2009 7:56 AM
Points: 2, Visits: 18
Found a solution!!

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
Post #606176
Posted Friday, November 21, 2008 7:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 10, 2010 4:28 AM
Points: 18, 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.
Post #606504
Posted Friday, November 21, 2008 10:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 09, 2009 7:21 PM
Points: 34, 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
Post #606663
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse