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

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