August 5, 2008 at 2:30 pm
Hi. First post so please bear with me if I'm not explaining my issue properly. I have an application DB which I am told id is mirrored across 2 servers. Within my SSIS I have a control flow which has a Sql task (Truncate tbl X ) and a dataflow task. This dataflow basiclly pulls a whole tbl through (only 5lk rows approx) and then imports the result set into a tbl X on my data DB. This dataflow is using a static connection string rather than a DNS. Now, the application db I'm extracting from is having a few issues and as such the package won't validate.
I also have a third source for the required data but the formatting it different and that has another tbl to import into (Y).
If both dbaseA and dbaseB are off line I plan to build an additional dataflow to import the data from this third source into tbl (Y) but it is not updated as reglarly and therefore is not the prefered option.
Do I need to write a script along the lines of IF App db.A online=1 use connection server_A.dbase_A else if db.B online =1 use connection server_B.DbaseB else suceed or is there another way?
August 6, 2008 at 7:16 am
Ok, if the two databases are really mirrored (correctly, that is) you should be able to specify both the primary server and the mirror using the SQL native client in your connection manager. If a mirrored database is failed over to the mirror, the client can automatically figure this out and connect to the correct server.
For the other server, or if your first two servers are not mirrored correctly, you have a couple of options. You could create two sets of components and two connection managers. Put them each into a sequence container. Set the DelayValidation property on the SQL task and Data Flow components to true (so the validate when they are accessed). Drag the connector in the control flow from the first sequence container to the second one and change the connector to OnError. So, when one of the components in the first sequence container errors, it will fail over to the second sequence container.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply