A scripted expression has a C#.NET similar syntax and must start with #{ and end with #} . Basic .NET data types and operations in the System namespace of the .NET Environment including string operations are supported.

Examples:

Description Input Output
Current date and timestamp #{ DateTime.Now}# 23.07.2013 10:17:37
Date 5 days ago #{ DateTime.Now.AddDays(-5).ToString("d") }# 18.07.2013
Current date #{ DateTime.Now.ToShortDateString() }# 23.07.2013
Current date in SAP format #{ DateTime.Now.ToString("yyyyMMdd") }# 20130723
Current year concatenated with “0101” #{ String.Concat(DateTime.Now.Year.ToString(), "0101") }# 20130101
Current year concatenated with “0101” #{ String.Concat(DateTime.Now.ToString("yyyy"), "0101") }# 20130101
Current year concatenated with “0101” #{ String.Concat(DateTime.Now.ToString("yyyyMMdd").Substring(0,4), "0101") }# 20130101

Using Scripted Expressions #

Scripted expressions can be used both for the where-condition of a table and in the destination settings using Custom SQL(Xtract Universal exapmle).

IF-statement #

An IF-statement is supported and has the following syntax: iif(bool condition, string trueResult, string falseResult)

Example:

Description Input Output
When we are in the 7th month, the output is “July”, else “Unknown” #{ iif(DateTime.Now.Month==7, “July”,”Unknown”)}# July

Defined Expressions #

Defined functions #

bool ExistsTable(string tableName): check if the the table exists on the database destination.

Defined properties #

The following expressions are defined and can be used:

#{ Extraction.TableName }# : Name of the extraction
#{ Extraction.RowsCount }# : Count of the extracted rows
#{Extraction.RunState}#: Status of the extraction (Running, FinishedNoErrors, FinishedErrors)
#{(int)Extraction.RunState}#: Status of the extraction as number (2 = Running, 3 = FinishedNoErrors, 4 = FinishedErrors)
#{Extraction.Timestamp}#: Timestamp of the extraction 

See an example of the use of defined expressions in the destination Custom-SQL settings.

Supported data types #

Following .NET datatypes are supported:
Object, object
Boolean, bool
Char, char
String, string
SByte
Byte, byte
int
Int16
UInt16
Int32
UInt32
long
Int64
UInt64
Single
Double, double
Decimal, decimal
DateTime
TimeSpan
Guid
Math and
Convert

Supported key words #

Following key words are supported: true, false and null.

You can find more information about the .NET data types and Date and Time format in MSDN.