Package upgrade - connection strings
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".
