Subqueries on DTS/SSIS Packages
-
Hi,
I had this problem on DTS and now I'm having the same with SSIS.
I need to add a parameter into my subquery and in some cases this is extremely important and there is no way to create the query without the subquery.
Does anyone has any idea?
Below, follow a query example about what I'm talking:
select *
from tableA a
where exists (Select ID
from tableB b
where b.ID = A.ID
and b.Date = ? )
Tks
-
In order to create the input parameter for an Execute SQL Task in DTS, you will need to update the Task with a Disconnected Edit:
- Through EM (For SQL2K), open the package and select Package, then Disconnected Edit.
- Find the task you need to update and click it.
- In the right pane, double click the property for "InputGlobalVariableNames".
- Enter the name of the GV you wish to provide input to the task.
- Click OK, then Close.
There is a known issue with the parser for an Execute SQL Task when attempting to add an input parameter to a subquery. Let me know if this does not work or does not answer your question.
As for SSIS... I can't help you there. I only know SQL 2000 at this time.
-
Ok... tks Don....
Now, I'm moving this question to SQL 2005 Integration Services to get SSIS answers.
-
Also On DTS (sorry, not up to SS2005 yet), this might work:
DECLARE @DateParm DATETIME
SELECT @DateParm = ?
select *
from tableA a
where exists (Select ID
from tableB b
where b.ID = A.ID
and b.Date = @DateParm )
Although I have a feeling I`ve had problems assigning parameters to variables in DTS`s too
"Don`t try to engage my enthusiasm, I don`t have one."(Marvin)
-
run as stored proc.
-
That's cool. I dont know what I was doing wrong but now the variable worked fine.
Also, a temp stored proc is a good idea as I cannot create a regular proc on the Database.
Tks fellows
-
Did you have a comment in the SQL? I have noticed that parameters don`t work where there are comments in the SQL
"Don`t try to engage my enthusiasm, I don`t have one."(Marvin)
-
it doesnt work for me either!!!!
-
Don't forget the semicolons:
DECLARE @DateParm DATETIME;
SELECT @DateParm = ?;
select *
from tableA a
where exists (Select ID
from tableB b
where b.ID = A.ID
and b.Date = @DateParm )
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply