All geek questions in one place

Using a stored procedure as an OLE DB source in BIDS

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

I basically have 2 tables: - test_source - test_destination

test_source has one table called "Companies with Three Rows" (ID, Name, Foundlished). I created a stored procedure (spGetCompanies) to return all the records in this table to the result set.

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

Can someone help by letting me know how I can use the spGetCompanies stored procedure as an OLE DB source?

Kian

+3
source share
3 answers

If you configure your data transfer, follow these steps:

  • Double-click OLEB source
  • Select the connection manager item.
  • Select data access mode as "SQL command"
  • Enter the SQL command to execute your SP (for example, exec usp_myproc 1234).
  • Click "Preview." You need to return the data.
  • Click "Ok"

Done

+5

set FMTONLY off; exec sp. :)

+3

OLEDB, : https://web.archive.org/web/20141215063233/http://consultingblogs.emc.com/jamiethomson/archive/2005/12/09/2480.aspx p >

, sprocs , SSIS , sproc select. (, SQL-CLR-).

: , ok.

+1

Source: https://habr.com/ru/post/1779162/

More articles:


All Articles