CODEWITHSUNDEEP
sqlsql-serverstored-proceduresssis
TrevorGoodchild
TrevorGoodchild

Reputation: 1060

SSIS: Retrieving result set using a stored procedure, stored in a variable, that requires input parameters

I am working on an SSIS package that needs to retrieve a resultset. I'm storing all of the changeable settings in the database, one of them being the stored procedure that retrieves the data. I'm storing the settings as variables within the package.

My problem is, the stored procedure requires input paramters. So I have an OLE DB connection, using an Execute Task component, and I've set the variable name for the source variable in the Execute Task Editor. I know that if I were using Direct Input, I could just use "exec sp_Name ?, ?, ?" with the question marks denoting parameters, but I can't find any way to pass the parameters when the SQL SourceType is set to 'Variable'.

Upvotes: 0

Views: 2398

Answers (1)

billinkc
billinkc

Reputation: 61249

It's the same as if it were Direct Access.

@[User::Query] string "exec sp_Name ?, ?, ?"

Map your parameters as expected.

enter image description here

enter image description here

Upvotes: 2

Related Questions