Skip to Content
Former Member
Oct 01, 2014 at 08:53 PM

Options for setting global variables in a data flow


Hello experts,

I am new to BODS, but have used a couple other ETL/ELT tools. Please help me answer below questions:

#1. What are my options (if any) to set the values of global variables inside a data flow? Can I set it in a query transform either directly (preferably) or via a custom function, or any other transform?

#2. Can I have a data flow without a target table? ( I am trying to use a data flow to process only one row from a source table - just to set a bunch of global variables. If the answer is No; what is the easiest dummy target - a flat file? I dont want to use a template table as it adds an temp table to the database; whereas, with a flat file I am assuming it need not pre-exist (and will not be generated in my dataflow anyway). My thinking correct? Any more choices here?

#3. Inside a script, using the sql() call, can I get more than one column into variable(s). For instance, below statement gets one column into a global variable. However, I want to get multiple columns into separate variables - in one pass. Possible or not?

$G_Current_SQL_Text = sql('Datastore_Name','SELECT Column_1 FROM my_table WHERE SQL_NO = [$G_Current_SQL_No]');

Background (If interested):

<<Please look at the attachment - "Proof_Of_Concept.jpg">>

I am trying to define a data validation framework using BODS. I have a bunch of validation SQLs in a metadata table and having BODS loop through it using a while loop. I am processing only one row (containing the sql statement) at a time. I am using a global variable ($G_Current_SQL_Text) in the SQL transform to execute the sql and write the result into a target table. However, as a precursor to this, in the beginning part inside the while loop, I am using script to define below global variables in multiple passes. I want to replace the script with a data flow; hence the questions posted above




$G_Current_SQL_No_Of_Columns, etc

Thanks in advance,