HI All,
i have a job that runs a number of ssis packages. at the moment it runs against the primary database however i want to run it against the read only db. the setup is sql server 2012 with always on. the connection string for the package is the listener so i need to add a step to force the job to check if its primary or secondary and then run the next step.
what i was thinking was a piece of sql like so:
DECLARE @preferredReplica int
SET @preferredReplica = (SELECT [master].sys.fn_hadr_backup_is_preferred_replica('DBNAME'))
IF (@preferredReplica = 1)
Begin
go to step 2.
question is how do i programmatically get the step to the next step?
so something like
IF (@preferredReplica = 1)
Begin
run package or don't run package
Thanks in advance.
i have a job that runs a number of ssis packages. at the moment it runs against the primary database however i want to run it against the read only db. the setup is sql server 2012 with always on. the connection string for the package is the listener so i need to add a step to force the job to check if its primary or secondary and then run the next step.
what i was thinking was a piece of sql like so:
DECLARE @preferredReplica int
SET @preferredReplica = (SELECT [master].sys.fn_hadr_backup_is_preferred_replica('DBNAME'))
IF (@preferredReplica = 1)
Begin
go to step 2.
question is how do i programmatically get the step to the next step?
so something like
IF (@preferredReplica = 1)
Begin
run package or don't run package
Thanks in advance.
