SQL Server Integration Services - adding parameter to SQL statement

Question

I have a SQL Server Integration Services project which queries a SQL Server 2005 database using an OLE DB Source with SQL Command as data access mode.

I'm trying to parametrize my SQL query but the syntax is not @PARAM and when I try to use ? and click on the parameters I get an error saying that "Parameters cannot be extracted from the SQL Command."

I'm doing something like

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

Answer 1

It looks like the OLEDB connection doesn't supply SSIS with everything that it needs to determine the parameter data type, etc. so it can't properly parse out what it should be (this is conjecture on my part). As the error message suggests though, you can make the whole SQL command come from a variable. You'll need a string variable which you set previous to the Data Flow.

Answer 2

You can use variable parameters in the ole db source to provide a dynamic table, dynamic conditional statement, or a dynamic sql statment. By clicking on the ole db source, you can change the data access mode. Below are examples for some of the different type of access modes in which you can use package variables:

parameterize table- use the table name or view name variable. Then select the variable name that contains a valid table name;

parameterize conditional statement: use sql command. Sql Command text would be something like : Select * From Table Where id = ?. Then map your variable.

dynamic sql in a variable : use the sql command from variable. You can then place your sql statment in a package string variable: Select * From Table

I was not entirely sure what you were trying to parameterize so, I kind of gave you the run down on some possibile options that may/may not help you with your specifice issue. I hope this helps.

Answer 3

When creating a dynamic SQL statement I've had much better luck using expressions. So in this hypothetical case I would create 2 variables Qry1 and Qry1Param1.

Inside the Qry1 variable I would use the expression editor to create something that looks like

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

Qry1Param1 variable would be something like 1.

So that Qry1 evaluates to

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

Then you can change the OLEDB Data source to use the data access mode of SQL command from variable and enter in the User::Qry1 variable.

Answer 4

How did you set up your execute sql task component? I have just tried and it works fine.

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 setting.

Tested on MSSQL2008, SSIS2008.