Data Divas,

I am using a DTS transform data task to read text strings from a spreadsheet and the insert them into a table.

There are 12 named columns in the spreadsheet which become the source record names. Each column will have at least 1 string but may have many.

Col1 Col2 .... Col12
text text text
text text
text

The destination is a table with 3 columns. I only want to transfer the text from each named source column to one column in the destination table, the other columns are fixed strings. I am using an ActiveX transform of the form:-

'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************

' Copy each source column to the destination column
Function Main()

IF TRIM(DTSSource("Col1")) <> "" THEN
DTSDestination("Col1") = "Message 1"
DTSDestination("Col2") = DTSSource("Col1")
DTSDestination("Col3") = "Message 2"
END IF

' and so on for each source column ...

IF TRIM(DTSSource("Col12")) <> "" THEN
DTSDestination("Col1") = "Message 1"
DTSDestination("Col2") = DTSSource("Col12")
DTSDestination("Col3") = "Message 2"
END IF

Main = DTSTransformStat_OK

End Function

However, this doesn't work as each destination column is successively overwritten in each step, so it is only the last (12th) source column that is actually written to the destination table. Obviously, I need to add some sort of intermediate 'DTSTransformXXX' step after each of the 12 source column operation. Any ideas???

Thanks
Mark


_COMPANY CONFIDENTIAL UNLESS STATED OTHERWISE.

Any opinions expressed in this message are those of the sender only and do not necessarily represent the views or opinions of THE PROMISE SOLUTIONS GROUP or any associated companies.
Registered Office: Promise Solutions Ltd Promise House, Stafford Road, Wolverhampton, West Midlands WV10 6AD.
Company Registration Number: 04822774

The information contained in this message and any attachments is intended solely for the use of the individual or entity to whom it is addressed. It may contain privileged and confidential information and exempt from disclosure under applicable law.
If you are not the intended recipient you must not disclose, copy, distribute, retain or take action reliant on any part of it. If you have received the e-mail in error please notify immediately to [Email address protected]

Whilst we have checked this e-mail and any attachments for viruses, we do not warrant that they are virus-free. You therefore take full responsibility for virus checking.


Promise Finance Limited is authorised and regulated by the Financial Services Authority in respect of mortgage and general insurance activities only.

This e-mail has been scanned for all viruses by Star. The service is powered by MessageLabs.
______________________________________________________________________



________________________________________________________________________

MS Sql Server LazyDBA home page