SSIS package failing while executing as Agent job

Question

SSIS package failing while executing as Agent job

lazy writer

Ten Centuries

Points: 1126
More actions
March 20, 2013 at 8:29 am

#271891

Hi,
I had some weird issues while running SSIS package as agent job. I can run SSIS package manually but failing to run as agent job. I thought its permission issues with Agent service account then I RDP into the server using service account login (domain\agentserviceaccount) and run the package and it went fine.
any idea?
much appreciated 🙂
Thanks

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

What error message are you getting when you run it as a job? Does the package use package configurations? Could it be that the configuration settings are being picked up from a different place when you run it as a job?

John

lazy writer Ten Centuries Points: 1126 More actions · Answer 2

Thanks John,

This package wouldn't use any configurations and everything saved with in the package. Interestingly it works fine when I login as Agent service account then give right click and execute package all good but only problem when running as SQL Agent Job.

Error message keep varying and not the same for every failure as shown below..

failed with the following error: "String or binary data would be truncated

DSN contains an architecture mismatch between the Driver and Application

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

What are the differences between your login and the SQL Server Agent account login? Default database? Language? Anything else?

Does your package do anything user-sensitive, for example INSERT INTO MyTable (mycol) VALUES (SUSER_NAME())? If you could find out what task it's failing on, that will help you to work out the reason for the failure.

John

lazy writer Ten Centuries Points: 1126 More actions · Answer 4

thanks for your response John,

Here I am not comparing it with my login and service account.

just to narrow down the issue I logged into the server as service account using service account credentials and then ran the package execute utility and it wen fine.

then I ran the same package as Agent job and then it failing 🙁 -- While running as Agent Job it will be executed under the same service account security context.

any idea?

lkennedy76 SSChampion Points: 13683 More actions · Answer 5

lazy writer (3/20/2013)
Hi,
I had some weird issues while running SSIS package as agent job. I can run SSIS package manually but failing to run as agent job. I thought its permission issues with Agent service account then I RDP into the server using service account login (domain\agentserviceaccount) and run the package and it went fine.
any idea?
much appreciated 🙂
Thanks

You need to have the package encryted with the user. you can open and save it all day however if you do not encrypt it won't save and it will fail.

MCSE SQL Server 2012\2014\2016

lkennedy76 SSChampion Points: 13683 More actions · Answer 6

It's the Database!!! (3/20/2013)
lazy writer (3/20/2013)
Hi,
I had some weird issues while running SSIS package as agent job. I can run SSIS package manually but failing to run as agent job. I thought its permission issues with Agent service account then I RDP into the server using service account login (domain\agentserviceaccount) and run the package and it went fine.
any idea?
much appreciated 🙂
Thanks
You need to have the package encryted with the user. you can open and save it all day however if you do not encrypt it won't save and it will fail.

see this link for more detail...

http://www.mssqltips.com/sqlservertip/2091/securing-your-ssis-packages-using-package-protection-level/

MCSE SQL Server 2012\2014\2016

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

Right Click on the Project and Select Properties,

On the left click the debugging tab.

Make sure that Run64bitRuntime is set to false.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

Looking at your error message more closely it could be related to the DSN.

Consider where it is a 32 bit DSN or a 64 bit DSN.

http://sql-ution.com/32-bit-odbc-connections-with-64-bit-ssis

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 9

It's the Database!!! (3/20/2013)
It's the Database!!! (3/20/2013)
lazy writer (3/20/2013)
Hi,
I had some weird issues while running SSIS package as agent job. I can run SSIS package manually but failing to run as agent job. I thought its permission issues with Agent service account then I RDP into the server using service account login (domain\agentserviceaccount) and run the package and it went fine.
any idea?
much appreciated 🙂
Thanks
I capture my error messages.
This is an example when the password was not stored:
OLE DB provider "MSDASQL" for linked server "LS_RDB_DWH_POLICY" returned message "[Oracle][ODBC][Ora]ORA-01017: invalid username/password; logon denied ".
You need to have the package encryted with the user. you can open and save it all day however if you do not encrypt it won't save and it will fail.
see this link for more detail...
http://www.mssqltips.com/sqlservertip/2091/securing-your-ssis-packages-using-package-protection-level/

I error that the OP is receving is not consistent with package encrypted.

You will get a completly different error if it is password related.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

lkennedy76 SSChampion Points: 13683 More actions · Answer 10

Welsh Corgi (3/20/2013)
Right Click on the Project and Select Properties,
On the left click the debugging tab.
Make sure that Run64bitRuntime is set to false.

If it is set to TRUE even while debugging it will not run at all, it looks like he got past that...However the last time this happened to me, it was package encryption, just sayin, doesn't hurt to look at all avenues...

MCSE SQL Server 2012\2014\2016

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 11

I had 64 bit set to true on many occassions and it still ran in debug but failed when running as a job.

I just ran a package with 64 bit set to true and it sucessfully completed.

Is the OP connecting to Oracle, DB2, etc or are they only using SQL Server Authenication?

If they are only using SQL Server Authenication specifying EncyptSensitiveWithPassword is not going to do anything.

The error that the OP is getting is not the type of error you would expect if there was an issue with the password.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

lazy writer Ten Centuries Points: 1126 More actions · Answer 12

Thanks Welsh for your help.

When I set the job to run package as 32 bit then it started working fine. 🙂

this package connecting to remote MYSQL Server to pull the data.

Thanks John and every one.

lkennedy76 SSChampion Points: 13683 More actions · Answer 13

lazy writer (3/21/2013)
Thanks Welsh for your help.
When I set the job to run package as 32 bit then it started working fine. 🙂
Thanks John and every one.

Glad that was it, it is always something like that, that gets me all the time! When ever I create a new package this is one of my first steps! Good luck! and don't forget to encrypt it if you are saving a user name and password or you will end up in the same boat!

😀

MCSE SQL Server 2012\2014\2016