Using a stored procedure with SSIS OLE DB Source

Hi

I had a mission to write a SSIS package that transfers data from a source to destination (pretty ordinary... :-)), but the problem was I had to dynamically generate the sql statement that perform the select and I wanted to use the result set generated by the procedure as the output of the OLE DB Source. (The result set is constant, just the select statement that generates it is dynamic).

First, SSIS didn't work properly with the dynamic query since it could not know the structure of the result set. In order to get around this problem, I created a second stored procedure which inserts the result of the first stored procedure into a table variable, and then select from that variable. This way, SSIS could recognize the structure of my result set.

First procedure that perform the select - sp1
Second procedure :

create procedure sp2 @p1 varchar(100), @p2 numeric(19,0)
as
SET NOCOUNT ON
declare @tmp table (
col1 varchar(250),
col2 datetime)
insert into @tmp
exec sp1 @p1, @p2

select * from @tmp

SET NOCOUNT OFF
return

Notice the bold lines with the set nocount statement.
At first I didn't have these statements in my procedure, and I kept getting this error message:

"Error: A rowset based on the SQL command was not returned by the OLE DB provider."
error 0xc02092b4

But once I have added the set nocount statement at the beginning and ending of the procedure, everything was working.

Some Internet forums and blogs suggested using the SET FMTONLY statement, but I didn't need to use it.

Till next time
Bye