Connection Managers not installed?

apologies if this is the wrong group ..

whenever I create an Integrated Services project and attempt to add a Connection Manager I get the error

Connection Manager 'blah' is not properly installed on this computer.

(Microsoft.DataTransformationServices.Design)

This happens with all menu options except New Connection..

I have uninstalled and reinstalled both Visual Studio 2005 and SQL Server 2005 and made sure I have selected everything in each case

what am I missing?

Thanks

Chris

seems this has happened to others

see Jason Lodice thread for solution

Chris

Connection Managers are recognized after migrating to another server

Hi,

My database admin just migrated my packages from development to user testing server.

My packages configured to read the connection manager properties from the .dtsconfig.

But I am getting "The connection "CRPRDNMSQEZ.CIReporting2" is not found"

CRPRDNMSQEZ.CIReporting2 is being my connection manager.

I did find and replace server name in the dtsconfig but I guess it didn't work.

Any help is appreciated.

At a guess your connection manager name contained the orginal server name. The connection manager's connection string property ( you may have used other properties such as ServerName) would also have included the server name. When you did the find and replace you change both the connection name and the property values. The connection name should not have changed as that is the object in the package that you are trying to address.

For example take this config file snippet -

<Configuration ConfiguredType="Property" Path="\Package.Connections[ConnName].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>Data Source=MachineNameInstance;User ID=FunnyName;Password=somethingsecure;</ConfiguredValue>
</Configuration>

ConnName is the connection name, what you see in the Connections tray in the package.

MachineNameInstance is the bit to change. It is probably if you just accepted the defaults the designer gave you that your config file looked a bit like this-

<Configuration ConfiguredType="Property" Path="\Package.Connections[CRPRDNMSQEZ.CIReporting2].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>Data Source=CRPRDNMSQEZ;Initial Catalog=CIReporting2</ConfiguredValue>
</Configuration>

Doing a find and replace on CRPRDNMSQEZ would have found two hits, one of them being incorrect from your point of view. I strongly recomend when building packages you name things such as connections with functional or logical names, not physical names. It means the name continues to sensible over entire the package lifetime, e.g.

<Configuration ConfiguredType="Property" Path="\Package.Connections[DWReportServer].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>Data Source=CRPRDNMSQEZ;Initial Catalog=CIReporting2</ConfiguredValue>
</Configuration>

|||Sounds like the name of the connection manager in the .dtsconfig file does not match the name of a connection manager inside the package. Double check that it does.

Connection Managers are NOT recognized after migrating to another server

Hi,

My database admin just migrated my packages from development to user testing server.

My packages configured to read the connection manager properties from the .dtsconfig.

But I am getting "The connection "CRPRDNMSQEZ.CIReporting2" is not found"

CRPRDNMSQEZ.CIReporting2 is being my connection manager.

I did find and replace server name in the dtsconfig but I guess it didn't work.

Any help is appreciated.

At a guess your connection manager name contained the orginal server name. The connection manager's connection string property ( you may have used other properties such as ServerName) would also have included the server name. When you did the find and replace you change both the connection name and the property values. The connection name should not have changed as that is the object in the package that you are trying to address.

For example take this config file snippet -

<Configuration ConfiguredType="Property" Path="\Package.Connections[ConnName].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>Data Source=MachineNameInstance;User ID=FunnyName;Password=somethingsecure;</ConfiguredValue>
</Configuration>

ConnName is the connection name, what you see in the Connections tray in the package.

MachineNameInstance is the bit to change. It is probably if you just accepted the defaults the designer gave you that your config file looked a bit like this-

<Configuration ConfiguredType="Property" Path="\Package.Connections[CRPRDNMSQEZ.CIReporting2].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>Data Source=CRPRDNMSQEZ;Initial Catalog=CIReporting2</ConfiguredValue>
</Configuration>

Doing a find and replace on CRPRDNMSQEZ would have found two hits, one of them being incorrect from your point of view. I strongly recomend when building packages you name things such as connections with functional or logical names, not physical names. It means the name continues to sensible over entire the package lifetime, e.g.

