Tuesday, March 20, 2012

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

No comments:

Post a Comment