Samoin
asked on
Pass table to a Stored Procedure in SSIS
Hi Experts,
I have table that I'd like to pass to stored procedure (basically each row needs to go through the stored proc) and depending upon the result it either needs to be updated or inserted into a different table.
Can anybody please assist me in designing a SSIS package? I'm unable to find a task that would select each row and pass to a T SQL task that would call the procedure and then use a conditional task to pass the output based on the result of the stored proc. Any help will be greatly appreciated.
Regards,
Samoin
I have table that I'd like to pass to stored procedure (basically each row needs to go through the stored proc) and depending upon the result it either needs to be updated or inserted into a different table.
Can anybody please assist me in designing a SSIS package? I'm unable to find a task that would select each row and pass to a T SQL task that would call the procedure and then use a conditional task to pass the output based on the result of the stored proc. Any help will be greatly appreciated.
Regards,
Samoin
It sounds like what you are trying to do is possible within SSIS. If you were to use a conditional split (split based up the criteria of update/insert) and then use an OLE DB Command (Description: Executes a SQL Statement for each row in the dataset) in your control flow you should be able to acheive the desired result.
You can also create a new proc to do it.
inside the proc create a cursor whit the table, and in each loop do your stuff...
inside the proc create a cursor whit the table, and in each loop do your stuff...
DECLARE @ObjectName VARCHAR(100)
DECLARE @ReturnValue INT
DECLARE cur CURSOR FOR
SELECT
*
FROM
master.sys.objects -- but your table here
WHERE
TYPE = 'T'
OPEN cur
FETCH NEXT FROM cur INTO @ObjectName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'EXECUTE PROCEDURE @ObjectName = @ObjectName'
IF @ReturnValue = 0
BEGIN
PRINT 'INSERT INTO TABLE THE ROWS'
END
ELSE
BEGIN
PRINT 'UPDATE INTO TABLE THE ROWS'
END
FETCH NEXT FROM cur INTO @ObjectName
END
CLOSE cur
DEALLOCATE cur
ASKER
@EvilPostIt
how do we pass each row to the task that contains the stored proc?
how do we pass each row to the task that contains the stored proc?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The solution provided assisted me in the right direction
Just out of interest how do you do this in the end?