2

I am trying to connect to SQL Server 2017 Always On from an SSIS package (built in SSDT 2017).

I am using an OLEDB Connection Manager. 'Application Intent' is set to READWRITE. However, where do I specify MultiSubnetFailover? I don't see an option for it. And do I have to add Server=TCP in the connection?

When I run the package the connection string looks like this...

Data Source=myTestServer\mssql;User ID=TestUser;Initial Catalog=TestDB;Provider=SQLNCLI11.1;Persist Security Info=True;Auto Translate=False;
1

1 Answer 1

0

In short, the OLEDB v11 doesn't support the MultiSubnetFailover parameter. Check out this helpful article. You can instead set the ConnectionTimeout to 30 seconds, which seems to let it failover to the other node, but then every OLEDB step in your package could take 30 seconds to fire up.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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