Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1469
  • Last Modified:

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!
0
rmm2001
Asked:
rmm2001
  • 5
  • 3
  • 2
1 Solution
 
robertg34Commented:
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
 
rmm2001Author Commented:
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
 
robertg34Commented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
rmm2001Author Commented:
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
 
robertg34Commented:
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
 
Reza RadConsultant, TrainerCommented:
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
 
rmm2001Author Commented:
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
 
rmm2001Author Commented:
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
 
Reza RadConsultant, TrainerCommented:
I think a re-install will be helpful too.
try that and let me us know the result
0
 
rmm2001Author Commented:
Reinstall seemed to fix it. Oddly enough. But ouch!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now