Conditional Connection in SSIS based on Availability Group status

Let's assume that you have an availability group with only one secondary replica (you could easily expand this sample for more replicas)

1. Connections

You will need 3 connections:

• AG listener (to run the query that will return which server is holding the primary an secondary)
• Primary (to be parameterized with the primary server)
• Secondary (to be parameterized with the secondary server)

2. Variables

Create 2 variables with data type String

• ServernamePrimary
• ServernameSecondary

3. Query

To map a query result set to a variable using Execute SQL Task, your query needs to return only one row. You will map each column to the variable that you wish. Knowing that, I've changed you query to the following:

WITH CTE_AGStatus as (
    select cs.replica_server_name, rs.role
    from sys.dm_hadr_availability_replica_states rs
    join sys.dm_hadr_availability_replica_cluster_states cs
        on rs.replica_id = cs.replica_id
    join sys.dm_hadr_name_id_map n
        on rs.group_id = n.ag_id
    where n.ag_name = 'DVAG001'
)

select  ServernamePrimary = MAX(case when role = 1 then replica_server_name end)
,       ServernameSecondary = MAX(case when role = 2 then replica_server_name end)
from CTE_AGStatus

(Feel free to change it as you wish, just remember to return only one row)

4. Mapping the result set to variables

On the execute SQL Task Editor, paste the query above, set the OLEDB connection 'AG listener' and change the result set to 'Single row'

Then go to the 'Result Set' tab and map the columns to the variables

5. Change the connection strings

Click on the 'Primary' connection manager and go to the 'Properties' tab, then click on 'Expressions', select the property 'ServerName' and add your variable on the Expression.

Repeat the process to the 'Secondary' connection manager

6. Check the process

To validate, you could add other steps and use breakpoints to check how your variables are running. The next steps are up to you. Now you have one connection pointing to each server of your AG.