We are migrating from Oracle to SQL Server. I'm attempting to create the
DTS packages we'll need to migrate the data. We will have already created
the tables on SQL Server so we'll just be copying data.

Our plan is to copy the data twice. Once for testing and to use as we
convert our apps, and a 2nd time as we move into production.

Our first attempt at a DTS package includes one SQL task to truncate all the
tables we're copying data into, one Oracle data provider and one SQL Server
provider, then 60 Transform Data tasks between the two. We gradually built
the GUI up to about 40 of the tables and saved the package at the end of the
day. When the DTS package was opened the next day, only 11 of the
transformation steps remained.

Is there a limit to the number of transformation steps allowed per
connection? Is there some other error?

We're not a VB shop so I have no desire to save the steps to VB. What
should we be doing?

--
Jeffrey R. Price
Database Manager
Computing & Communication Services
Max M. Fisher College of Business
The Ohio State University
320F Mason Hall
250 W. Woodruff Avenue
Columbus, OH 43210-1309

In one of my packages I move data to and from Oracle. There are 2 distinct
Oracle connections and 2 distinct SQL Server connections. There are 2500
DataPump tasks so I do not think that this is a problem. A very Quick way
to test would be that if you only have 11 remaining then it would seem that
12 was the breaking point and DTS left the ones it permitted. Add another
Data Pump task | save | Close | open.

Still there ?

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
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


"Jeff Price" <price.9 (AT) osu (DOT) edu> wrote

I'm certain there is no limit. I have seen 100's of individual
tranformations in packages before. Get's quite ugly to look at - but they
work.

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

In message <#qRUA2T2DHA.1264 (AT) TK2MSFTNGP11 (DOT) phx.gbl>, Jeff Price
<price.9 (AT) osu (DOT) edu> writes
Are you sure this is not just a GUI issue? If you only had one icon for
each connection and had 40 black lines then it may just have got lost
behind each other. Try using auto-layout to re-generate the layout
information. To force the layout info to be re-created use the object
model to load and save the package. Then re-open it in the designer.


For clarity you can create multiple icons that all use teh same
connection, by selecting Existing Connection when you add a new
connection onto teh design sheet. For performance however it is better
to use individual connections, not just icons assuming you wish to run
task in parallel.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Thanks to all!

It appears to be an operator error....not sure what I saved, or where, but I
can add all I need although I think I'll continue to save with a new file
name [name (X)] every 5-10 tables.

"Darren Green" <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote