Help re-mapping source columns 1

[Sweenster]

Hi,

I am creating a DTS package that loops through and imports 2 types of text files (5 of Type 1 and then 5 of type 2) to the same staging table using a single data pump but hit problems when I reach the 2nd file type as 4 of the source column names are not the same as the first file type.

I am able to define global variables and set the source filename OK but the actual transformation fails with an error like
'Source Column "Invoice Number" can not be found'

Here is a snippet of the ActiveX transformation that I am using...

'These Column names are same in both file types
DTSDestination("ProductCode") = DTSSource("ProductCode")
DTSDestination("OrderUnit") = DTSSource("OrderUnit")

'These Column names differ
IF DTSGlobalVariables("LoopCounter").Value  < 6 THEN 'e.g 1st file type
DTSDestination("InvoiceNumber") = DTSSource("InvoiceNumber")
Etc…
Etc..
ELSE 'e.g 2nd file type

DTSDestination("InvoiceNumber") = DTSSource("CreditNumber")
Etc…
Etc..
END IF

I thought this would be enough but do I some how have completely re-map source columns as well?

I guess worst case I could duplicate all the steps and run the 2 completely separately but wanted to try an reuse steps if possible.

Any Help would be much appreciated..

 

[gradley]

Are you pulling in column headings as the first row of the Source Table. If so, I would try ingoring the first row when importing the file. This should bring in all columns using the default column names ("col001"), ("col002")etc...

Unfurtunately the transformation task does map column to column so if you assign a name to the Source column, it can't be overridden.

 

[Sweenster]

Thats does the trick. Thanks
A touch frustrated wiy myself as it was staring me in the face all the time!

 

[gradley]

Glad to help!

I know the feeling... Sometimes the most obvious things are the last ones I try