Connection objects are not available in DTS Designer

Problem Description: The following error is popping up and not allowing opening or running any of the DTS packages. Connection objects are not available in DTS Designer.

"Error in the DLL"
"Need to run the object to perform this operation"

We have reinstalled the MS SQL Server 2000 Standard Edition with Service pack 3 and MSO3031 hot fix. Still the issue continues.
Operating System:Windows 2000You really should open up an MS ticket for this. It's $250. They don't charge you if it's actually their issue. Are there any other errors in the application, system, or SQL Server error logs?|||Thanks For you help
This seems to be related to MDAC issue. Latest MDAC 2.8 was resolved the problem but it is not clear why the MDAC 2.7 was created the problem.sqlsql

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 Manager now showing "New Connection From Data Source..."

Hello,

I've created a SSIS Solution and have created Data Sources. I have two packages. One was created before the Data Sources, and one was created after. The package that was created after is using connections from the Data Sources. I want to change the package before the Data Soruces were created to use them, but when I right click in the Connection Managers pane "New Connection From Data Source.." is not an option.

Did I not add it to the Solution properly?

How do I get it to show?

Did I not refresh something?

Please provide the how if you figure it out.

Thanks

I Don't know why you cannot see that option. In my opinion, a connection manager based in a Data Source does not add any benefit, and it may represent an issue. If you ever need to change the connection string in the CM in the package; BIDS will generate a warning because DS and CM are out of sync....it will not sync them for you. While that won't cause the package to fail; it is certanly annoy during design time.|||if the first package is not part of the solution (you just opened a dtsx file without a solution) then it will not see the datasources from the solution.|||

One thing that worked was to right click on the child package and select "Reload with Upgrade" This gave me another version of the child package that uses the shared Data sources. However, whenever I run the wrapper it runs the older version of the package. I couldn't get it to work using the child package, so to make sure everything was clean. I started over and created a wrapper package that calls the child package and they share Data Sources and a XML Config file to set the connections. Everything was working fine. The child was using the Data Sources in the Connection manager.

I relocated the packages and repointed the XML Config path to the new location. When I ran the wrapper the child package used OLEDB connections instead of the icon of using the Data Source Connection. So I was back again. I selected "Reload with Upgrade", and it generated another version of the child package, with my original connections of using the shared Data Sources. When I run the wrapper, it runs an older version of the child package.

How do I manage the versions? How do I use the new package that is created after the "Reload with Upgrade"? How do I get the wrapper to run the correction version of the child package?

Thanks

Connection Manager not showing "New Connection From Data Source..."

Hello,

I've created a SSIS Solution and have created Data Sources. I have two packages. One was created before the Data Sources, and one was created after. The package that was created after is using connections from the Data Sources. I want to change the package before the Data Soruces were created to use them, but when I right click in the Connection Managers pane "New Connection From Data Source.." is not an option.

Did I not add it to the Solution properly?

How do I get it to show?

Did I not refresh something?

Please provide the how if you figure it out.

Thanks

I Don't know why you cannot see that option. In my opinion, a connection manager based in a Data Source does not add any benefit, and it may represent an issue. If you ever need to change the connection string in the CM in the package; BIDS will generate a warning because DS and CM are out of sync....it will not sync them for you. While that won't cause the package to fail; it is certanly annoy during design time.|||if the first package is not part of the solution (you just opened a dtsx file without a solution) then it will not see the datasources from the solution.|||

One thing that worked was to right click on the child package and select "Reload with Upgrade" This gave me another version of the child package that uses the shared Data sources. However, whenever I run the wrapper it runs the older version of the package. I couldn't get it to work using the child package, so to make sure everything was clean. I started over and created a wrapper package that calls the child package and they share Data Sources and a XML Config file to set the connections. Everything was working fine. The child was using the Data Sources in the Connection manager.

I relocated the packages and repointed the XML Config path to the new location. When I ran the wrapper the child package used OLEDB connections instead of the icon of using the Data Source Connection. So I was back again. I selected "Reload with Upgrade", and it generated another version of the child package, with my original connections of using the shared Data Sources. When I run the wrapper, it runs an older version of the child package.

How do I manage the versions? How do I use the new package that is created after the "Reload with Upgrade"? How do I get the wrapper to run the correction version of the child package?

Thanks

Connection Manager Keeps External Metadata information

We have a Main package and which is calling 2 more other packages.
The first package contains a connection and we are using a Dataflow task. The data flow task has OleDB Data source which is taking getting columns using a Stored Procedure. And the output we need to write in a Flat File.

The second Package also contains the same(The same Tasks, Database and Stored Procedure Calling)
The difference is in the stored procedure Parameters. Based on the different parameters Stored procedures returns the different Columns and Rows output.
When we are trying to Get the second package output in OleDb Data source it shows all the columns which is the output of the First Package because it stores External Meta Data.

So My understanding is the Connection to the same database keeps the External metadata information with the connection and because of that it is always getting the same output columns in Ole DB Data source task in the second Package also.

How to Get my correct output from the second package in this case?
Or If we dont want to store external Meta data with the Connection then is that possible? If yes then How?

Thanks in advance.

The metadata about the columns is not kept at the connection manager level; it is part of the OLE DB component. Have you tried to delete the OLE DB source component and adding a new one? Or , perhaps using the preview button.

|||Thanks for your reply.

We have tried by removing the OLEDB task and also Tried to create with New one but it didnt worked.

We also have tried using New connection and New OLE db task that also didnt worked.

Then we tried by adding the new package and the same thing happend.

But when we are using a other stored procedure with the same connection and same OLE DB Source task that works fine.
If we want to use the same with the same stored procedure name with different parameter then how it is possible?

If I want to clear the Meta data for the same then is there any way to do it?

Thanks.

Connection Manager in SSIS packages

I am trying to run a package outside of the Business Intelligence development Studio. It continuously fails and tell me that my username cannot logon due to a password failure. When I check my connections the password never shows in the connection string and remains blank in the connection properties although I have checked Save Password in the connection properties. Does anyone know how I can save the password in the connection string being utilized in the SSIS package?Passwords are not shown in the properties due to security. I am assuming you are using SQL Server authentication instead of Windows Authentication. When you specify the password and save password, it saves it. But if you open the connection manager again, it won't show you the password (though it is still saved). However, to be able to leave it in the current state, you should now click Cancel to exit the connection manager, instead of OK. If you press OK, it will expect you to enter the password again. I think you must have opened the connection manager, and then pressed Ok, instead of Cancel, which erased the password.|||

I don't think that's the problem. I experiencing the same problem in my development Environment (using SQL Server authentication):

We're a group of developers working on the same package. after setting the connection managers with the correct username and password (and checking the save option) and saving the package by a certain user, when a different user accessing it, he cannot run the package and have to go through all the connection managers and ser the user and passwords all over again.

Any solution?

|||

Liran,

Check the ProtectionLevel property of your package. My guess is that you have ProtectionLevel=EncryptSensitiveWithUserKey

This means that all passwords are encrypted with a user-specific value meaning that only that user can run it.

If you follow best practice (http://blogs.conchango.com/jamiethomson/archive/2006/01/05/2554.aspx) of making your packages location-independant (http://www.windowsitpro.com/SQLServer/Article/ArticleID/47688/SQLServer_47688.html) then this should not be a problem.

-Jamie

sqlsql

Connection Manager Deployment with master/detail package

Hallo,

I'm currently strugling with the setup of our packages for deployment to a new environment.

We are working with a master/detail package setup. One master package is created that will call all child packages. In the master package we don't have any connection towards our source and/or target databases/sourcesystems.

Everything works fine, however, starting to deploy the whole set of packages, it seems that we don't have the option to set specific properties of our detailed packages, e.g. connection properties. But this is just what we need.

When we are adding a job in the Job Agent for our master package to be scheduled, we want to be able to set all different connection manager properties, not only the one from the master package and definitely the ones from the detailed packages as there we switch the connections from the development environment towards the acceptance environment.

I tried to fix this with parent package variables, but I can't set the password property, only the ServerName and UserName can be set, not the Password.

Anyone an idea what the easiest and best approach is to solve this burden?

Thx

Children packages should use package configuration on their connection strings as well; then when deploying the packages to a different enviroment all you have to do is to change the configuration values.

Be warned that there is a known bug when using package configuration with 'parent variables' that, under certain circustances may prevent you to set connection in children packages properly. See details here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126146

Because of that, my recommendation is to define package configurations to set connection strings on every child package(via XML file, SQL server table, env. variable, etc).

"When we are adding a job in the Job Agent for our master package to be scheduled, we want to be able to set all different connection manager properties, not only the one from the master package and definitely the ones from the detailed packages as there we switch the connections from the development environment towards the acceptance environment. "

I think you can only set properties of the invoked package from the command line...as I said before; use configuration packages for that.

connection management

hi.. i have like 50 ssis packages,, most of all have the samne conexion to my target server.,, the user an pwd i'm using is the same for all of them... but the pwd has been changed because of security rules,., the result: these packages will no longer execute correctly .. is there anyway i can change this conexion for all the packages without doing it manually? and if so.. how?

thanks!!!!!

You should have used package configuration from the really beginning. If you didn't and you are planning to go over all the packages again; do that for adding the required package configurations to set the connection string of your connection manager at run time; that way next time a change of this nature happens you will have to update the connection string in a single place. Serach for package configurations to learn more about it.

Rafael Salas