Solved

Pass table to a Stored Procedure in SSIS

Posted on 2010-11-14
6
316 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
0
Comment
Question by:Samoin
  • 3
  • 2
6 Comments
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34134850
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
 
LVL 7

Expert Comment

by:Cboudroz
ID: 34136411
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
 
LVL 1

Author Comment

by:Samoin
ID: 34138674
@EvilPostIt

how do we pass each row to the task that contains the stored proc?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 16

Accepted Solution

by:
EvilPostIt earned 500 total points
ID: 34143092
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
 
LVL 1

Author Closing Comment

by:Samoin
ID: 34164085
The solution provided assisted me in the right direction
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34164123
Just out of interest how do you do this in the end?
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Help with Merge Join and Conditional Split in SSIS 6 77
SQL Backup Question 2 29
Query for timesheet application 3 16
sql 2016 data tools breakdown.. 1 11
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question