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»»

SSIS package failing while executing as Agent job Expand / Collapse
Author
Message
Posted Wednesday, March 20, 2013 8:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 4:38 AM
Points: 42, Visits: 1,063
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

Post #1433252
Posted Wednesday, March 20, 2013 8:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:46 AM
Points: 5,429, Visits: 10,094
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
Post #1433264
Posted Wednesday, March 20, 2013 8:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 4:38 AM
Points: 42, Visits: 1,063
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
Post #1433279
Posted Wednesday, March 20, 2013 9:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:46 AM
Points: 5,429, Visits: 10,094
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
Post #1433287
Posted Wednesday, March 20, 2013 9:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 4:38 AM
Points: 42, Visits: 1,063
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?
Post #1433314
Posted Wednesday, March 20, 2013 9:53 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 18, 2014 10:25 AM
Points: 301, Visits: 595
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.


MCSA SQL Server 2012
Post #1433321
Posted Wednesday, March 20, 2013 9:55 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 18, 2014 10:25 AM
Points: 301, Visits: 595
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/


MCSA SQL Server 2012
Post #1433323
Posted Wednesday, March 20, 2013 10:21 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:57 PM
Points: 4,242, Visits: 4,290
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/

Post #1433332
Posted Wednesday, March 20, 2013 10:54 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:57 PM
Points: 4,242, Visits: 4,290
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/

Post #1433347
Posted Wednesday, March 20, 2013 11:02 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:57 PM
Points: 4,242, Visits: 4,290
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/

Post #1433349
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse