xls DTS filters

[jcisco2]

I have a xls file that i wish to import into an existing table. the xls file holds the cols OF: Item Lot tagNumber, and Qty. what i would like to do is import this file via a DTS package. but i have to run each one of these fields in this file through a data scrubber first (which is a sproc i created.)

my question is this. how can i import the data from the xls file and run each row through my sproc and put the resulting set in the correct table? cause my sproc can only take one field at a time?

example of what i would like to do

exec MySPROC (select Item, Lot, tagNumber, Qty from my xlsFile)

 

[philhege]

Upload the data in its rough form to a work table. Spin through the rows and insert the scrubbed data in your target table.

SELECT *,0 as processed into worktable from myuploadtable
while (select count(*) from worktable where processed = 0)
>0
begin
select top 1 @item = item, @lot = lot, @tagnumber = tagnumber, @qty = qty from worktable
exec MySPROC(@item,@Lot,@tagnumber,@qty)
update worktable set processed = 1 where item = @item -- you MUST use some identifying key value for this
end

Better yet, make your stored procedure a table-valued function, and you can apply it in one step.

HTH,

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Eschewing obfuscation diurnally.