Solved

SSIS 2008 OLEDB Connection Not Valid??

Posted on 2010-09-03
10
1,353 Views
Last Modified: 2013-11-10
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!
0
Comment
Question by:rmm2001
  • 5
  • 3
  • 2
10 Comments
 
LVL 5

Expert Comment

by:robertg34
ID: 33604482
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.  
 
0
 
LVL 7

Author Comment

by:rmm2001
ID: 33604498
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)
0
 
LVL 5

Expert Comment

by:robertg34
ID: 33604515
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.  
0
 
LVL 7

Author Comment

by:rmm2001
ID: 33604531
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
0
 
LVL 5

Expert Comment

by:robertg34
ID: 33604641
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.  
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 30

Expert Comment

by:Reza Rad
ID: 33605620
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/


0
 
LVL 7

Author Comment

by:rmm2001
ID: 33607358
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!
0
 
LVL 7

Author Comment

by:rmm2001
ID: 33632449
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?
0
 
LVL 30

Accepted Solution

by:
Reza Rad earned 500 total points
ID: 33655666
I think a re-install will be helpful too.
try that and let me us know the result
0
 
LVL 7

Author Comment

by:rmm2001
ID: 33684781
Reinstall seemed to fix it. Oddly enough. But ouch!
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now