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
LVL 1
SamoinAsked:
Who is Participating?
 
EvilPostItConnect With a Mentor Commented:
Use the OLE DB Command component in your dataflow. Although you should look into the other approach i suggested as this would work in parallel and run faster.

Please see below for more infromation on the OLE DB Command component.

http://technet.microsoft.com/en-us/library/ms141138.aspx
0
 
EvilPostItCommented:
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.
0
 
CboudrozCommented:
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

0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
SamoinAuthor Commented:
@EvilPostIt

how do we pass each row to the task that contains the stored proc?
0
 
SamoinAuthor Commented:
The solution provided assisted me in the right direction
0
 
EvilPostItCommented:
Just out of interest how do you do this in the end?
0
All Courses

From novice to tech pro — start learning today.