Package upgrade - connection strings

Published Mar 25 2019 02:07 PM 77 Views
Not applicable
First published on MSDN on May 21, 2008

We tried to make our package upgrade as seamless as possible, but one problem we've seen people running into is with their connection strings. As I've previously noted, the provider name for SQL Native Client was changed in 2008 from SQLNCLI to SQLNCLI10. This will could cause your connection managers to fail at runtime, as we wouldn't be able to locate the correct OLEDB provider.

Our package upgrade process will modify your connection strings automatically (this is a configurable option , that is on by default). However, there are a couple of scenarios that we don't handle.

Expressions

If you find your connections look right at design time, but fail at runtime, check whether your connection manager is using expressions to set its connection string. The package upgrade process will not modify values stored in variables or expressions.

Data Source (.ds) files

The package upgrade process doesn't modify connection strings contained within Data Source files. Although we modify the package itself, you'll be prompted to synchronize the connection string the first time you open the package in the designer. This will reset the Provider back to the old value.

A workaround for both of these scenarios is to do a find/replace on all of the files in the solution, changing "SQLNCLI.1" to "SQLNCLI10.1".

%3CLINGO-SUB%20id%3D%22lingo-sub-387427%22%20slang%3D%22en-US%22%3EPackage%20upgrade%20-%20connection%20strings%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-387427%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3E%20First%20published%20on%20MSDN%20on%20May%2021%2C%202008%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CP%3EWe%20tried%20to%20make%20our%20package%20upgrade%20as%20seamless%20as%20possible%2C%20but%20one%20problem%20we've%20seen%20people%20running%20into%20is%20with%20their%20connection%20strings.%20As%20I've%20previously%20noted%2C%20the%20%3CA%20href%3D%22http%3A%2F%2Fblogs.msdn.com%2Fmattm%2Farchive%2F2008%2F03%2F21%2Fsome-notes-about-sql-native-client-in-2008.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20provider%20name%20for%20SQL%20Native%20Client%20was%20changed%20in%202008%20%3C%2FA%3E%20from%20SQLNCLI%20to%20SQLNCLI10.%20This%20will%20could%20cause%20your%20connection%20managers%20to%20fail%20at%20runtime%2C%20as%20we%20wouldn't%20be%20able%20to%20locate%20the%20correct%20OLEDB%20provider.%3C%2FP%3E%0A%20%20%3CP%3EOur%20%3CA%20href%3D%22http%3A%2F%2Fblogs.msdn.com%2Fmattm%2Farchive%2F2008%2F03%2F21%2Fpackage-upgrade-wizard.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20package%20upgrade%20%3C%2FA%3E%20process%20will%20modify%20your%20connection%20strings%20automatically%20(this%20is%20a%20configurable%20%3CA%20href%3D%22http%3A%2F%2Fblogs.msdn.com%2Fmattm%2Farchive%2F2008%2F03%2F21%2Fsetting-package-upgrade-options-in-the-designer.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20option%20%3C%2FA%3E%20%2C%20that%20is%20on%20by%20default).%20However%2C%20there%20are%20a%20couple%20of%20scenarios%20that%20we%20don't%20handle.%3C%2FP%3E%0A%20%20%3CP%3E%3CSTRONG%3E%20Expressions%20%3C%2FSTRONG%3E%3C%2FP%3E%0A%20%20%3CP%3EIf%20you%20find%20your%20connections%20look%20right%20at%20design%20time%2C%20but%20fail%20at%20runtime%2C%20check%20whether%20your%20connection%20manager%20is%20using%20expressions%20to%20set%20its%20connection%20string.%20The%20package%20upgrade%20process%20will%20not%20modify%20values%20stored%20in%20variables%20or%20expressions.%3C%2FP%3E%0A%20%20%3CP%3E%3CSTRONG%3E%20Data%20Source%20(.ds)%20files%20%3C%2FSTRONG%3E%3C%2FP%3E%0A%20%20%3CP%3EThe%20package%20upgrade%20process%20doesn't%20modify%20connection%20strings%20contained%20within%20Data%20Source%20files.%20Although%20we%20modify%20the%20package%20itself%2C%20you'll%20be%20prompted%20to%20synchronize%20the%20connection%20string%20the%20first%20time%20you%20open%20the%20package%20in%20the%20designer.%20This%20will%20reset%20the%20Provider%20back%20to%20the%20old%20value.%3C%2FP%3E%0A%20%20%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F99381iBD4BD1E278B46D7B%22%20%2F%3E%3C%2FP%3E%0A%20%20%3CP%3EA%20workaround%20for%20both%20of%20these%20scenarios%20is%20to%20do%20a%20find%2Freplace%20on%20all%20of%20the%20files%20in%20the%20solution%2C%20changing%20%22SQLNCLI.1%22%20to%20%22SQLNCLI10.1%22.%3C%2FP%3E%0A%20%0A%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-387427%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20May%2021%2C%202008%20We%20tried%20to%20make%20our%20package%20upgrade%20as%20seamless%20as%20possible%2C%20but%20one%20problem%20we've%20seen%20people%20running%20into%20is%20with%20their%20connection%20strings.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-387427%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Econnectivity%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Ekatmai%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EUpgrade%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Mar 25 2019 02:07 PM
Updated by: