Solved

SSIS 2008 OLEDB Connection Not Valid??

Posted on 2010-09-03
10
1,391 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

738 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