Monday, March 19, 2012

assigning values for variables from ref table

I have variables and values stored in a table in this format

process_id | t_variable | t_value
--
1 | Remote_Log_Server | AUSCPSQL01
...
many such rows

how to assign values to variables in SSIS?

basically i'm looking for SQL equivalent of the following query i currently use to assign values to multiple variables (in a single query)

SELECT
@.varRemoteLogServer=MAX(CASE WHEN [t_variable] = 'Remote_Log_Server' THEN [t_value] END)
,@.varVariable2=MAX(CASE WHEN [t_variable] = 'variable2_name' THEN [t_value] END)
FROM Ref_Table
WHERE process_id=1
Have you tried using Execute SQL task? It may be used to assign values from a result set to a variable.|||This looks similar to what you get with a SQL Server or table based configuration. It has its own way of doing this, but you may want to look into it.

No comments:

Post a Comment