Alright, let me setup the scenario. I have a series of packages that run in a job that we're upgrading to our SSIS 2k8 server from 2k5. So far, not a big deal.
We apply configurations pretty heavily. The pattern for our configurations is this: Local Environment Variable (SSISLOCATION) configures the OLEDB.SSISCONFIG datasource. This datasource then feeds the rest of the configurations for all our packages. This is so our environment variable controls the necessary changes for Dev-QA-Prod progression so a package never is forced to be opened by our deployment teams, as well as ease of modifications for file paths and the like.
All datasources are local to their package and are not shared across the solution.
Now, what's the problem? I can't get the OLEDB.SSISCONFIG to stop being SQLCLI.1. I open one of these packages, strip out all the logging to make sure it's not doing overrides, and then delete the local datasource. So far, so good. I then create a brand new data source to the target system, and it sets up with SQL Native Client 10.0/SQLCLI10.1. All's well.
Save, close, reopen package, and it's setup as SQL Native Client / SQLCLI.1.
The SSISLOCATION Environment Variable is set as:
Data Source=<Servername>;Initial Catalog=SSISCONFIG;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;
Now, I've tried closing/reopening VS 2k8 to force the environment variables to re-cache, no joy. I can't quite seem to figure out what the heck is going on here.
Now, why is this important? Because SQLCLI.1 is occassionally coughing up an error that's boggling my DBA and I'd rather just remove the issue than force him to track down some horribly random error for a driver we don't care about anymore.
I'm open to ideas here.
EDIT: Additional information. Removing the configuration from the environment variable completely does not remove the driver swap from 10.1 to .1 that it performs on me. I can create new datasources that persist as 10.1, but not this one.
- Craig Farrell
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake. For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally TablesTwitter: @AnyWayDBA