<Configuration ConfiguredType="Property" Path="\Package.Connections[DWReportServer].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>Data Source=CRPRDNMSQEZ;Initial Catalog=CIReporting2</ConfiguredValue>
</Configuration>

|||Sounds like the name of the connection manager in the .dtsconfig file does not match the name of a connection manager inside the package. Double check that it does.sqlsql

Connection managers - OLE DB

Hi There

Quick question.In DTS when you had multiples tasks connecting to the same Database, it was reccomended that you create multiple connections for the same database and let each task use a different connection, thereby making a new connection for each one.
In SSIS if i define 1 OLE DB connection to a database, but i have say 6 tasks using that connection that run simultaneously, does eachone open a new connection to the database to make it more efficient? Or do i still need to define multiple connections to the same database and assign each task a different connection?

Thanx

SeanDL wrote:

Hi There

Quick question.In DTS when you had multiples tasks connecting to the same Database, it was reccomended that you create multiple connections for the same database and let each task use a different connection, thereby making a new connection for each one.
In SSIS if i define 1 OLE DB connection to a database, but i have say 6 tasks using that connection that run simultaneously, does eachone open a new connection to the database to make it more efficient? Or do i still need to define multiple connections to the same database and assign each task a different connection?

Thanx

Sean,
In that situation each of the 6 tasks will use the same connection as far as I know. I guess this is why the connection managers are so called - its because they manage a single connection thereby enabling many tasks to use it.

I'm no DBA but I suspect that this is more efficient than opening multiple connections.

-Jamie|||Hi Jamie

In DTS it was definately better to use multiple connections when extracting large amounts of data from each connection.But no mention has been made of it in SSIS, i guess i could do some tests myself to see if it helps performance?

Thanx|||I'd be interested in seeing any metrics you get.

I don't claim to know much about DTS but I suspect that there are different reasons that multiple connections are recommended that outweigh the benefit of only having 1 connection to the server.
I also suspect that even if one connection object were used each task that used it would still have its own connection (I stand to be corrected here). This is not the case with SSIS.

-Jamie|||Sean, the reason connection managers are called that is to distinguish them from connections as named in DTS. The reason it was recommended to use multiple connections in DTS is because workflow would be serialized if pointing to the same connection. Connection managers actually generate a new connection everytime you call AcquireConnection so the workflow isn't serialized. Each task or transform etc. that calls AcquireConnection on the connection manager receives a new physical connection.
So, for SSIS, just use one connection manager. Also, consider using Data Sources and Data Source views for your OLEDB connections. I blogged about it a bit recently. That simplifies matters even more and makes your packages that much more portable.
Thanks,|||Kirk,
Right then, I'm a wee bit confused. I'll tell you why.

I was recently working on a package where I had an Execute SQL Task followed by a data-flow which extracted from a SQL Server DB. Both tasks used the same connection manager.
In the Execute SQL Task there was a SET ROWCOUNT 20000 statement. When I ran the package, the data-flow only processed 20000 rows - obviously because it was affected by the SET ROWCOUNT command in the previous task.

Now, if both tasks had seperate connections that wouldn't happen. Or would it? I've done a quick scan of google to find out if I could find out the scope of SET ROWCOUNT but couldn't find anything (and its getting late :)

Any ideas?

-Jamie|||Hi Kirk

Thanx for the info, that is what i thought but i just could not find any documentation to verify it.

Although Jamies issue is a bit worrying because set rowcount's scope is definately within a connection/transaction?

Thanx|||Hi Kirk

Ok i am also still confused, my concurrent connections (5 of them) using the same conenction manager we all executing in parrellel, then i changed the connection manager to use the OLE DB for SQL Server NOT the native client that iw as using), now only 3 of the 5 tasks execute concurrently when 1 finishes the next one starts.Therefore it seems only 3 concurrent conenctions are allowed, i have checked BOL , but i cannot find where this setting is as i would like all tasks to run in parallel, if you know it would be greatly appreciated.

