Conditional Connection in SSIS based on Availability Group status

I'm working on an SSIS package that will refresh dev databases from production backups. The package has been in place for years, but we're deploying Availability Groups on the dev instance, so the tasks have to be changed to work with both the primary and secondary replicas.
Background: All instances are SQL Server 2014 SP2. One production instance (PD1), two dev instances (DV3 & DV7) and one utility instance (DV1), where the SSIS package is deployed to SSISDB and where the SQL Agent job runs.
In the SSIS package, after the full backups are obtained and put on a network share from production, the next set of tasks (in a separate SSIS package) needs to figure out which node hosts the primary replica. I'm following the steps listed here to go about this work, and everything works great if it's hard-coded (which, of course won't work if/when a failover occurs).
The only way I've been able to think about approaching this is, at runtime, evaluate which instance is primary and then proceed with the tasks (which, by the way are all "Execute SQL Task" objects--requiring their own connection definition). I've seen a few examples of how to use expressions in ConnectionStrings and variables, but can't figure out how to set the value returned from the below query in SQL Server to return the correct instance to set the connection string in the package.

select cs.replica_server_name
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 rs.role = 2
and n.ag_name = 'DVAG001'

Ideally, there would be two of these connection managers, one for the primary and one for the secondary, which I could then use in the "Execute SQL Task" objects. Then everything would work perfectly.
There very well may be other ways to do this, but I'm not very experienced in SSIS or development outside of TSQL.

UPDATE: The above process is done ev

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)

1. Variables

Create 2 variables with data type String

• ServernamePrimary

• ServernameSecondary

1. 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)

1. 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

1. 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

1. 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.

StackExchange Database Administrators Q#204963, answer score: 9