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

Microsoft DevelopmentMicrosoft SQL Server

Last Comment

EvilPostIt

8/22/2022 - Mon

EvilPostIt

11/15/2010

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.

Cboudroz

11/15/2010

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