We help IT Professionals succeed at work.

Pass table to a Stored Procedure in SSIS

356 Views
Last Modified: 2012-05-10
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
Comment
Watch Question

EvilPostItIT Director
CERTIFIED EXPERT

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

Commented:
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

Author

Commented:
@EvilPostIt

how do we pass each row to the task that contains the stored proc?
IT Director
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
The solution provided assisted me in the right direction
EvilPostItIT Director
CERTIFIED EXPERT

Commented:
Just out of interest how do you do this in the end?
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.