Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Upgraded Package from 2k5 to 2k8 with configuration having problems with driver swap Expand / Collapse
Author
Message
Posted Friday, October 26, 2012 4:39 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:46 PM
Points: 5,384, Visits: 7,458
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.

NARM?

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 Tables

Twitter: @AnyWayDBA
Post #1377905
Posted Monday, October 29, 2012 11:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:46 PM
Points: 5,384, Visits: 7,458
Update for the testing I've done.

I can completely rebuild these packages without issue from scratch, but I cannot alter an existing one that's been upgraded from 2k5. Something is hiding in the cache, somewhere.

I'm open to wild suggestions at this point... otherwise I spend a week 'from scratch' rebuilding these.



- 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 Tables

Twitter: @AnyWayDBA
Post #1378394
Posted Monday, October 29, 2012 7:04 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 7:47 AM
Points: 388, Visits: 1,043
Hi Craig,

Poke around in the XML for the package. The easy way is to right-click in Solution Explorer and click View Code. See if it's lurking in there.

Andy


Andy Leonard
CSO, Linchpin People
Follow me on Twitter: @AndyLeonard
Post #1378530
Posted Tuesday, October 30, 2012 4:00 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:46 PM
Points: 5,384, Visits: 7,458
Checked the XML on the source file. First I removed the data source entirely (though two of my other configurations are relying on it) and checked the XML.

I can find two calls to it (same as the # of configs involved with it) ... one from the environment variable and the other for the configuration that relies on it.

I've yanked logging out as well, and that hasn't removed the issue.

I managed to, alchemically, fix one of the packages to stay in 10.1. I have absolutely no idea how I did it. When I got back to the package it had swapped the Environment Variable into a called configuration from a sql table. Reversing this to an environment variable corrected the problem for that package... and even attempting to replicate that process doesn't fix the issue for other packages.

I'm stumped.



- 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 Tables

Twitter: @AnyWayDBA
Post #1379064
Posted Tuesday, October 30, 2012 5:07 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 7:47 AM
Points: 388, Visits: 1,043
Can you send me one of the offending packages? My email address is my firstname.lastname *at* gmail.

Andy Leonard
CSO, Linchpin People
Follow me on Twitter: @AndyLeonard
Post #1379084
Posted Tuesday, October 30, 2012 5:50 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:46 PM
Points: 5,384, Visits: 7,458
Andy Leonard (10/30/2012)
Can you send me one of the offending packages? My email address is my firstname.lastname *at* gmail.


Email sent. Thanks for looking at this for me Andy.



- 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 Tables

Twitter: @AnyWayDBA
Post #1379097
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse