sql server dts: We had a DTS package written by presious staff. which import data from
MS Access database to SQL Server 2000. So basically what;s he'd done was
like direct mapping from Access Columen to SQL Server column. and we
have a new request to somehow do some kind of upfront processing to the
data.

..e.g in one column called col1 , datatype is text, value is S.99.10 , we
need to split this into 3 colums, so i wrote a scalar fuction to do just
that. So is it possible for me to run this fuction when the DTS package
is run, and if so.. how?


TIA

----

FUNCTION fn_get_SB1
(@sb varchar(6))
RETURNS int
AS
BEGIN
declare @sb1 as int
declare @sb1_word as varchar(2)

select @sb1_word = SUBSTRING(@sb, 2, 2)
select @sb1 = null

if ( charindex('.', @sb1_word) = 0 and charindex(',', @sb1_word) = 0 )
begin
if ( isnumeric(@sb1_word) = 1) begin
select @sb1 = cast( @sb1_word as int)
end
end

RETURN @sb1

END

/* */
FUNCTION fn_get_SB2
(@sb varchar(6))
RETURNS int
AS
BEGIN
declare @sb2 as int
declare @sb2_word as varchar(1)

select @sb2_word = SUBSTRING(@sb, 5, 1)
set @sb2 = null

if ( charindex('.', @sb2_word) = 0 and charindex(',', @sb2_word) = 0 )
begin
if (isnumeric(@sb2_word) = 1) begin
select @sb2 = cast( @sb2_word as int)
end
end

RETURN @sb2

In message <O1F$WHsVFHA.1796@TK2MSFTNGP15.phx.gbl>, erymuzuan
<erymuzuan@yahoo.com> writes
[quoted text, click to view]

Using the DataPump task you do not have statement level control over the
actual insert into SQL. You could look at the Data Driven Query task as
for this you actually write the INSERT statement, so you can call your
function in that statement.

The alternative way would be to use an ActiveX Script Transform, and
implement your function in VBS, calling it to assign the values to the
destination columns.

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

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