Solved

Pass table to a Stored Procedure in SSIS

Posted on 2010-11-14
6
308 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
Comment Utility
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
Comment Utility
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
Comment Utility
@EvilPostIt

how do we pass each row to the task that contains the stored proc?
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 16

Accepted Solution

by:
EvilPostIt earned 500 total points
Comment Utility
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
Comment Utility
The solution provided assisted me in the right direction
0
 
LVL 16

Expert Comment

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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MS SQL server Varchar and nvarchar, GMT_DATE 23 49
Updating variable table 9 17
Numeric sequence in SQL 14 36
Azure SQL DB? 3 14
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

744 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now