I have been unable to map parameters for a sql select to a user
variable. The error message states no data provided. I have read other
topics related to this error, but need to confirm this option does not
work in SSIS for OLE DB Source objects in Data Flow Components.

I have tried various combinations of names for parmaters, replacing the
"?" with the procedure parameter name, etc. I've spent a couple days
playing with this, watching what profiler is seeing, and getting
nowhere.

SqlCommand: EXEC dbo.SPGetMonthlyTickets ?

Proc on server:
[dbo].[spGetMonthlyTickets]
@smnthtckt varchar(50)
AS
Declare @sSql varchar(2000)
set @sSql =
'select Distinct acctno, name, ytdbhexp, [' + @smnthtckt + ']
[Tickets], status, ctsw ' +
'from dbo.Merchantwftbl ' +

'where status = '' ''' +

'and ctsw IN ('01'',''02'',''03'',''04'',''05'',''09'') '
'order by acctno desc '

exec(@sSql)

Thanks,
Ken

I myself played around with this for a few days before getting it to work.
Here are the things I had to do to get mine to work.

SQL command text:
SET FMTONLY OFF; EXEC dbo.SPGetMonthlyTickets ?
Had to add the SET statement since my select was not the first statement in
the sproc. Though this approach is questionable since the method for making
the SQL call may change, hopefully making this SET statement unnecessary.
The other recommendation I saw was to insert the contents of the sproc into
the SQL command text but I did not want to maintain the code in SSIS.

For the Parameters make sure in the Mappings that the naming under the
Parameters column is the name of the variable in the sproc, i.e. @smnthtckt.

Hope that helps.

"Ken" wrote: