Mark,
One approach I have used to write multiple rows of data for a
single input row in a DTS transform task (this will work with
a Data Driven Query too) is to build a DTSLookups() call that
executes a stored procedure. Not the most efficient
approach, but it does let DTS handle the row-by-row
processing, and it lets you control what happens to the data
in that row after you read it.
With your scenario, I would be tempted to do the
transformation in two stages: stage 1, write the contents of
the spreadsheet to a staging table; stage 2, for each of the
12 columns, read column x from each row and transform it.
Some might suggest just doing 12 separate transforms from the
spreadsheet -- I think the single read of the entire
spreadsheet into a table would be faster than 12 single-
column reads from that spreadsheet. I'm sure there are some
other DTSers out here that can comment on the relative
efficiency of these approaches.
Let me know if this approach is useful to you.
Regards,
Dan McCue
Information Technology Services
Facilities & Services
University of Illinois at Urbana-Champaign
==========================================================
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.
______________________________________________________________
________
______________________________________________________________
__________
--------------------------------------------------------------
-------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
MS Sql Server LazyDBA home page