Using Stored Procedure As OLE DB Source In BIDS

Question

I am testing SSIS packages and stored procedures as I am only a beginner. What I need to do is use a stored procedure i have installed on a source database to return a dataset and then I need an ssis package to use the dataset returned by the stored procedure as an OLE DB source to populate a second, destination table.

Basically I have 2 tables: - test_source - test_destination

test_source has one table titled Companies with 3 rows - (ID, Name, Established). I have set up a stored procedure(spGetCompanies) to return all records in that table into a result set.

Then in BIDS I have an SSIS package which takes data from the test_source table (In this case, the spGetCompanies SP) and inserts it into the test_destination table.

Can anyone help by telling me how I can use the spGetCompanies Stored procedure as the OLE DB source?

Cian

James Wiseman · Accepted Answer · 2010-12-08T13:11:02.650

7

Assuminng you've set up your data conenction, do the following:

Double-click your OLEB Source
Select your connection manager item
Select Data Access Mode as 'SQL Command'
Enter your SQL Command to execute your SP (e.g. exec usp_myproc 1234).
Click 'Preview'. You dataset should be returned.
Click 'Ok'

Done

edited Dec 08 '10 at 13:11

answered Dec 08 '10 at 13:04

James Wiseman

29,946
17
95
158

So if I had a stored procedure title spGetCompanies would I simply put the query to: "EXEC spGetCompanies" ? – cian1991ferrari Dec 08 '10 at 13:56
Yes. The 'Preview' option is quite useful for testing this. – James Wiseman Dec 08 '10 at 13:59
Thanks very very veru much James! I tried it there and it seems to work just as I need! :D – cian1991ferrari Dec 08 '10 at 14:02
No problems. Glad to be of help – James Wiseman Dec 08 '10 at 14:03

score 4 · Answer 2 · edited Oct 10 '12 at 12:58

4

You can try set FMTONLY off; before exec sp. Worked for me :)

edited Oct 10 '12 at 12:58

Mike Aski

9,180
4
46
63

answered Oct 10 '12 at 12:52

jambis

122
2
10

score 3 · Answer 3 · edited Oct 27 '17 at 19:00

It is not always possible to use a stored proc within an OLEDB data source, as Jamie Thomson explains here: https://web.archive.org/web/20141215063233/http://consultingblogs.emc.com/jamiethomson/archive/2005/12/09/2480.aspx

Basically, since sprocs have no associated metadata, SSIS tries to guess the metadata by looking in the sproc and grabbing the first select statement. This may be inaccurate, or impossible (in the case of a SQL-CLR proc for example).

Answer should read: if you are lucky, after you press Preview you can press ok.

Using Stored Procedure As OLE DB Source In BIDS

3 Answers3

Linked