Please tell some tactics to use Stored Procedures in OLEDB Source task.

Asked By jacob.daniel_h 10 points N/A Posted on - 06/27/2011

Hi mates,

In OLEDB Source task, I have tried to use the Stored Procedures in the SQL source command. But the task is not producing the expected results in the SQL table. I have tried the same SP in SSMS. It was producing the results as expected. I am not aware of using the SP in OLEDB source command.

There must be some tactic to do this. Can anyone give some ideas as to how to handle the Stored Procedure in the OLEDB Source task?

Thank you.

• Status: Open
• Question Views: 767
• Answer Count: 1
• Vote Up 1 Vote Down

• Answer Accepted: No
• Question Category: MS SQL Server

Answered By david.masony 0 points N/A #79953

Please tell some tactics to use Stored Procedures in OLEDB Source task.

Using Stored Procedures in OLEDB source is an issue for many developers too. Here I have given some tactics to resolve the issue. You can try this.

1. In general, while using Store Procedures (in SSIS or SSMS), insert the “SET NOCOUNT NO” line at the beginning of the SP (not in out of the SP). It will switch off the record count tracking feature in SQL.
2. While using the SP in OLEDB source, please note that the query should return a set of records. So add Select statement at the end of the SP to return the resultant records. Probably this could resolve the issue.
3. In the OLEDB source SQL command, before entering the “Exec Sp_name” code, add this code at the beginning “SET FMTONLY OFF”.

If the issue still persists, you can try using the table variable in the Stored Procedure. From the table variable, select the resultant records at the end of the SP. Hope this works.

About david.masony

Questions
0

Answers
1

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 06/27/2011
• Question Category: MS SQL Server