Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Upgraded Package from 2k5 to 2k8 with configuration having problems with driver swap


Upgraded Package from 2k5 to 2k8 with configuration having problems with driver swap

Author
Message
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5715 Visits: 7660
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
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5715 Visits: 7660
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
Andy Leonard
Andy Leonard
Mr or Mrs. 500
Mr or Mrs. 500 (539 reputation)Mr or Mrs. 500 (539 reputation)Mr or Mrs. 500 (539 reputation)Mr or Mrs. 500 (539 reputation)Mr or Mrs. 500 (539 reputation)Mr or Mrs. 500 (539 reputation)Mr or Mrs. 500 (539 reputation)Mr or Mrs. 500 (539 reputation)

Group: General Forum Members
Points: 539 Visits: 1092
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
Data Philosopher, Enterprise Data & Analytics
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5715 Visits: 7660
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
Andy Leonard
Andy Leonard
Mr or Mrs. 500
Mr or Mrs. 500 (539 reputation)Mr or Mrs. 500 (539 reputation)Mr or Mrs. 500 (539 reputation)Mr or Mrs. 500 (539 reputation)Mr or Mrs. 500 (539 reputation)Mr or Mrs. 500 (539 reputation)Mr or Mrs. 500 (539 reputation)Mr or Mrs. 500 (539 reputation)

Group: General Forum Members
Points: 539 Visits: 1092
Can you send me one of the offending packages? My email address is my firstname.lastname *at* gmail.

Andy Leonard
Data Philosopher, Enterprise Data & Analytics
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5715 Visits: 7660
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search