SSIS package failing while executing as Agent job

  • lazy writer

    Ten Centuries

    Points: 1126

    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

  • John Mitchell-245523

    SSC Guru

    Points: 148771

    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

    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: 148771

    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

    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: 13676

    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: 13676

    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

    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

    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

    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: 13676

    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

    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

    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: 13676

    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

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

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