Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » SSIS package failing while executing as Agent...
Add to briefcase
14 posts, Page 1 of 2
12»»

SSIS package failing while executing as Agent job Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
lazy writer
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 @ 9:51 AM
Points: 38, Visits: 843
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
John Mitchell-245523
Posted Wednesday, March 20, 2013 8:44 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 8:44 AM
Points: 4,434, Visits: 7,218
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
lazy writer
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 @ 9:51 AM
Points: 38, Visits: 843
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
John Mitchell-245523
Posted Wednesday, March 20, 2013 9:06 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 8:44 AM
Points: 4,434, Visits: 7,218
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
lazy writer
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 @ 9:51 AM
Points: 38, Visits: 843
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
It's the Database!!!
Posted Wednesday, March 20, 2013 9:53 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 17, 2013 7:39 AM
Points: 136, Visits: 307
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.
Post #1433321
It's the Database!!!
Posted Wednesday, March 20, 2013 9:55 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 17, 2013 7:39 AM
Points: 136, Visits: 307
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/
Post #1433323
Welsh Corgi
Posted Wednesday, March 20, 2013 10:21 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 6:19 AM
Points: 3,833, Visits: 4,052
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
Welsh Corgi
Posted Wednesday, March 20, 2013 10:54 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 6:19 AM
Points: 3,833, Visits: 4,052
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
Welsh Corgi
Posted Wednesday, March 20, 2013 11:02 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 6:19 AM
Points: 3,833, Visits: 4,052
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
14 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.