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


MS Sql Server LazyDBA home page