SQL Server Integration Services - Adding a Parameter to an SQL Statement

Question

I have a SQL Server Integration Services project that queries a SQL Server 2005 database using an OLE DB source using an SQL command as the data access mode.

I am trying to parameterize my SQL query, but the syntax is not @PARAM and when am I trying to use? and click on the parameters, I get the error message: "Parameters cannot be extracted from the SQL command."

I'm doing something like

SELECT * FROM [dbo].[TabledValuedFunction] (?)

Answer 1

, OLEDB SSIS , , .., , ( ). , SQL . , .

Answer 2

SQL . 2 Qry1 Qry1Param1.

Qry1 , -

"SELECT * FROM [dbo].[TabledValuedFunction] where tbl_key = " +   @[User::Qry1Param1]

Qry1Param1 1.

, Qry1

SELECT * FROM [dbo].[TabledValuedFunction] where tbl_key = 1

OLEDB, SQL User:: Qry1.

Answer 3

, , , . SELECT * . . , / , OLEDB , .

OLEDB , ??

Answer 4

ole db , SQL-. ole db, . , :

- . , ;

: sql. Sql : * id =?. .

sql : sql . SQL- : *

I was not completely sure what you were trying to parameterize, so I somehow gave you some possible options that may / may not help you solve your problem. Hope this helps.

Answer 5

How did you configure your sql runtime task component? I just tried it and it works great.

This is the function I used:

create function test1(@x int) 
returns @tbl table (x int)
as begin 
 while ( @x > 0 )
 begin
  insert @tbl values(@x);
  set @x-=1;
 end;

 return;
end;
go

This is my ExecSql parameter.

Tested on MSSQL2008, SSIS2008.