Helpful information and examples on how to use SQL Server Integration Services.
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.
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".
Matt - how about connection strings stored in package configurations? Is the upgrade wizard intelligent enough to find (and fix) these too?
No, the package upgrade wizard only processes the .dtsx file - it doesn't walk the configuration list.
We've modified the OLEDB_NOPROVIDER error to return the name of the provider we failed to find. This