Using Parameters in ODI: The Dynamic File Name Example

The posts in this series assume that you have some level of familiarity with ODI. The concepts of Model, Project, Interface and Package are used here assuming that you understand these concepts in the context of ODI. If you need more details on these elements, please refer to the ODI Tutorial for a quick introduction, or to the complete ODI documentation for detailed information on these concepts.

In a previous post we have seen how to use variables in the ODI interfaces and packages.
We used two methods to assign a value these variables: a direct assignment of a value, or a "refresh" statement that was running a SQL query and storing the resulting value in the variable.
Today, we will see how to set the value dynamically as we start our process. The value to be stored in our variable will be passed as a parameter to our process.

The example we will work on is a fairly common one. When dealing with flat files, it is not rare for the actual name of the file to vary over time. An external process may know the file name and pass that name over to ODI for further processing.

For our example to work, we will need to perform two actions:
- Reference the file name dynamically (In the ODI Metadata, we cannot hard-code the file name anymore)
- Pass the file name as a parameter to our process.

1. MAKING THE FILENAME DYNAMIC

1.1 Creation of a Variable

Our first step is to create a variable to store the file name. Variables are defined in projects, even for variables that will be used in the metadata. Select the project where you will use the variable (typically the project that will contain the Interface where the file is used as a source or as a target).
We will create a variable called FileName.

Filename Variable Creation

Defining a default value for the variable is not required, though it is good practice. We do not need an associated query in the refresh tab of the variable object, as we will receive this value as a parameter.
Click Ok to save the definition of the variable.

1.2 Using the variable in the metadata

Now that we have created a variable, we can use it in place of the file name.
Note: If you are not familiar with the definition of Files in the ODI Models (where the metadata are defined) please refer to the ODI Tutorial where you will find a couple of examples.

I usually recommend starting with a hard-coded version of your file name. Copy a version of the file locally, and use it to define the file structure. This will allow you to validate the file format, make sure that the data can be viewed with the ODI GUI, validate alignment of the columns, etc. Pay particularly attention to date columns (unless you define them as strings – after all, this being a file, you can choose the most convenient format for you): dates require the definition of the date format. The ODI documentation gives you the necessary details on how to describe this format (See the Appendix on Date Formats in the Reference Manual).

In our example, we will use the file SRC_SALES_PERSON.txt that is provided as an example with ODI (look under your ODI installation directory under oracledi/demo/file).

Once we have validated that the file format is correct, we will edit the file definition. Double click on the file name in the Model tree, so that we can change the value of the Resource Name. We can keep the Name as is, to have a meaningful description of the nature of the file, and replace the Resource Name with our variable. Whenever using a variable outside of a project, keep in mind that you always have to prefix the variable name with the project CODE. In case of doubt, double click on your project Name in the Project tree: the definition window of the project will show you both the name of the project and the code of the project.

Project Code

In the example above, the project code is SALES.

Back to the Definition of our file, we now change the Resource Name from SRC_SALES_PERSON.txt to #SALES.FileName.

Dynamic File Name

