SQL Clone
SQLServerCentral is supported by Redgate
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



Posted by vittorio caminiti on 3 February 2010

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


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.