I prefer to use SQL statements on the data after copying in the columns
needed.
>>>>>>>>>>>
Yes, that would work but I think that would mean importing the spreadsheet into a separate table and then using SQL to transfer it to the actual destination table, which I was hoping to avoid. I suppose the overall question is, how do I transform the data from a single row of the spreadsheet into a number of rows within a table?
Mark
-----Original Message-----
From: Mark Fleming
[Email address protected]
Sent: Tuesday, June 28, 2005 7:47 AM
To: LazyDBA Discussion
Subject: DTS Transform Data Task
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.
______________________________________________________________________
________________________________________________________________________
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
________________________________________________________________________
This e-mail has been scanned for all viruses by Star. The
service is powered by MessageLabs. For more information on a proactive
anti-virus service working around the clock, around the globe, visit:
http://www.star.net.uk
________________________________________________________________________
_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