Thanx|||Check the MaxConcurrentExecutables property of the package by clicking on the package design surface and then looking at the properties. If it's set to -1, the heuristic used to determine max executables is n+2, where n is the number of processors.
Change it to a higher number and watch them all fly together. :)
K|||Thanx Kirk

Spot on , i did see that setting but i thought -1 was unlimited.Thanx A million works like a bomb !|||

Jamie Thomson wrote:

Kirk,
Right then, I'm a wee bit confused. I'll tell you why.

I was recently working on a package where I had an Execute SQL Task followed by a data-flow which extracted from a SQL Server DB. Both tasks used the same connection manager.
In the Execute SQL Task there was a SET ROWCOUNT 20000 statement. When I ran the package, the data-flow only processed 20000 rows - obviously because it was affected by the SET ROWCOUNT command in the previous task.

Now, if both tasks had seperate connections that wouldn't happen. Or would it? I've done a quick scan of google to find out if I could find out the scope of SET ROWCOUNT but couldn't find anything (and its getting late :)

Any ideas?

-Jamie

OK, I've just built a simple mock-up to demo this and lo and behold it doesn't happen anymore. Setting rowcount in the Excute SQL Task didn't affect the dataflow.
I swear that when I did this before though it DID affect the data-flow...and I have witnesses to prove it. It occurred on April CTP whereas I am now using June CTP. perhaps that's why!!!

For now, ignore this. If it happens again I'll raise it.

Weird!!

-Jamie|||Did you have retain same connection set to true?
I don't have a good answer here. Without looking at the package, it's hard to know for sure.|||Yes, did so myself. Couldn't repro it either...

connection manager losing credentials

On my SSIS package I have 2 connection managers, both are connecting to an Oracle db. I enter in the ID and Password then click 'test connection' I'm able to connect to the database fine. I then go to a data flow of one of my control flow task. Open up my OleDb Source and click preview. The SQL query executes with no issues. I can see the data from my connection manager source.

I then go and run the package and I get this error message:

[OLE DB Source [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "OracleConnection" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

I've been racking my brain on this for a few days and still no luck on getting this to work.

You probably have the 'protection level' property of the package set to 'dontsavesensitive'. You could either change this to 'encryptsensitivewithuserkey' or set the password property of the connection manager through a configuration.

|||

I've done both, changed the package property and tried a config file and I get the same results.

|||Enable package logging, or if you are using BIDS, check the progress tabs and see if you any warning about package configurations.

Package configuration should solve the problem. This KB article has some related info:

http://support.microsoft.com/kb/918760|||

I got it working actually now, well sort of.

I had to remove all of my connection managers, close down BIDs open it back up, add new connection managers, change the package property setting again and it work.

Now I'm running into another issue

I have one package (dtsx file) that calls other dtsx file (all within the same solution) and its unable to connect to the packages it needs to call, though all the packages are in the same solution and the same database

|||Please provide more details:

How are you running the packages (BIDS, DTEXEC, etc)?

Where are the packages stored?

Error messages?|||

I'm trying to run it in BIDS right now and I get this error message:

Error: Error 0xC0012050 while preparing to load the package. Package failed validation from the ExecutePackage task. The package cannot run. .

If I deploy them to my SQL server and try to execute it from there (right click, execute), I'm getting this error message:

Error: Error 0xC0012050 while preparing to load the package. Package failed validation from the ExecutePackage task. The package cannot run. .

and I list of warnings saying it can't find the connection manager and so on.

|||You have a problem here. If you remove a connection manager; you need to go to every single place where that connection manager was being referenced and provide a new/valid connection manager.

The message you are getting indicates that the package failed validation; that means SSIS won't even try to run it. You need to remove all the references to that connection manager(s) you deleted.|||

This is driven my insane.

I can now run the SSIS package in BIDS, but I get the error:

'the acquireconnection method call to the connection manager 'oracle' has failed

and I've removed all of the connection managers used and created new ones and I still get the above error on my SQL server