The only thing you need to worry about is creating the SQL logins on the secondary with the same SID.  If you grab the SID from the primary - create...

You can avoid all of this by switching your deployment model from package deployment to project deployment - and then deploying to the Integration Services Catalog.  If you don't have...

Alan - I agree for this specific scenario the other options will perform better.  However, you didn't answer the questions I asked - what if the requirements or data change?

If the format will always be delimited by a dash - then you can easily utilize a string split utility.  A normal string split will split the results into separate...

I avoid these kinds of issues - by insuring that logins created on the mirrored system are created with the same SID that was used to create the login on...

I realized there is a much simpler way to get the results:

Declare @product Table (
   Product char(1)
  , Location tinyint
  , TransDate DATE

I use something like this to generate dates:

Declare @minDate date = '2018-03-01'
       , @maxDate date = '2018-03-06';

   With dates
 ...

If you are looking for code to identify the primary - this would work:

 Select ags.primary_replica
   From sys.dm_hadr_availability_replica_states     ars
Inner...

Not sure if this is what you are looking for: https://www.softlanding.ca/about-Softlanding/resources/blog/getset-ssrs-data-sources-using-powershell
There is also this: https://www.mssqltips.com/sqlservertip/4738/powershell-commands-for-sql-server-reporting-services/