none
SSIS Job Failing when database is failed over to Secondary Replica. RRS feed

  • Question

  • Hi,

    2 weeks ago we deployed and configured some SSIS packages to run via SQL Agent Job. Everything was running smoothly until when the Database (Part of Basic Availability Group) was failed over to secondary replica due performance issue. See the error snippet below:

    

    We setup the SQL Agent Job to run via Proxy Account and the SQL Agent account is an AD account. 
    

    The snippet below shows the Architecture of our BAG Setup.

    Questions: 

    1. Why the job is running smoothly when the database is on Primary?

    2. Why it failed when the database was failed over even though  the connection is via listener? 

    I though it was due to SQL Agent user was missing on the secondary server and created one with the same permissions but it did not made any difference. 





    • Edited by Shaddy_1 Wednesday, November 13, 2019 3:35 PM
    Tuesday, November 12, 2019 6:27 PM

All replies

  • Hi Shaddy_1,

    When you create a package on your local machine and create a connection in it, you have the option to save the password of that connection. However by default it encrypts this password so that in can only be decrypted when you' re running the package on the same machine, with the same account
    So, in the above scenario, if you run the package with different account or you run the package in a Remote Sql Server, it fails with the "Login failed.." error as it is not able to decrypt the password. 

    Resolution:
    To correct the problem, you should opt between either of the two options:
    1. Set the ProtectionLevel in SSIS. Encrypt the package with "EncryptSensitiveWithPassword" or "EncryptAllWithPassword" and provide a package password each time User wants to edit/manipulate the package.
    2. Create a configuration file to provide the connection information during Package runtime.

    Please refer to:

    SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred :- Reasons and troubleshooting.

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, November 13, 2019 1:56 AM
  • Hi Shaddy_1,

    When you create a package on your local machine and create a connection in it, you have the option to save the password of that connection. However by default it encrypts this password so that in can only be decrypted when you' re running the package on the same machine, with the same account
    So, in the above scenario, if you run the package with different account or you run the package in a Remote Sql Server, it fails with the "Login failed.." error as it is not able to decrypt the password. 

    Resolution:
    To correct the problem, you should opt between either of the two options:
    1. Set the ProtectionLevel in SSIS. Encrypt the package with "EncryptSensitiveWithPassword" or "EncryptAllWithPassword" and provide a package password each time User wants to edit/manipulate the package.
    2. Create a configuration file to provide the connection information during Package runtime.

    Please refer to:

    SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred :- Reasons and troubleshooting.

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Hi Mona,

    Thanks for your input but the package runs fine currently and it being run on the same machine where it resides. It makes connection to the database on remote machine to query the data. When the remote  database that the package is querying is on Primary  server the package did not failed but when the database was failed over to  secondary it failed with this error.  




    • Edited by Shaddy_1 Wednesday, November 13, 2019 3:50 PM
    Wednesday, November 13, 2019 3:12 PM
  • Hi Shaddy_1,

    Please refer to SSIS package does not run when called from a SQL Server Agent job step .

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, November 15, 2019 7:34 AM