none
Error at the time of Package Execution on AlwaysOn Environment

    Dotaz

  • Hi Guys,


    We are facing issue during executing SSIS Package in AlwaysOn Environment. We have 2 nodes on Win 2012 R2 DC & SQL Serer 2016. The SSIS DB is the part of Availability Databases & in the SSIS, we are AG_Name for connection. We have also enabled the "Enable  AlwaysOn Support" feature.


    We have also created job for the package & while executing the Package, it is throwing below error message:

    Failed to execute IS server package because of error 0x80131904
    Package path: \SSISDB\SSISPackages\Interface2018Primary\FORT_Events.dtsx, Environment reference Id: NULL.
    Description: Please create a master key in the database or open the master key in the session before performing this operation.
    Source: .Net SqlClient Data Provider
    Started:  11:48:49 AM
    Finished: 11:48:49 AM
    Elapsed:  0.171 seconds


    Will you please help on the issue.


    Thanks
    D
    • Upravený Dhanush007 čtvrtek 12. července 2018 7:34
    čtvrtek 12. července 2018 6:33

Odpovědi

  • Hi Dhanush007,

    Please try to execute following queries on the node that issues occurred:

    USE [SSISDB]
    OPEN master Key decryption by password = 'Password' -- Password used when creating SSISDB
    ALTER Master Key ADD encryption by Service Master Key

    Reference: SSIS package execution failure when SSISDB is configured with AlwaysOn

    Check if this helps

    Regards,

    Pirlo Zhang 


    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.

    • Označen jako odpověď Dhanush007 pátek 13. července 2018 8:59
    čtvrtek 12. července 2018 7:39
  • Hi Dhanush,

    I was facing same issue & don't have any issue to open the master key on both Primary & secondary Replica.

    Please use below script with password which was used at the time of SSIS DB inclusion in Availability group.

    It'll work during failover also but you need to run on both the nodes.

    USE SSISDB      

    OPEN master Key decryption by password = '!@#$%^&*(AA' 
    ALTER Master Key ADD encryption by Service Master Key

    Thanks

    DKG

    • Označen jako odpověď Dhanush007 pátek 13. července 2018 8:59
    čtvrtek 12. července 2018 9:58

Všechny reakce

  • Hi Dhanush007,

    Please try to execute following queries on the node that issues occurred:

    USE [SSISDB]
    OPEN master Key decryption by password = 'Password' -- Password used when creating SSISDB
    ALTER Master Key ADD encryption by Service Master Key

    Reference: SSIS package execution failure when SSISDB is configured with AlwaysOn

    Check if this helps

    Regards,

    Pirlo Zhang 


    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.

    • Označen jako odpověď Dhanush007 pátek 13. července 2018 8:59
    čtvrtek 12. července 2018 7:39
  • Hi Dhanush,

    I was facing same issue & don't have any issue to open the master key on both Primary & secondary Replica.

    Please use below script with password which was used at the time of SSIS DB inclusion in Availability group.

    It'll work during failover also but you need to run on both the nodes.

    USE SSISDB      

    OPEN master Key decryption by password = '!@#$%^&*(AA' 
    ALTER Master Key ADD encryption by Service Master Key

    Thanks

    DKG

    • Označen jako odpověď Dhanush007 pátek 13. července 2018 8:59
    čtvrtek 12. července 2018 9:58