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 every day...which is why I'm using an SSIS package and SQL Agent. So, the whole process has to be automated and have all the values necessary--especially the determination of the replica states (primary and secondary)--inside the package so that no manual intervention is required.