-
Implementing SCD type 1 & 2 using DTS
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
-
sure.
Copy your SCD's T-SQL code on SQL query task of DTS.
-
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
-
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.
-
if you still have trouble upload the code, let me take alook.
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|