Implementing SCD type 1 & 2 using DTS

[jaganmbv]

Hi!

How to Implement scd type 1 and 2 functionality using DTS.
Implemented the functionality using T-SQLS, but want to make use of DTS. Can we do this using DTS without using T-SQLs.

Thanks

[MAK]

sure.

Copy your SCD's T-SQL code on SQL query task of DTS.

[jaganmbv]

Thanks for ur response.
We have implemented copying the T-SQL code into the Execute SQL Task, but facing problem with a cursor definition. I want to perform row by row, Execute SQL Task supports only bulk operations. Update me if i am wrong.
thanks

[MAK]

1. I created the following code on the Execute SQL task. it works perfectly fine. (i am inserting and updating a table called aut)

--drop table aut
--create table aut (name char(100))

SET NOCOUNT ON

set quoted_identifier off

DECLARE @au_id varchar(11), @au_fname varchar(20), @au_lname varchar(40),
@message varchar(80), @title varchar(80)

DECLARE authors_cursor CURSOR FOR
SELECT au_id, au_fname, au_lname FROM authors WHERE state in ("UT","CA"
,"N")
ORDER BY au_id

OPEN authors_cursor

FETCH NEXT FROM authors_cursor
INTO @au_id, @au_fname, @au_lname

WHILE @@FETCH_STATUS = 0
BEGIN
insert into aut select @au_fname
-- Get the next author.
FETCH NEXT FROM authors_cursor
INTO @au_id, @au_fname, @au_lname
END

CLOSE authors_cursor
DEALLOCATE authors_cursor
GO



SET NOCOUNT ON

set quoted_identifier off

DECLARE @au_id varchar(11), @au_fname varchar(20), @au_lname varchar(40),
@message varchar(80), @title varchar(80)

DECLARE authors_cursor CURSOR FOR
SELECT au_id, au_fname, au_lname FROM authors WHERE state in ("UT","CA"
,"N")
ORDER BY au_id

OPEN authors_cursor

FETCH NEXT FROM authors_cursor
INTO @au_id, @au_fname, @au_lname

WHILE @@FETCH_STATUS = 0
BEGIN
update aut set name = "mr." + @au_fname where name = @au_fname
-- Get the next author.
FETCH NEXT FROM authors_cursor
INTO @au_id, @au_fname, @au_lname
END

CLOSE authors_cursor
DEALLOCATE authors_cursor
GO

--select * from aut

2. Put that code in to a procedure and execute the procedure in the DTS SQL task.

[MAK]

if you still have trouble upload the code, let me take alook.

[skhanal]

Cursor processing should not be a problem as experimented by MAK.

Only restriction may be

You can't refer to a cursor in one execute sql task in another execute sql task.