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