asked on

SSIS 2008 OLEDB Connection Not Valid??

Hi all -

Here's what I'm running on.. ssis/sqlserver2008/windows 7/64bit/service packs applied and up to date.

There is a parent package. In it there is a for loop that loops 70 times (arbitrary but it's what I need) and calls the same set of 14 packages. Each child package is identical except for the query inside it. They consist of a sequence container containing an execute sql task and a script task on error of the execute sql task. (I've tested the query inside and outside of the package and it runs as expected.). The query merges a table from one database to a table in another database. The connection for the execute sql task is set to the source of the merge statement. Its connection is an oledb connection manager with the connection string reading from an xml configuration file. Finally outside that sequence container, I write the results to a logging table (oledb connection manager for that, from an other database).

So I have 2 connection managers per package. (RetainSameConnection = False)

The problem is I'll run this, and then right away I'll get

Error: 0xC0014005 at : The connection type "OLEDB" specified for connection manager "MyDatabase" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.
Error: 0xC0010018 at : Error loading value "<DTS:ConnectionManager xmlns:DTS="www.microsoft.com/SqlServer/Dts">
<DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
<DTS:Property DTS:Name="ObjectName">A2DB</DTS:Property>
<DTS:Property DTS:Name="DTSID">{ADDA0270-ECE0-4D48-97DC-43D36516ED93}</" from node "DTS:ConnectionManager".

Open in new window

Sometimes it happens immediately, Sometimes after 37 loops into the package. Always(or not always) on a different package on a different connection manager. But the error isn't predictable when it will happen,, but it will happen.

I ran regsvr32 dtsconn.dll and that didn't seem to do me any good.

Any ideas on what is happening and what I can do to get rid of whatever is happening?

Thanks!

robertg34

I take this statement " The connection for the execute sql task is set to the source of the merge statement" to mean at least one of the connections is dynamic....if that's the case, I have seen my packages with a dynamic connection have a problem not getting the connection string results back before the sql is ran. It's very random. The fix is to put a sequence container around whatever grabs the connection string to ensure it runs first.

rmm2001

ASKER

I kinda get what you're saying and kinda not. What do you mean by "The fix is to put a sequence container around whatever grabs the connection string to ensure it runs first. "

Right now there is a sequence container around the execute sql task and the script task (on error)

robertg34

If you have code that dynamically grabs the connectionstring, you need to make sure it runs first. SSIS runs everything in parallel so you never know which piece is going to execute first.

rmm2001

ASKER

There's no code that dynamically grabs the connection string. I have a variable (string) that is populated by a package configuration XML file. And then I have that variable set to the connection string of my connection manager. Should I have it differently where I have a script task that reads in the value from the configuration file. And then that sets the connection manager's connection string in the script. And then the rest of the package executes? That seems like a strange

robertg34

The scenario I've experienced is pulling the connection string from a table, so it's slightly different from what you are describing.
Does the variable get set before each execution of each package? If so, I'm still guessing that the variable is not getting set before the next package executes.

Reza Rad

There might be other programs causing this. One way to identify registy keys with incorrect permissions is to compile the program below and run it under low priveleged user account (create a temporary user account, add it only to Users group, run the program). The permissions of the keys identified by the program should be changed to grant Users read access.using System;using Microsoft.Win32; namespace CheckClsidPerm{ class Program { static void Main(string[] args) { RegistryKey clsid = Registry.LocalMachine.OpenSubKey(@"Software\Classes\CLSID"); string[] clsids = clsid.GetSubKeyNames(); Console.WriteLine("found {0} keys", clsids.Length); foreach (string s in clsids) { try { using(RegistryKey clsidKey = clsid.OpenSubKey(s)) { using(RegistryKey ic = clsidKey.OpenSubKey("Implemented Categories")) { } } } catch( Exception e ) { Console.WriteLine("error while reading key {0}: {1}", s, e.Message); } } } }}

reference:
http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/8136833a-f231-47de-8c16-1ccbf3ce95b1/

rmm2001

ASKER

Yeah...I saw that post there. is there any other way how to check this without having to add another user to my computer? (I have full admin rights - just can't create new users). Should I just reinstall sql and vs and see if that fixes the problem? (And hope the issue doesn't come up when I put it on the production machine? Although I hate doing that, If it's an answer I definitely will.

Thanks!

rmm2001

ASKER

I tried to get a non-admin user created on my machine and IT didn't like that. I think my last resort is to reinstall unless there's something else out there?

ASKER CERTIFIED SOLUTION

Reza Rad

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rmm2001

ASKER

Reinstall seemed to fix it. Oddly enough. But ouch!