Hi All,

I'm trying to pump a CSV file with 33 columns into a table.I read that using
ordinals instead of field names helps boost the performance.I tried to use
odinals then I realized that DTSDestination(1) reffers to my last column in
the row instead of the first column.I looked at Destination Columns in
datapump and I notices the order of my columns are upside down.I tried to
add the Destination columns upside dwn to get the correct result( 1 reffers
to first column ) but DTS still sort the Destination columns upside
down.here are my question


1)Why really we should use ordinal instead of field names as using ordinals
really hurt the code readability?

2)what is the workaround to the above problem?


Thanks

I don't know what has been going on for you, but this is not quite right, or
at least not what I'd expect unless you have been manually tweaking
transforms in a big way.

The columns use a 1 based index, and are in order, left to right when viewed
in conventional data grid format. DTSDestination(1) is the first column in
the destination table, so for a three column table I would use -

DTSDestination(1) = DTSSource(1)
DTSDestination(2) = DTSSource(2)
DTSDestination(3) = DTSSource(3)

This does assume that you have added the columns to the transform in the
default manner (which is automatically) as opposed to manually adjusting the
source and destination columns for the transformation itself, as opposed to
the transform task. To see what I mean, open a DataPump, highlight a
transform, and click Edit. The Source Columns and Destination Columns tabs
are available for editing. I would expect the Available columns and Selected
columns to be in the same order.


Ordinals are faster than names since a name has to be looked up first to get
the ordinal which is then used. That is it, if you really want max
performance then use ordinals, but the trade-off is code is harder to read,
that's your choice. It is also faster to use a single transform, rather than
several. This is true for both ActX and direct Copy transforms.

To tackle code readability, one strategy is to define constants, e.g.

Const ColumnOne = 1
Const ColumnMyTextData = 2
Const DateUpdated = 3

DTSDestination(ColumnOne ) = DTSSource(ColumnOne )
DTSDestination(ColumnMyTextData) = DTSSource(ColumnMyTextData)
DTSDestination(DateUpdated) = DTSSource(DateUpdated)


--
Darren Green
http://www.sqldts.com
http://www.sqlis.com



"RayAll" <RayAll (AT) microsft (DOT) com> wrote

Thanks Darren.Really helpful.

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