A question, that is often asked by customers, is how to make WHERE statements dynamic by using variables. All Xtract IS components can be easily used together with SSIS variables. This short article shows, how to set up an SSIS variable, fill it with a value by using a script, and then use the variable in an Xtract component.

If you want to use the current date in a where clause, you have to convert the date into a format SAP understands. First we need to create an SSIS Variable called “TDatum” (data type String, scope Package) in the variables pane.

After creating the variable we insert a Script Task.

Double-click on the Script Task and insert the recently created variable in the field ReadWriteVariables. With the button “Edit Script…” the script editor opens.

Insert the following code into the script editor.

public void Main()
{
Dts.Variables["TDatum"].Value = DateTime.Now.ToString("yyyyMMdd");
Dts.TaskResult = (int)ScriptResults.Success;
}

This code converts the current date to the format year, month and day without any delimiter, and stores the value in the variable “TDatum”. Now we can use this variable in an Xtract IS Table Component to make the where clause dynamic.

And enjoy the result:

Written by PatrickTheobald

Patrick is the living demonstration that you don’t need a degree to go places. Having dropped out of his physics studies, the amateur pilot founded Theobald Software in 2004 – and without any kind of crash landing developed it into a leading SAP integration supplier, with branches in Stuttgart, Seattle, Hong Kong and Istanbul. As managing director, visionary and self-appointed factotum, he keeps the team on track. In his leisure hours he plays double bass and goes running.