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

SSIS Data Flow Parameterized query problem


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.

1 Solution
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?
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

Where are you setting the parameter?
OLE DB Source>> SQL Command Text>> Parameters button on the right..
You should define a global parameter and put the whole qurey in that parameter. Then use this parameter as data source.
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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...
Do it in the Execute T-SQL Statement Task and use an Expression to supply the value of the parameter.
pai_prasadAuthor Commented:
actually this has been conf as a bug in SSIS...
Anthony PerkinsCommented:
Please supply a link to some documentation to confirm that.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

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