Failure of SSIS Package when Executed from SQL Server Agent

Question

Failure of SSIS Package when Executed from SQL Server Agent

mackie

Right there with Babe

Points: 745
More actions
October 29, 2008 at 3:22 am

#127328

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.

Viewing 13 posts - 1 through 12 (of 12 total)

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

COZYROC One Orange Chip Points: 28499 More actions · Answer 1

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 Right there with Babe Points: 745 More actions · Answer 2

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 SSC Enthusiast Points: 102 More actions · Answer 3

mike.spraggett

SSC Enthusiast

Points: 102

November 7, 2008 at 4:41 pm

#895874

Same issue. has anyone found an answer to this yet??

Randy Anthony SSC-Addicted Points: 498 More actions · Answer 4

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 Right there with Babe Points: 745 More actions · Answer 5

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 SSC-Addicted Points: 498 More actions · Answer 6

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 SSC Enthusiast Points: 102 More actions · Answer 7

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 Right there with Babe Points: 736 More actions · Answer 8

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 Ten Centuries Points: 1358 More actions · Answer 9

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

virtualeffect Right there with Babe Points: 736 More actions · Answer 10

Well even if I run it manually it won't get the config data from the sql configdb.

So possibly there is something wrong other than what I am thinking?

I'm not sure what you mean by the Scheduled Job owner..

from my understanding, the owner of the schedules job is whatever logon id is used as the longon as for the sql server agent. But in this case, it's part of the proxy user.

Using the xml file way of storing the config data worked well, but the dba for the server that the database is on wants it down with the configdb..

skosanabv Valued Member Points: 66 More actions · Answer 11

I found the solution

I experienced the same Issue with my packages, and tried all these solutions posted here, but none of them worked for me.

What I then did:

1. SQL Server Configuration Manager

2. SQL Services

3. Select SQL Server Agent, right click, select properties.

4. Under log on tab, select 'this account' radio button, and select the user who created the package.

Remember the user can be changed in the Solution Explorer in visual studio/SQL Server Data tools of the package in case there is a need to do that. HOW: Right Click the Project, Properties, Common properties, Project, CreatorName, Change it to whatever name you will configure the SQL Server agent to logon using.

Redeploy your package but not necessary. Start job at Step to test execution. Hurrraaaaayyy... Your Job is up and running.

I trust that this helps. Please give me feedback if this has helped you. Thank you.

Bongani

Phil Parkin SSC Guru Points: 246686 More actions · Answer 12

skosanabv (11/19/2013)
I found the solution
I experienced the same Issue with my packages, and tried all these solutions posted here, but none of them worked for me.
What I then did:
1. SQL Server Configuration Manager
2. SQL Services
3. Select SQL Server Agent, right click, select properties.
4. Under log on tab, select 'this account' radio button, and select the user who created the package.
Remember the user can be changed in the Solution Explorer in visual studio/SQL Server Data tools of the package in case there is a need to do that. HOW: Right Click the Project, Properties, Common properties, Project, CreatorName, Change it to whatever name you will configure the SQL Server agent to logon using.
Redeploy your package but not necessary. Start job at Step to test execution. Hurrraaaaayyy... Your Job is up and running.
I trust that this helps. Please give me feedback if this has helped you. Thank you.
Bongani

Are you suggesting that the SQL Agent service needs to run with the same account as created a package?

And therefore that every deployed package needs to be created by the same account?

🙂

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.