Options for setting global variables in a data flow

[Former Member]

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_Total_No_Of_SQLs

$G_Current_SQL_Text

$G_Current_SQL_Datastore

$G_Current_SQL_No_Of_Columns, etc

Thanks in advance,

Kaushik.

[former_member205887 · Accepted solution]

1) You can call Custom Function to assign Global Variable, In the function assign a value. But this function will be called for every record.

2) When you have only one record, use Script.

[Former Member]

Balakrishna & Dirk,

Thanks for the quick responses. Both of your answers were very helpful. A few follow-up questions:

1. In a custom function, can we dynamically assign the value for the variable? In other words, is it possible to

a) Create just one custom function with two input parameters - one for the global variable name and other for the assigned value?; or

b) Do I need to create a custom function for every global variable that I have to assign a value to?

2. With flat file as the target;

a) Does the file need to pre-exist in the specified directory; or

b) Will an empty file be created when the data flow is executed; or

c) No file will be created as no rows will be written to the file.

Thanks

Kaushik.

[former_member187605]

1 b

2 b

[former_member215091]

Hi,

You can assign global variable values in execution properties.

and the file need to pre-exist in the specified directory.

Thanks,

Faiz

[former_member187605]

#2. Every dataflow needs at least one target, table or source. Even if the flow doesn't procude any output, the file and template table are created.

#3. The sql() function returns a single text string only. If you want multiple columns, you'll need multiple calls.