Hidden Connections
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/hiddenconnections.asp
-
Just last friday I had a weird problems when migrating DTS packages from development (my workstation) to test environment (customer's site). I used Global variables and Dynamic Properties to set all connections. When I ran each step from designer, it worked. But when I ran the whole package, it took a while and then I got an error message, something about not being able to connect to server or server does not exist. I tried the same technique as you did: building a package from scratch on destination server. Finally i figured it out. In "DTS Package Properties" -> "Logging" Tab I had logging enabled and set to "(local)" server. I also had "Fail package on log error" enabled. Obviusly the "(local)" server set was not the server that currently hosted the package, but my development server. When I changed the log server to an existing local server, it worked OK.
There is one more funny behaviour. Since connections were made on one server, they still contain that server's name. If I want to edit "Data Driven Query Task", I have to run "Dynamic Properties Task" that set the connections first. Then I can view/edit "Data Driven Query Task".
-
I ran into the same issue from a different angle. There is a quick way to solve the problem. Add an existing connection. In the dropdown list of existing connections, select the phantom connection. Close the dialog box. Delete the connection object just added. You will find that the phantom is no longer there.
Craig W. Bell
Craig W. Bell -
Thanks for the workaround. Hadn't figured out a way to do it.
One of those things that drove me crazy for awhile. Especially when it ran in interactive mode.
Steve Jones
-
When I want to change the name of an existing connection, I always use the "Disconnected Edit" feature to change the name. Renaming through the GUI will always result in these "phantom connections". Changing the name of the existing connection does not cause any ripple effects, as DTS uses the Connection ID not the name to reference the connection. When you do change the name, you have to open the connection in DTS Designer to see the new name displayed. At this point it will ask you if you want to reset transformations that reference the connection. Choose to NOT reset transformation, or you will have to start again from scratch.
Sincerely,
Mark Cudmore, MCP
Sincerely,
Mark Cudmore, MCDBA -
I think you can get rid of that old connection by right clicking, choosing Add Connection, and selecting the old connection from the list of existing connections. Now delete the visible connection for the icon and I think the vestigal connection will be removed from the DTS package.
-
In packages that need to be migrated to differnent servers, I set the connection and task properties dynamically using "Dynamic Properties Task" and global variables.
The Global Variables are passed to the package on execution. So this means I can run the package on any server without having to make any changes to the package.
Phill
Thanks
Phill Carter
--------------------
Colt 45 - the original point and click interface
-
A good solution. I do something similar. I can snag the servername from the local server in some pacakges.
Not so smooth in SQL 7, but works great in sQL 2000
Steve Jones
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply