Hi,

I'm using ssis 2005 - I'm trying to create an ole db coomand which uses
parameters in sub-qyery:

SELECT vwContactInteraction.iContactID,
vwContactInteraction.iInteractionID, 1 AS iSiteID
FROM nice_interactions.dbo.vwContactInteraction AS
vwContactInteraction
WHERE (vwContactInteraction.iContactID IN
(SELECT TOP (100000) WITH TIES iContactID
FROM nice_interactions.dbo.vwContact
WHERE (dtModifyDate > ?) AND (dtModifyDate
<= ?)
ORDER BY dtModifyDate))

Is there a way to do it - I'm keep getting the "Parameter information cannot
be derived from SQL Statment" ????

Hello nirk,


The problem will no doubt still be the fact that the design time interface
is confused by placing the ? placeholders within a subquery. There is an
easy way around this though and it is more elegant than that we have in DTS
2000. You can create an Expression that maps to the SQLStatementSource property
of the task. This will allow you to use your variables. It also has a validation
window that will let you know what the string looks like before running the
task itself.


Allan

The way I do mine to bypass the subquery issue is to declare new
parameters and set them you your variables like so:

declare @parm1 int

Set @parm1 = @Var1

Select *
(Select Parm1
From table2
Where parm1 = @parm1)

Doing so would allow you to use your package variables in a subquery.
Hope this helps
Philip