SSIS package fails when running it as a job on another server

Question

Post reply

SSIS package fails when running it as a job on another server

hariomsingh_kuntal

Old Hand

Points: 346
More actions
December 19, 2007 at 3:59 am

#377566

Hi All,
I hav a strange problem. I created a SSIS package on my machine with protection level as "EncryptAllWithPassword".
When I deploy this package on a SQL Server on another machine (either in File System or SQL Server)and add it as a job in SQL Server Agent and try to run it through a scheduler or manually the job fails with the following message -
Executed as user:IBM-P4-1976\SYSTEM. The package could not be loaded. The step failed.
However if I deploy it on my own machine the job runs successfully.
Also if I run the package using dtexec utility on the another machine it runs successfully.
It is not running as a job on another machine. All the logged in users have administrative rights. My databases are altogether on a different server.
Can any one help me out with this issue...Pleassssseeee...

Viewing 11 posts - 1 through 10 (of 10 total)

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

Powichrowski Marek Hall of Fame Points: 3423 More actions · Answer 1

Try to read this thread:

http://www.sqlservercentral.com/Forums/Topic423399-148-1.aspx

hariomsingh_kuntal Old Hand Points: 346 More actions · Answer 2

I cant get any thing from that thread...I had created the job in the SQLServer agent as explained in the thread....but still the job runs..

Powichrowski Marek Hall of Fame Points: 3423 More actions · Answer 3

Powichrowski Marek

Hall of Fame

Points: 3423

December 21, 2007 at 5:26 am

#762069

Give me more information about job error.

WillyWonka Ten Centuries Points: 1131 More actions · Answer 4

This is a common problem. The SQL Agent needs to have ownership of the package before it can run it as a job. Since you created it on your machine the package is no doubt authored by you. There is a cheat you can use to get around this. Open the package on your machine, go to the properties of the package (same place where you set the password). Change the author to reflect the service account your SQL Agent is running under, you should update the GUID (left click the guid and it will over the option create a new one) as well.

Here is the KB from Microsoft for this.

http://support.microsoft.com/kb/918760

Hope this Helps...Happy Holidays,

Eric

hariomsingh_kuntal Old Hand Points: 346 More actions · Answer 5

Thanx Eric..It solved my problem. I m grateful for your help.

JLG-489362 SSC Veteran Points: 209 More actions · Answer 6

You mention to "go to the same place to change the password" to set the user.

WHERE is that? I've looked in Vis Sutdio, but I'm not finding where to set the password, change the user, etc. -- can you give detailed instructions on WHERE to do that??

I would greatly appreciate it.

WillyWonka (12/21/2007)
This is a common problem. The SQL Agent needs to have ownership of the package before it can run it as a job. Since you created it on your machine the package is no doubt authored by you. There is a cheat you can use to get around this. Open the package on your machine, go to the properties of the package (same place where you set the password). Change the author to reflect the service account your SQL Agent is running under, you should update the GUID (left click the guid and it will over the option create a new one) as well.
Here is the KB from Microsoft for this.
http://support.microsoft.com/kb/918760
Hope this Helps...Happy Holidays,
Eric

JLG-489362 SSC Veteran Points: 209 More actions · Answer 7

Duh, sorry I found it - right in the properties window for the package!!!

Unfortunately, it didn't solve my problem.

I have "encryptsensitivewithuserkey", and then the creator set as a domain\sys_name id (that is also a login on the server), gen'd new guid.

The Run As for the step is set to a proxy that points to that credential/login.

Still getting:

Executed as user: domain\sys_name. The package could not be loaded. The step failed.

IF I SET this domain\sys_name in to the Administrators group on the machine, all works fine. If not, I get the error, so some sort of permission must be messed up, but very poor logging in Sql agent (I get NO package logging because the package isn't loaded, but I do have it turned on).

princa SSCommitted Points: 1819 More actions · Answer 8

WillyWonka (12/21/2007)
This is a common problem. The SQL Agent needs to have ownership of the package before it can run it as a job. Since you created it on your machine the package is no doubt authored by you. There is a cheat you can use to get around this. Open the package on your machine, go to the properties of the package (same place where you set the password). Change the author to reflect the service account your SQL Agent is running under, you should update the GUID (left click the guid and it will over the option create a new one) as well.
Here is the KB from Microsoft for this.
http://support.microsoft.com/kb/918760
Hope this Helps...Happy Holidays,
Eric

Hi Eric, I did the same thing as you said and SQL agent still can't load the SSIS package, I am using Don'tSaveSensitivity for protection level and have a config file to load. But since SQL Agent can't load *.dtsx at the first place, I don't think it's the matter that load config file could help a lot! Any ideas? thanks!!

gotonagle SSC Veteran Points: 210 More actions · Answer 9

gotonagle

SSC Veteran

Points: 210

April 9, 2011 at 3:33 am

#1309528

anyboy got the solution of it?

leah.graber Newbie Points: 5 More actions · Answer 10

One of the solutions to similar issues that I found was to set the DelayValidation property to True. You need to make sure that doing so will not have any adverse affects, but it helped in my cases.

--Leah