When you click Ok to save this new definition, you can see in the Model tree that ODI will show you both the Name (SRC_SALES_PERSON.txt) and the Resource Name (#SALES.FileName) to indicate that this value will be set dynamically.

File Name In Tree

One important note is that once you use the variable as part of the resource name, you cannot see the data from your file from the GUI as the variable will only be resolved at run-time. From that perspective, it may be useful to have two definitions for the same file: one using the variable, and one that points to a specific file so that debugging is made easier.


2. PASSING THE FILE NAME AS A PARAMETER

2.1 Create a Package and Declare the Variable

You will have to create an interface where you use the file (as you would do for any other file) so that you can really see the operations in action. Then create a package where you drag and drop the FileName variable and the interface. Make sure that the variable is your first step (right-click on the variable icon in the package and select First Step if it is not the case. The first step of your package is identified by a little green triangle over the step name).

Parameters Package


Click on the icon representing the FileName variable and set the action on this variable to Declare Variable. This will authorize the passage of the value as a parameter at run time.

Parameter Declare Variable


2.2 Create a Scenario

You will only be able to pass parameters to a "production ready" object: the scenario. The creation of a scenario is trivial: right-click on the package name, select "generate scenario", edit the scenario name and version if needed and click ok.

Generate Scenario

Set Scenario Name and Version

If you have "declare variable" steps though, you will be prompted to define whether these will be parameters or not.

Scenario Parameters

When this window prompts you, if you do not change anything, all declared variables become placeholders for potential parameter values. Note that in the screenshot above, we have changed the selection from "Use All" to "Selective Use" simply to highlight the variable name. Either selection would have had the same value in our example.

At this point, we now have a scenario that is ready to receive a value for our FileName variable.

2.3 Different ways to start the scenario and pass the parameter

There are many ways to start a scenario. Each one of those will allow you to set the values for your parameters.

2.3.1 Starting the Scenario from Designer or Operator

In both cases, the GUI will prompt you for the value of the parameters after you select in which context and with which agent to run the scenario:

Set Scenario Params Value

ODI will by default select the last value assigned to the variable. You can uncheck the "Last Value" check box and change the value for the parameter. Hit the "Enter" key or the "Tab" key to validate your entry and run the scenario.

2.3.2 Starting the Scenario from a Command Line

You can also start a scenario from a command line interface. The script startscen (.bat for Windows or .sh for Unix-like environments) will take the scenario name as a parameter... along with the name of the variables that you are setting along with their values. Enclose the variable name and its value in double quotes, and prefix the project code with a minus sign (-). Remember to prefix the variable name with the project CODE as usual:

starscen DYNAMICFILENAME 001 "-SALES.FileName=c:/Incoming/Sales09092009.dat"

2.3.3 Starting the Scenario through a Web Service

If you start the scenario from a web service, you are invoking the service OdiInvoke. When you look at the WSDL for this Web Service, you will notice that beyond the scenario name, version and the execution context, you can specify pairs of variable names and values. Again, remember to specify the project code

WSDL to Start Scenario

2.3.4 Starting the Scenario from Another Scenario

If you start a scenario from another ODI package, you can either drag and drop the scenario in the parent package or use the ODI tool OdiStartScen and enter the information manually. When you look at the property window for that step, you will notice an Additional Variables tab. In this tab, you will be able to select your project, variable and to set the value for the variable. Remember to use the Enter or the Tab key to validate your choices as you make them.

Scenario Call From Package


MORE ON VARIABLES IN ODI

For more information on ODI variables, please refer to the ODI Users Guide (part of the Documentation Library that comes with the ODI installation), in particular the entry "Creating and Using Variables"

All Screenshots were taken using version 10.1.3.5 of ODI. Actual icons and graphical representations may vary with other versions of ODI.

Comments:

wrt section 2.3.4, when i use date variables, i get a unparseable date error. Any idea how to fix this?

Posted by derick on May 24, 2009 at 10:20 PM PDT #

Check out the values you are passing for the data: my guess is that you are not passing a valid date in your parameter. For more information about Dates, please check out this link: http://www.oracle.com/technology/products/oracle-data-integrator/10.1.3/htdocs/documentation/oracledi_reference.pdf, in particular the appendix with details on ODI and Dates. Hope that will help! -Christophe

Posted by Christophe Dupupet on May 25, 2009 at 11:25 PM PDT #

I have created an interface that loads the data of the flat file to a relation table. But then, I have a question. Do I need to have the default vaule for the variable Filename same as the location of the source file(file for which we have created a model and created a data store , then changed the resource name).

Posted by Kranthi Kumar on September 01, 2009 at 08:32 AM PDT #

The default value of the variable should not really matter as long as you set the value for the variable in your package before you actually use the variable. I always set a default value for the variables in case somebody else would use the variable and forget to set a value. It makes for errors that are easier to debug and to understand...

Posted by Christophe Dupupet on September 03, 2009 at 06:24 AM PDT #

What if I have multiple variables to be passed to call a scenario through web services??

Posted by Deepak on April 02, 2010 at 03:14 AM PDT #

You can definitely pass several parameters to your ODI scenario when you start them with Web Services. As part of the payload of the ODIInvoke web service, you will see an entry for VariableType: use this to set the name of the variable to be set and the value for this variable. You can use as many entries as needed to set the value for multiple variables. I hope this helps! -Christophe

Posted by Christophe Dupupet on April 02, 2010 at 03:41 AM PDT #

Hi, I have followed all the steps which are given in this article. But after running the sdenario i got one error "[Microsoft][ODBC Excel Driver] Syntax error in FROM clause." To resolve this error i have enabled the File naming rules which was disable. To enable this i went to Topology ----> Microsoft Excel ( Double click on to it ) ---> Definition Tab ----> Under naming rules and Data handling --> Files. but after making such changes, now when i am running the scenario i am getting the error as "com.sunopsis.sql.SnpsMissingParametersException: Missing parameter" I have used LKM - SQL to SQL IKM - SQL increment upadate. Any idea how to resolve this error.

Posted by Mala on July 06, 2010 at 05:30 PM PDT #

I tried executing a scenario from command prompt but unable to execute.. I just created 1 variable to capture file name and 1 interface. I'm able to pass parameter and execute the scenario from Designer but when i execute from command prompt , its getting executed with error. No error message. I could see only 1 step in the operator thats the variable... No clue whats the issue.. startscen.bat TEST 001 "-TEST.file_name=D:/SITA_VOYAGER_PAYM/Source_files/ProcessorFiles_Samples/PaypalExpressCheckout/efg.txt" Any help on this is appreciated. Regards, Surabhi

Posted by Surabhi on August 26, 2010 at 08:10 PM PDT #

If the first step of the process does not show any error, look at the process level and you should have more details as to what went wrong. This could be typical of a connection issue (check out your source and target systems, as well as connectivity to the repository). Worse case scenario: the schema for your repository is full...

Posted by Christophe Dupupet on August 31, 2010 at 12:57 AM PDT #

If you are using Excel, the connectivity to your spreadsheet is defined in the ODBC driver definition - unfortunately not something that can be modified from within ODI. But what can be done from within ODI is to dynamically copy the files to a known name... that you use in your ODBC connection string. ODBC always points to the same name, and you can control which file is actually being processed in that case.

Posted by Christophe Dupupet on August 31, 2010 at 01:20 AM PDT #

Can I pass variable of ODQ script name and location into ODIDataQuality?

Posted by Leo on March 23, 2011 at 01:01 AM PDT #

This should definitely work. The substitution of the variable with its actual value will be done by the agent at run-time. ODQ will received the substituted value!

Posted by Christophe Dupupet on March 23, 2011 at 01:06 AM PDT #

So I just put #v_script_location.sh in value for "Data Quality batch file" parameter field?

Posted by Leo on March 23, 2011 at 01:32 AM PDT #

Yes, as long as you are doing this in the project where the variable was defined. If you are using a global variable, you want to use #GLOBAL.v_script_location. Also remember to set the value of your variable prior to this call (either as a parameter, or by using "set variable" or "refresh variable" prior to this step)

Posted by Christophe Dupupet on March 23, 2011 at 02:29 AM PDT #

Hello christopher!! very helpfull and easy to follow your posts I really enjoy !! I have a doubt ! I have a scenario in the company that I work for where I have to extract data from a certain table then I have to generate a flat file and put that in a directory with a predefined name "filename.in" the layout all that has been defined already, then other people will validate and let the file on another directory that I will read and make the load process back into the tables, how I generate the file name?? should I put a fixed name on the resource name ?? thanks in advance

Alan Yves

Posted by guest on October 08, 2011 at 10:34 AM PDT #

Alan,

You can either define the file twice in your model (not very productive, but since there is a manual process in the middle, why not... lineage could be counter intuitive otherwise).
Or you use the variable to rebuild the directory name and the filename altogether: the "filename" can include sub-directories.
So your model could use a variable for the file name: #Filename
Then in your processes, you set the filename to something like this:
preprocess/filename.in
When you need to process the file that has been processed your can set the file like this:
postprocess/finemame.in
This assumes that the directories pre and post process are both under the same directory. If not, you could use variables in Topology to define the directory names. In that case, no need for a variable name in the model as long as the file name remains the same!

Posted by christophe dupupet on October 11, 2011 at 02:50 AM PDT #

Hi Christophe,
I have one doubt.Please help me.Currently i have files in
\\cr-beaodi-01\ home\Ashok
and i can give dynamic filename as per requirement. But what if i need a file from source something outside of the box? Like
\\cr-datajnctn-01\ProcessControl\dw\test

Is it possible ? because when i created physical schema for File Dataserver i have given one path.Obviously everyfile shoud be there (\Ashok)or in sub directory folder (\Ashok\testdir) but not in parent (\home).
Please suggest.I am confused

Thanks

Posted by Ashok on October 19, 2011 at 09:20 PM PDT #

Ashok,

Yes, all files will be under the directory specified in topology (or under sub directories of that directory).
So if your physical schema in topology is \\cr-beaodi-01\home then your files wil have to be under \cr-beaodi-01\home or under a sub-directory of \\cr-beaodi-01\home. If they are is a sub-directory, the additional elements of the path will have to be part of the file name, for instance Ashok\myFile.txt (and I think there is a limit as to how deep you can go there...)
Now if you want the physical schema itself to be variable, there are two options.
The first option is to define several contexts. If you want to process the files in \\cr-beaodi-01\home\Ashok in your development environment and in \\cr-datajnctn-01\ProcessControl\dw\test in your production environment, then create two contexts (dev and prod), two physical schemas (one for each path), one single logical schema - and execute your code against the context of your choice. My example is dev and prod but you can also define several production execution contexts if it makes sense in your environment.
The second option is to use variables as well for the definition of the physical schema. In that case, instead of hard-coding the path in your schema, you would use an ODI variable (make sure to prefix the variable with the project code - or GLOBAL if this is a global variable). Make sure to set the value of the variable prior to executing the code though. There is more on how to use variables in topology here: http://blogs.oracle.com/dataintegration/entry/using_odi_variables_in_topolog

Posted by Christophe on October 20, 2011 at 03:58 AM PDT #

Thanks Christophe for clearing my doubts.Its really helpful <3.
Now i am looking some of your post regarding odi agent and scheduling.

Regards
Ashok

Posted by Ashok on October 20, 2011 at 11:45 PM PDT #

Hi,
I have a similar condition
variable-scenario of interface
this variable provides dynamic filename
but as in your case it is not working since you are using interface
and not its scenario.
Please let me know is it possible to use scenario in place of interface
if yes then how?

Posted by guest on January 09, 2012 at 06:27 PM PST #

You cannot use the interface's scenario because you will have to set the variable's value one way or the other.
What you have to do is to create a package, add the variable and the interface in the package, then generate a scenario from the package. The variable step in the package can be a "refresh variable" step (if you get the file name from a table) or a "declare variable" step if you want to pass the value as a parameter to the interface.
Another benefit of having a package for the variable and the interface is that you can also take other actions on the file. The ODI tools available in the package will allow you to move the file (in you want to save it in an archive folder after processing for instance), rename the file, delete the file... all operations that are often asked for after a file has been processed.

Posted by Christophe on January 10, 2012 at 01:09 AM PST #

Hi, Sorry for a bit of digression.

I am facing a problem in ODI please help me if you can.
I have a source of ACCESS files and target Oracle Database.

Now Access has a boolean datatype(true,false) which is not supported by oracle

The knowledge module sql -sql fetches the boolean datatype from access
without quotes ie true and not "true" this is happening on command on source.

Now In command on target while inserting it gives error because it
is an insert statement where columnname is without quotes(target is varchar2).

It seems that we might have to change the ODI functions used in KM but we dont want to do that please let me know any work around possible for this one.

Posted by guest on January 10, 2012 at 06:53 PM PST #

Mmmm... yes, a little bit off topic...
You would have to convert the type on MS ACCESS prior to extracting.
It looks like the function CStr would do the trick on MS ACCESS:
make sure your mapping in ODI is "on source".
use CStr(YourColumn).
That should return "True" or "False" in a string format for Oracle consumption.

Posted by Christophe on January 11, 2012 at 02:23 AM PST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Learn the latest trends, use cases, product updates, and customer success examples for Oracle's data integration products-- including Oracle Data Integrator, Oracle GoldenGate and Oracle Enterprise Data Quality

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
3
5
6
7
8
9
10
12
13
14
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today