BEST SSIS equivalent for DTS2000 CURSORS

Hi everyone,

Right now we are trying to replace all stored procedures by SSIS logic.

One of the stored procedures does the following,

1. For Every record in PSTREELEAF find the Hierarchy FROM
PSTREENODE and store them in local valriables.

First the stored procedure pulls all the data from the PSTREELEAF table based on join condition into local variables using cursor. Then for every record in the cursor it finds the corresponding records from PSTREENODE table. This is just a part of the stored procedure.

can anyone tell me how can i do this in SSIS? and also tell me which task would be a perfect replacement for CURSORS in SSIS?

Thanks,

Praveen

Hi,
Do not use cursors if you can avoid it and do not attempt to replicate cursors.
Use Set based logic or derived tables or subqueries or Update statements or affect values to columns using subqueries as columns (top 1 or distinct).

Please post some more explicit examples and we may suggest another approach.

you can create loops in SSSI but the point is to make sure you really need this in the first place.

Philippe|||

Hi ,

thanks Phillippe.... here is the code which i need to change it to SSIS logic.

DECLARE NodeCursor CURSOR LOCAL FOR SELECT DISTINCT Tree_node_num,Range_from,Range_to,EFFDT
FROM FINANCEODS.DBO.PSTREELEAF
WHERE TREE_NAME='ACCTROLLUP' AND EffDt>=@.effDt AND INACTIVE_DATE IS NULL

OPEN NodeCursor

/*********************************************************************************
fetch nodes one by one and find out the hierarchy of the nodes
*********************************************************************************/

FETCH NEXT FROM NodeCursor
INTO @.Tree_node_num,@.LeafFrom,@.LeafTo,@.effDt
SET @.Parent_node_num=@.Tree_node_num

IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.p_errNum = 50505
SET @.p_errMsg = 'SPUpdateAccounts: Error Fetching Node Cursor.'
RETURN Exit Stored Procedure
END
WHILE @.@.FETCH_STATUS = 0
BEGIN
/*****************************************************
While Loop to find out all the Hierarchy information

******************************************************/
WHILE (@.Parent_node_num)<>0

BEGIN

/************************************************************************************
Find the Parent node and its level(hierarchy)
*************************************************************************************/
SELECT @.Parent_node_num=Parent_node_num, @.Tree_node_num=Tree_node_num,@.TreeLevel=TREE_LEVEL_NUM ,@.TreeNode=ISNULL(B.DESCR,A.TREE_NODE)
FROM FINANCEODS.DBO.PSTREENODE A
LEFT JOIN ( SELECT MAX(EFFDT) AS EFFDT,DESCR,TREE_NODE,INACTIVE_DATE FROM FINANCEODS.DBO.PS_TREE_NODE_TBL WHERE EFFDT<=@.EFFDT GROUP BY DESCR,TREE_NODE,INACTIVE_DATE ) B ON A.TREE_NODE=B.TREE_NODE
WHERE A.Tree_node_num=@.Tree_node_num
AND A.TREE_NAME='ACCTROLLUP' AND A.EFFDT=@.EFFDT AND A.INACTIVE_DATE IS NULL AND B.INACTIVE_DATE IS NULL
/************************************************************************************
Store the Hierarchy level information in local variables.
*************************************************************************************/
SELECT @.Source= (CASE @.TreeLevel WHEN 6 THEN @.TreeNode ELSE @.Source END),
@.Type= (CASE @.TreeLevel WHEN 5 THEN @.TreeNode ELSE @.Type END),
@.Element= (CASE @.TreeLevel WHEN 4 THEN @.TreeNode ELSE @.Element END),
@.Component= (CASE @.TreeLevel WHEN 3 THEN @.TreeNode ELSE @.Component END),
@.FinStatement= (CASE @.TreeLevel WHEN 2 THEN @.TreeNode ELSE @.FinStatement END)
SET @.Tree_node_num=@.Parent_node_num
END