• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1552
  • Last Modified:

SSIS Data Flow Parameterized query problem

Hi,

I am using  SSIS in SQL Server 2005 and want to have a query like this in my data flow task

Select a.*
from abc as a
inner join (Select b.id,c.id from xyz as b  inner join pqr as c on b.id = c.id and b.id > ?) as t1
on t1.id = a.id

SSIS fails to detect the parameter (?) for the inner query and gives message.

"
Parameters cannot be extracted from the SQL command. The provider might not help to parse parameter information from the command. In that case, use the "SQL command from variable" access mode, in which the entire SQL command is stored in a variable.", so assuming this is your problem, then you can workaround.

"
0
pai_prasad
Asked:
pai_prasad
1 Solution
 
Atlanta_MikeCommented:
Is this an old DTS package and transform or Are you using the SQL Server 2005 SSIS OLEDB command from the toolbox? Where are you setting the parameter?
0
 
pai_prasadAuthor Commented:
Is this an old DTS package and transform
>> Nope.

 Are you using the SQL Server 2005 SSIS OLEDB command from the toolbox
>>Yes


Where are you setting the parameter?
>>
OLE DB Source>> SQL Command Text>> Parameters button on the right..
0
 
imran_fastCommented:
You should define a global parameter and put the whole qurey in that parameter. Then use this parameter as data source.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
pai_prasadAuthor Commented:
i have tried that also but the variable data type string has a limitation of 128 char...
which aint enuf for the whole query...
0
 
Atlanta_MikeCommented:
Do it in the Execute T-SQL Statement Task and use an Expression to supply the value of the parameter.
0
 
pai_prasadAuthor Commented:
actually this has been conf as a bug in SSIS...
0
 
Anthony PerkinsCommented:
Please supply a link to some documentation to confirm that.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now