Link to home
Start Free TrialLog in
Avatar of Samoin
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
Avatar of EvilPostIt
EvilPostIt
Flag of United Kingdom of Great Britain and Northern Ireland image

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.
Avatar of Cboudroz
Cboudroz

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...

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

Open in new window

Avatar of Samoin

ASKER

@EvilPostIt

how do we pass each row to the task that contains the stored proc?
ASKER CERTIFIED SOLUTION
Avatar of EvilPostIt
EvilPostIt
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Samoin

ASKER

The solution provided assisted me in the right direction
Just out of interest how do you do this in the end?