Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Handling Database Mirror Failover within an SSIS Package

Recently I was asked how to handle a Mirrored Database Failover within an SSIS package.  For those of us that don’t write connection strings and simply use Graphical User Interfaces (GUI) to create the connections, we tend to overlook certain attributes that are available when creating connections to SQL Server databases. If you have written connection strings you may already be familiar with the Failover Partner attribute that is available.  When using an ADO.NET connection or a Native OLE DB\SQL Server Native Client, setting the attribute is simple.  If you connect using one of these methods to a database that is mirrored, the SSIS package can take advantage of the drivers ability to recognize the active Server when database mirroring failover occurs.

First, inside of your package right-click in the Connection Managers pane of your SSIS package and create a new connection.  The following dialogue window will appear:

 

 

Choose New OLE DB Connection or New ADO.NET Connection.  Create your connections as normal.  Before clicking OK on the Connection Manager window, choose the ALL option on the left-hand side of the window.

 

Specify the Failover Partner, which would be the IP address or name of the Partner server. To test, run your SSIS package while the data is available on the principal server. Then failover to the partner server and rerun your SSIS package.  Both executions should succeed.  If you have any questions about this please email me at pleblanc@pragmaticworks.com.

Patrick LeBlanc, Founder SQLLunch.com and TSQLScripts.com

 

Comments

Posted by vittorio caminiti on 3 February 2010

Can you separate the SSIS components from the database services components?

like

Server A: SSIS

Server B: SSRS

Server C: SSAS

Server D: databases

So that if the database fail over to the mirror, server A,B, and C can repoint having specified the FAILOVER PARTNER options?

Leave a Comment

Please register or log in to leave a comment.