Folks,
Thanks for the various suggestions.
OK, you win, I have relented and imported the spreadsheet into a 'staging' table using a transform data task with a single N-to-N copy column transform. This basically copies the data in the same form as the spreadsheet. I have then added an SQL task to insert the required data correctly into the actual destination table. All works fine. The spreadsheet does not contain many records, which is just as well as I have to type them all in!
I have used this method before with another spreadsheet but I thought there may be a more elegant way of doing it in DTS. I like Dan McCue's idea of using a stored procedure to populate the table from the ActiveX script using DTSLookups() but couldn't find a reference in BOL. Quickest and easiest solution in the end ...
Mark
-----Original Message-----
From: Carter Phillip
[mailto:mssqldba-ezmlmshield-x47115586.[Email address protected]
Sent: 28 June 2005 16:19
To: LazyDBA Discussion
Subject: RE: DTS Transform Data Task
You shouldn't use these sort of transformations in the Transform Data
Task. It switches the DataPump to row-by-row processing and greatly
slows down the overall process.
What is the reasoning for avoiding the staging table? This is common
practice in many ETL processes.
Thanks
Phillip Carter
Ph: +61 3 9235 1691
-----Original Message-----
From: Mark Fleming
[mailto:mssqldba-ezmlmshield-x22679064.[Email address protected]
Sent: Tuesday, 28 June 2005 10:41 PM
To: LazyDBA Discussion
Subject: RE: DTS Transform Data Task
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
IMPORTANT DISCLAIMER - THIS MAY AFFECT YOUR LEGAL RIGHTS:
Because this document has been prepared without consideration of any
specific clients investment objectives, financial situation or needs,
a Bell Potter Securities Limited investment adviser should be
consulted before any investment decision is made.
While this document is based on the information from sources which
are considered reliable, Bell Potter Securities Limited, its directors,
employees and consultants do not represent, warrant or guarantee,
expressly or impliedly, that the information contained in this document
is complete or accurate.
Nor does Bell Potter Securities Limited accept any responsibility to
inform you of any matter that subsequently comes to its notice, which
may affect any of the information contained in this document.
This document is a private communication to clients and is not intended
for public circulation or for the use of any third party, without the
prior approval of Bell Potter Securities.
Disclosure of Interest: Bell Potter Securities Limited receives commission
from dealing in securities and its authorised representatives, or introducers
of business, may directly share in this commission. Bell Potter Securities
and its associates may hold shares in the companies recommended.
Bell Potter Securities Limited ABN 25 006 390 772 AFS Licence No. 243480
---------------------------------------------------------------------
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