What you really need to do is process the row more than one time (12 to
be precise). You need to look at a sample at:
http://www.sqldts.com/default.aspx?266
I have copied the example below. Basically in the ActiveX transform you
set the return result to DTSTransformStat_SkipFetch to process the row
again. You would need to set the result to DTSTransformStat_SkipFetch
OR DTSTransformStat_SkipInsert (using the OR to do both, not insert and
not get new results). if the column is not in the spreadsheet. After
twelve either set to DTSTransformStat_skipInsert if not present or
DTSTransformStat_OK if the twelfth column is there.
Good luck.
Processing The Same Row More Than Once
By Allan Mitchell
In his SQL Server Magazine article of August 2002 Itzik Ben-Gan shows us
how to use Transact SQL to normalise lab test results from a fictional
wine maker using either functions in SQL Server 2000 or Transact SQL
gymnastics in Version 7. In this article we're going to show you how to
do the exact same thing but using DTS. The added advantage of using DTS
is that it is Non-Connection specific.
The Situation
The scenario revovles around taking temperatures from sites. The way the
data is entered into the source system is highly denormalised and it's
our job using DTS to get it into our normalised structure. Listing 1
shows the source table structure with sample data and Listing 2 shows
the normalised target table design.
Listing 1 Source.
CREATE TABLE [TempSamplesBad] ([siteID] [int] IDENTITY (1, 1) NOT NULL
,[Readings] [varchar] (50))
INSERT TempSamplesBad(Readings) VALUES('1,9,-6,3,5,7')
INSERT TempSamplesBad(Readings) VALUES('34,39,10,13,51,27')
INSERT TempSamplesBad(Readings) VALUES('1,21,2,9,7,78')
Listing 2 Destination.
CREATE TABLE [TempSamplesGood] (
[siteID] [int] NOT NULL ,
[ReadingID] [int] NOT NULL ,
[Reading] [int] NULL ,
PRIMARY KEY CLUSTERED
(
[siteID],
[ReadingID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
The way we process the same row more than once in a DTS package is
through setting the return status of the transformation to
DTSTransformStat_SkipFetch. Listing 3 shows us how we do this in an
ActiveX script transformation in a data pump.
Listing 3 Code.
Option Explicit
Function Main
'This will form an array which we use to determine when to keep on the
same row or
'move to the next
DIM arr
'Populate the array
arr = SPLIT(DTSSource("Readings"),",")
'We create a Global variable to hold our position within the array and
for each processing
'of the row we need to check the value.
IF Cint(DTSGlobalVariables("ReadingID").Value) <= Ubound(arr) THEN
'Here is the insert of an element in the array based on the global
variable value.
DTSDestination("siteID") = DTSSource("siteID")
DTSDestination("ReadingID") =
Cint(DTSGlobalVariables("ReadingID").Value) + 1
DTSDestination("Reading") =
arr(Cint(DTSGlobalVariables("ReadingID").Value))
'This is the key to the whole thing. Setting the return value to
DTSTransformStat_SkipFetch
'forces DTS to reprocess the same row.
Main = DTSTransformStat_SkipFetch
'Increase the global variable value so we can pick up the next array
element the
'next time we process the row
DTSGlobalVariables("ReadingID").Value =
Cint(DTSGlobalVariables("ReadingID").Value) + 1
ELSE
'If our global variable = highest element in the array then we skip the
insert and move
'on. We set our return status to DTSTransformStat_SkipInsert otherwise
DTS would insert a
'NULL row in our table
DTSGlobalVariables("ReadingID").Value = 0
Main = DTSTransformStat_SkipInsert
END IF
End Function
We could extend this model further to processing a different column on
every iteration of a transformation so this would come in handy if you
had a table with fields like Product1, Product2, Product3.
Brian Freeman
Carnegie Technologies/Bluewave Computing
(770) 916-0595 ext. 415
www.carnegie.com
www.bluewave-computing.com
-----Original Message-----
From: drmccue
[mailto:mssqldba-ezmlmshield-x94378773.[Email address protected]
Sent: Tuesday, June 28, 2005 12:57 PM
To: LazyDBA Discussion
Subject: RE: DTS Transform Data Task
Mark and all,
The following link from MSDN gives details on using DTSLookups, this
link is page 1 of a 5 page section on
DTSLookups:
http://msdn.microsoft.com/library/default.asp?url=/library/en-
us/dtssql/dts_addf_misc_2dix.asp
Instead of using a SELECT or an UPDATE in the parameterized query as the
examples demonstrate, you can replace those with an EXEC <sp_name>
<parameter list> call. In the ActiveX script, I have in practice
"caught" the return value from the lookup query call and ignored it, but
since you can make the return value of your stored procedure what you
want it to be, I assume the sproc's return value will be passed through
to the ActiveX as the return value of your lookup query.
Dan McCue
Information Technology Services
Facilities and Services
University of Illinois at Urbana-Champaign
==============================================================
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
---------------------------------------------------------------------
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