I am wondering if there is a way to automate the transformation task
so that the source table is mapped correctly to the destination Excel.
The reason for this is that I am changing the tables dynamically and
deleting and re-creating the excel every time but the data pump task
fails because the transformation task has the old mapping between the
source and destination, and if this is remapped manually using
the DTS designer, once I click on the Transformations tab I a message
box
appears with three options:
- Remove invalid transformations
- Change source/destination
- Remove all transformations and redo auto-mapping

By clicking the last option the DTS package will work fine. So is
there a way to use "Remove all transformations and redo auto-mapping"
programmatically or Properties set via global variable.

Thanks in advance!

Yes you need to drop all current transformations

tform = transformation object
dpump = Datapump task

for each tform in dpump.Transformations
dpump.Transformations.Remove tform.Name
next

You would then need to recreate transformations

here I am looping through the fields on the source and creating a new
transformation when i encounter one.

set tform = dpump.Transformations.New("DTSPump.DataPumpTransfo rmCopy")
tform.Name = "transformation___" &
arr(DTSGlobalVariables("gv_ElementLoop").Value)
tform.TransformFlags = 63

To the transformation you need to add a SourceColumn and a DestinationColumn

You then add the transformation object to the transformations collection.

A good way to see this would be to save out a package to VB and see how DTS
itself does it.


--
--

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

"Rahul" <pradeep_pip (AT) yahoo (DOT) com> wrote