Switch Connection

Question

Switch Connection

peacesells

SSCrazy

Points: 2882
More actions
June 10, 2013 at 10:11 am

#278864

I am trying to load data from a replicated database in SSIS-if for some reason the replicated fails then i need to point my source to the production server. I am doing a record count comparison to find out if the replication has not failed. My problem though is to find out how we can switch the connection based on the result of the record counts. I have other destination databases connections down the stream which do not need to be switched. The switching only applies to the source database. Any suggestions on the approach - Thank You.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

david.alcock SSCertifiable Points: 6021 More actions · Answer 1

Maybe have the two destinations set up in connection manager.

You can then run a count in a SQL task that in turn populates a variable. You then have two data flow tasks, one for each connection - change the connector to use an expression based on the variable perhaps?

Not very elegant I know, I've never used it but there is a conditional split component too, that might be more helpful?

In fact, yes, the component can route rows to different outputs, here's the link:

http://msdn.microsoft.com/en-us/library/ms137886.aspx

'Only he who wanders finds new paths'

peacesells SSCrazy Points: 2882 More actions · Answer 2

david.alcock (6/10/2013)
Maybe have the two destinations set up in connection manager.
You can then run a count in a SQL task that in turn populates a variable. You then have two data flow tasks, one for each connection - change the connector to use an expression based on the variable perhaps?
Not very elegant I know, I've never used it but there is a conditional split component too, that might be more helpful?
In fact, yes, the component can route rows to different outputs, here's the link:
http://msdn.microsoft.com/en-us/library/ms137886.aspx

Conditional split is something totally different- its routing your destination path based on a given condition. My requirement is to be able to switch to one of the two Connection Managers based on a given value.

david.alcock SSCertifiable Points: 6021 More actions · Answer 3

Apologies, I didnt see the 'source database' - had another thought:

Would it be possible to use a script task to populate a variable (or variables) which is then used as the connection string for the connection object? I've done similar things when working with files.

'Only he who wanders finds new paths'