Transform Data Tasks

Question

Transform Data Tasks

wbeetge

SSChasing Mays

Points: 636
More actions
September 29, 2002 at 7:56 pm

#105145

How can I copy a Transform Data Task (Or the mappings made in one) to use for another Transform Data Task ? I have a package that does 9 Transform Tasks from different source files, but they all go to the same Destination (There are a few differences between each of them -- Debtors file looks exactly like the Customers file, but the different lookups are required for each individual file etc). So I need to copy the task and change the required mappings according to the sources - I have 67 mappings to make in each of the 9 tasks and a copy will speed up the process tremendously.
Thanks in advance.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

mhweiss SSCarpal Tunnel Points: 4284 More actions · Answer 1

Right click on the task, choose copy, then right click in the dts designer window and choose paste...or you could save it to a vb file then edit that file and find your task in the code and copy and paste it as many times as necessary and edit each one...just a couple of ideas...

hth,

Michael

Michael Weiss

wbeetge SSChasing Mays Points: 636 More actions · Answer 2

Hi Michael.

Thanks for your reply.

The Transformation Task object does not allow me to copy & paste it.

If I use the Save To option and save it to a .bas file, will the copied Transformation task object be displayed in the DTS Designer from the copied code in the .bas file?

mhweiss SSCarpal Tunnel Points: 4284 More actions · Answer 3

Darn! That is right...I tried copying and pasting transform data task objects before and couldn't do it...sorry about the bum steer there...to answer your question, yes you should see the copied and modified transform data tasks in the designer window...you will have to create an object variable in your bas file for each transform data task you are adding...it is definitely uglier than being able to copy and paste the task itself in the dts designer window...and after looking at the code generated by saving the package to a bas file, it could be error prone as well...before I went to that trouble, I think I would see if anyone else has any ideas of how to copy and paste a transform data object...could be we are both missing something simple here...

hth,

Michael

Michael Weiss

mhweiss SSCarpal Tunnel Points: 4284 More actions · Answer 4

Darn! That is right...I tried copying and pasting transform data task objects before and couldn't do it...sorry about the bum steer there...to answer your question, yes you should see the copied and modified transform data tasks in the designer window...you will have to create an object variable in your bas file for each transform data task you are adding...it is definitely uglier than being able to copy and paste the task itself in the dts designer window...and after looking at the code generated by saving the package to a bas file, it could be error prone as well...before I went to that trouble, I think I would see if anyone else on this list or on microsoft.public.sqlserver.dts (search google for dts transform data object) have any ideas of how to copy and paste a transform data object...could be we are both missing something simple here...

hth,

Michael

Michael Weiss

wbeetge SSChasing Mays Points: 636 More actions · Answer 5

No worries.

I tried to save it to .bas file, but noe I cant load it from the .bas again. We recently moved to 2000 from SQL7 and I cannot even save it as a .dts file from the menu. I quess there are a few radical changes (to administrative rights) to certain funtions that SQL allowed.

Thanks anyway for your reply.

mhweiss SSCarpal Tunnel Points: 4284 More actions · Answer 6

try submitting your question on microsoft.public.sqlserver.dts...could be someone there will have an answer...let us know if you find a solution, okay?

thanks,

Michael

Michael Weiss

jpfortney Newbie Points: 1 More actions · Answer 7

Hi,

How about creating 9 seperate packages, each a copy of the original? Then you could tweak each package according to your source files and create a job to execute each package in order.

wbeetge SSChasing Mays Points: 636 More actions · Answer 8

Hi Guys. Thanks for everybody's inputs.

So far, there is no answer yet for me.

As for creating each step as a DTS and executeing each from another scheduler ...

Doesnt that in essence totally defeat the purpose ?

I really think DTS is great but I am totally disappointed and unhappy about it's short-commings.

Is'nt there somebody that can create a new transformation component that will do the same, perhaps only better being able to be copied and it's tasks re-ordered ??

If this does not exists anywhere, well, perhaps I and/or we should start looking at putting our heads together and come up with a winning solution that would actually be marketable.

What u think ?