2

I ran into a puzzling configuration anomaly with a SSIS package establishing a OLEDB connection to a SQL Server 2012 availability group. The DBAs administering the AG requested that all developers target a secondary node by specifying the AG listener name and the DB part of the AG, and using ApplicationIntent=ReadOnly as an additional parameter in their connection strings. The routing to the secondary works with sqlcmd using the -K switch but SSIS packages were still being routed to the primary. An example of the connection string they requested is below. This was utilized as a project level config item in the SSISDB catalog.

Provider=SQLNCLI11.1;Server=xxx.xxxx.xxxxx.xxx.com;UID=YourNameHere;Database=AGDB;ApplicationIntent=ReadOnly;Application Name=SomethingForProfilerAndXEToFilterOn

During debugging, the OLEDB connection was created from scratch using the SQL Server Data Tools GUI. The notable difference in the connection string was the the addition of a space to have parameter now read Application Intent=ReadOnly. Connections to the secondary are now successful.

It appears that the OLEDB driver accepts both variations but only the one with the space is implemented properly with SSIS packages. Is this an anomaly with SSIS or with the SQL Server Native client/OLEDB driver?

1 Answer 1

2

This is by design

Depending on the SNAC protocol you are using there is a slight variation in the application intent connection string property.

When using SQL Native Access Client SQL OLE DB, the application intent connection string should be specified with a space: 'Application Intent' When connecting using SQLClient or ODBC specify 'ApplicationIntent' with no space

https://blogs.msdn.microsoft.com/alwaysonpro/2013/08/02/connect-to-sql-server-using-application-intent-read-only/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.