Quick question: Is it possible to change the graphical connections
associated with a Transform Data Task?

I have a have a DTS package with 30 Transform Data Tasks all pointing at the
same database connection, all with precedence constaints designed to result
in a serial execution of each task. Now that I have gotten this successfully
working and debugged, I want to alter this design to introduce a few more
connections where possible to improve performance. This is easy enough to
accomplish using the Disconnected Edit feature. I simply change the
DestinationConnectionID and voila, it works. Peeking inside the task, it is
clearly using the new connection, but graphically, the task is still
pointing to the old connection.

Now, I've put several hours into the design and debugging of each of these
30 transforms, so I don't want to delete them simply to change the graphical
appearance. At the same time I'd like the DTS graphical view to be accurate.

OK.

Yes you can easily change the Source and destination connection properties
and the SourceObjectName and DestinationObjectName for the DataPump task .
The problem comes in the Transformation objects. If the Source and
destination attribute names are the same then no problems. DTS will only
read the metadata and not have to change anything. if however the names
change then the Transformations are invalidated. You will then need to
remap them yourself.

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Isak Dinesen" <no5pam (AT) hotmail (DOT) com> wrote

Thanks for your reply, but I am afraid you've misunderstood the question: Is
it possible to change the graphical appearance of the task such that it is
visually connected to the new DestinationConnection after making the
aforementioned changes?


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

After more Googling, I was able to locate one of Darren's old replies on
this topic:

"I would go with Vyas's suggestion, but FYI in cases where the layout no
longer matches the object model the simplest thing to do is to use the
object model to load and then save package. I recently had a package
that looked like lots of tasks executing serially due to workflow
constraints, but the step log said otherwise. By loading and saving
through the object model you loose all annotations and layout
information. This is normally considered a real pain, but in this case
it can be useful. When you open the new package again, since there is no
layout information, the designer does the equivalent of auto-layout.
This is obviously based on just the object model information, In our
case it showed the package did not have one of the previously shown
constraints, so we had two execution paths instead of the expected one."

My DTS package translates to over 60,000 lines of VB code and doesn't
compile without some minor tweaking -- I guess I have my work cut out for
me.


"Isak Dinesen" <no5pam (AT) hotmail (DOT) com> wrote

I have never really thought about this as I see a connection object as just
that. If I have Source connections i call them something like

SOURCE_system

or for destinations

DESTINATION_system

This way when i move between environments I change the properties of the
connection and that's that.

What is it that you want to represent?

If you start changing the names of things then Steps and Tasks quickly start
getting orphaned and nasty.



--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Isak Dinesen" <no5pam (AT) hotmail (DOT) com> wrote

I am loading a series of tables, most of which follow a simple relationship
heirarchy (mostly requiring they be loaded in parent-child order to maintain
RI). Within this structure there are a few very large tables that I'd like
to load in parallel with the other tables. While I may be able to load
multiple tables in parallel through one connection, it is my understanding
from available literature that it is better to run parallel loads to a
single database via a separate connection (to that db) for each parallel
task, as each connection in a DTS package can only handle one thread at a
time. So I now have a separate connection for these larger tables, and I can
redirect existing tasks at the new connection, but visually each task still
appears to use the old connection, incorrectly suggesting to someone
unfamiliar with this package that those tasks are being performed in serial.

I have similar issues reading text files: Currently when loading multiple
tables from one text file, I have multiple tasks all sharing the same source
connection (running in serial). If I want to load these in parallel, I'd
have to set up different source connections pointing to the same text file.
I can change this using Disconnected Edit, but I want the DTS package
designer to visually reflect what is going on under the covers.

Source:
http://support.microsoft.com/default...242391&sd=tech





"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

ok So you have n Source connections and n destination connections

You are correct that it is better to have multiple connections even if to
the same object on the database. Be aware though that it is pointless and
sometimes degrading to have Num Processes executing in parallel >= Num of
Source CPUs. You will see a great deal of context switches and it will hurt
you.

Why do you want to reuse these connections?

I would build the package up front named accordingley and the name wouldn't
change. I wouldn't reuse a connection like I think you mean either. I
would add as many connection objects as I thought I needed at the design of
the package and be done with it.

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Isak Dinesen" <no5pam (AT) hotmail (DOT) com> wrote

This is a dual CPU system, no problem there. Regarding your 'solution' to
simply start with a perfect design, well, that is simply unrealistic. :-)

In trying to compile the VB package exported from DTS, the Main method
appears to exceed the 64k procedure size limit -- It is in fact 214k...




"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Ok, found a decent workaround that allows you to update the diagram and keep
the existing transformations you may have created (you lose exsiting
precedence constraints, but those are easy to rebuild manually):

1. Once you have created the new connection, open the "Disconnected Edit"
dialog and note the ID of the new Connection under the Connections subtree,
and locate the Task under the Tasks subtree that you want to reference this
new (source or destination) connection. Change the SourceConnectionID or
DestinationConnectionID to reference the ID of the new connection. Close the
Disconnected Edit dialog. The change will NOT appear in the designer. You
can verify this worked by double-clicking the Transformation Task and
looking at the Source or Destination tab. You can also verify this worked by
simply running the package.

2. Now we want to update the designer. Without deleting the existing
(obsolete) task in the designer, create a new Transformation Task connecting
the old and new Connections using the usual point-and-click method.

3. Once the new task is created, open the "Disconnected Edit" dialog, and
take note of the new Task (N) that was created under the Tasks subtree, and
locate the old and new Steps under the Steps subtree.

For the old Step:
A. Rename the Step -- add "old" to the existing Name property.
B. Note the existing TaskName value (T)
C. Update the TaskName value to reference the new Task (N)

For the new Step:
D. Rename the Step -- change the Name property to match the name of the old
Step.
E. Update the TaskName value to reference the old Task (T).

Close the Disconnected Edit dialog, and delete the old task from the
designer. That's it. This will automatically delete the unneeded step (from
3.A. above) and task (N, above), leaving behind only the objects you need.

Feel free to add this tidbit to SQLDTS.com FAQ.


"Isak Dinesen" <no5pam (AT) hotmail (DOT) com> wrote