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

  • 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[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • 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[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • 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, Chief Data Engineer, Enterprise Data & Analytics

  • 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[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Can you send me one of the offending packages? My email address is my firstname.lastname *at* gmail.

    Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

  • 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[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply