CODEWITHSUNDEEP
sql-serverssis
AJM
AJM

Reputation: 32490

SQL Server Integration Services - adding parameter to SQL statement

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] (?)

Upvotes: 3

Views: 20184

Answers (5)

user1052441
user1052441

Reputation: 21

I was having this problem and it drove me crazy that some queries would work while others would fail. The resolution for me was to remove SELECT * and instead explicitly specify the columns. In some other queries, I had to remove subqueries. Basically, you have to fiddle with / simplify your query until OLEDB provider is able to adequately parse all the information it needs to render parameter info.

Why couldn't the OLEDB provider return a more descriptive error message so people wouldn't have to resort to the package variable workaround????

Upvotes: 1

toper
toper

Reputation: 66

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.

Upvotes: 0

CTKeane
CTKeane

Reputation: 670

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.

Upvotes: 7

rfonn
rfonn

Reputation: 2221

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.

Upvotes: 0

Tom H
Tom H

Reputation: 47444

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.

Upvotes: 1

Related Questions