I have an Excel to Sql Server data pump transform that does a lookup on
table A on the sql server. This lookup returns a single row which I then
combine with some of the Excel columns and then add a single row to table B.
That works fine.

I now need to do the same thing but this time the lookup may return MULTIPLE
rows. I want to do the following: for each row from table A, combine the
row with the Excel data and write one row into table B.

I've stared at this for hours and am stumped. How can I implement this one
to many pump?

Gary Shell

You can process the source row more than once, so if you check for how many
records returned from teh lookup, if more than one, use
DTSTransformStat_SkipFetch to re-process the row. You'll also need a counter
for teh current row compared to the count of rows from teh lookup to
maintain "state" between iterations of the transformation script. Lookup
DTSTransformStat_SkipFetch in Books Online. Here is an
example as well-

Processing The Same Row More Than Once
(http://www.sqldts.com/default.aspx?266)


--
Darren Green
http://www.sqldts.com

"Gary Shell" <gshell (AT) fuse (DOT) net> wrote

I started playing with your suggestion RE: DTSTransformStat_SkipFetch and
Insert and understand how it applies, but now I am baffled as to how to
first know if the DTSLookups("myLookup").Execute did indeed return multiple
rows. And then if it did how to iterate through the rows.

Unable to seem to actually do one lookup and iterate through the "record
set" it would return, I'm trying this:

Function Main()
dim arrayReturn
arrayReturn= DTSLookups("myLookup").Execute(DTSSource("Target") .value)

IF Cint(DTSGlobalVariables("count").Value) <= Ubound(arrayReturn) THEN
DTSDestination("BPID")
=arrayReturn(DTSGlobalVariables("count").Value)
DTSGlobalVariables("count").Value =
DTSGlobalVariables("count").Value +1
Main = DTSTransformStat_SkipFetch
ELSE
DTSGlobalVariables("count").Value=0
Main = DTSTransformStat_Skipinsert
END IF
End Function

Am I on the right track that the lookup will return an array like object?
This looks sort of inefficient, though because I think the actual lookup
query is going to be re-executed "n" times where "n" is the count of rows it
will return. But hey DTS is better than cutting and pasting all that data!
I'll take inefficient if I can get it to work! <big ol' grin>

Gary

"Darren Green" <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote

Gary, see inline-

In message <u8PzykdfEHA.3964 (AT) TK2MSFTNGP12 (DOT) phx.gbl>, Gary Shell
<gshell (AT) fuse (DOT) net> writes
make one row, the top row, available. You can use the LastRowCount
property of the lookup to see how many rows where really returned, even
if you cannot get them.

Managing Zero or Multiple Result Rows in Lookup Queries
(http://msdn.microsoft.com/library/de.../en-us/dtssql/
dts_addf_misc_1kc3.asp?frame=true)


Lookups have a MaxCacheRows property, I forget what it is called in the
UI, but as the name suggests it will cache a number of rows to speed
things up. Set it as appropriate.

Cheers

Darren


--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

OUCH! Now I am not sure how I will approach this. I need to know the
contents of the rows that WOULD be returned if a lookup could do such a
thing. Any suggestions as to how to implement what I need? To reiterate: For
each row from an excel spreadsheet I need to use a column from the row as a
primary key lookup on a Sql Server table returning a recordset of a single
column. For each occurrence from that "lookup" I want to take a column from
the Excel and the returned column from the query and write these values to a
third table. Lookup sure looked like the answer.

Gary

"Darren Green" <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote

Well I thought I'd figured out a way to do this. What I thought might work
was to create a transform that used the SQL table as a source and the Excel
table as a lookup. (That way the "many" table in this one-to-many
relationship would be the source and the "one" table would be the lookup
table. But I was thwarted in that effort by the DTS designer as it would
not let me select the SQL Server as both a source and destination for the
transform. Arghhhhh... back to the drawing board once again.

Gary


"Gary Shell" <gshell (AT) fuse (DOT) net> wrote

Can you just not add a second connection ?


Perhaps an alternative would be to just import the Excel data in SQL Server
and then perform the transformation all in T-SQL. Sometimes this is just
easier all round.


--
Darren Green
http://www.sqldts.com


"Gary Shell" <gshell (AT) fuse (DOT) net> wrote

Hmmm, I didn't realize the DTS Designer would even let me add a second
connection to the same data source. So, I guess I could have done that.
What I did do was create a temp table in a connection to tempdb, write my
excel data there and then in a second xform step, read from the temp table
and write to my real database.

Thanks again!
Gary

"Darren Green" <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote