August 16, 2007 at 7:40 am
I am sort of new to SQL and DTS - have been working with SQL 2000 for a number of years to support other apps, learned what I know by experimentation. This is my first attempt at DTS packages. We have a package set up by a previous consultant that contains several Database connections, processes several txt files via "Transform Data Task" (datapump) transformations. Each datapump has a target of a different connection. We are cleaning up our environment, eliminating some unnecessary duplication, and one of those destination connections will no longer exist. we want to change the datapump to point to one of the other connections already defined in the same DTS Package WITHOUT having to re-do all of the column mapping on the TRANSFORMATIONS tab. On the Transform Data Task Properties, Destination Tab, I can change the table, but I cannot find anyplace to change the connection the task points to.
Please help !
August 16, 2007 at 8:55 am
Here's what I'd do.
1. In DTS Designer, open Disconnected Edit and expand the Connections list.
2. Click on the connection that you want to make the new destination for the Transfrom Data task. Note the value of the ID property.
3. Now expand the Tasks list and click on the task you want to change.
4. Double-click on the DestinationConnectionID property and change it to the ID of new destination connection.
5. Close Disconnected Edit and save the package.
6. Open the properties of the Transform Data task and click on the destination tab. Make sure the table name is correct for the new connection.
Greg
Greg
August 16, 2007 at 9:21 am
Thanks for the suggestion.
I did not know such a thing as "Disconnected EDIT" existed.
Alas, I am not certain it worked. I followed your instructions, saved the package. Even closed SQL Enterprise Manager and Stop/started SQL services.
When I went back into the the DTS package, the VISUALIZATION still shows the old connections, but the PROPERTIES windows appear to have the new connection. I change 4 tasks to the same connection.
Should the visualization have changed to reflect the new connections? the TRANSFORM DATA TASK arrow still points to the old connections.
August 16, 2007 at 9:40 am
No, don't expect the visualization to change. That's the "disconnected" part Disconnected Edit. You'll have to change the properties of the destination connection and, possibly, have to recreate the transformations.
Greg
Greg
August 16, 2007 at 1:11 pm
OK - but now, if we delete the database that one of those now un-used connections set up as, we get that the connection is invalid (no database).
If we delete the Connection from the Visualization inthe DTS package, it automatically ALSO DELETES the Transform Data task that was pointing to it. You said not to worry as long as the properties in the Disconnected Edit were correct, so I went there, and the Transform data Task is NO LONGER LISTED -- so it actually DELETED the task from my package, even though it was now pointing to a completely different connection.
I did not find a way to delete the connection using the disconnect edit.
But this is really confusing for some one else who may be looking a this, they won't know that the visualization is not correct.
August 16, 2007 at 2:45 pm
Okay, sorry. Let's try something else. Open the previous version of the package that still has the connection object referencing the deleted database.
Open the properties of the connection object and change the database that it references from the deleted database to the new database. When you click OK, you'll see a list of tasks that reference the connection you just modified. Click OK and open the Data Transformation task. Go to the Destination tab and select the table name in the new database, then click OK.
What you've just done is associate the connection object that you see on the graphical workspace with an new database without deleting the connection object itself.
I probably should have suggested this to you in the first place, but I was focusing on changing the destination of the datapump to another database without knowing that the intention was to drop the old database.
Greg
Greg
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply