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

Answer 1

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

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

Done

Answer 2

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.

Answer 3

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