where clause condtion as a variable (SSIS 2005)
Let's say we have this query
select * from customer
where customerid = '1'
My question is can we create this "customerid = '1'" condition as a variable in SSIS and once it becomes a varible then I will extract the conditions for a column in table,
Software/Hardware used:
SSIS
ASKED:
February 16, 2010 9:51 PM
UPDATED:
February 17, 2010 10:49 PM
Answer Wiki:
try using dynamic sql in a stored procedure. Something like this:
declare @sql varchar(1000)
declare @tablename varchar(100)
set @sql = 'SELECT * FROM '
set @tablename = 'database.schema.tablename'
set @sql = @sql + @tablename
exec ( @sql )
To see all answers submitted to the Answer Wiki: View Answer History.
If there are a finite number of where clause conditions, the simplest method is to use a control variable to choose the condition of interest at run time. Note that this is NOT necessarily an efficient way to run the query, but is very easy to implement.
…
where
(conditionselector = 1 and {this condition clause})
or
(conditionselector = 2 and {this condition clause})
or
…