Custom Properties #

The Custom Properties define the properties unique to the component. When parameterizing the component using SSIS variables or Expression Properties, the Custom Porperties are overwritten.

List of relevant Custom Properties:

Property Comment
ConvertsDates When this property is set to True (default) SAP date fields (YYYYMMDD) assume the SSIS pipeline type DT_DBDATE instead of DT_WSTR. In case of invalid data in SAP date fields, the following date conversions are applied:
InvalidDateReplacement, MaxDateReplacement, MinDateReplacement.
ConvertsTimes Enable or disable conversion of SAP times.
InvalidDateReplacement Allows the replacement of an invalid date format. The default value of the property is 1970-01-02.
To use the property enter a replacement value for invalid SAP dates, such as ‘20190132’ (January 32nd 2019) in the format yyyy-mm-dd. The value NULL is supported.
MaxDateReplacement Allows replacing the SAP dates with the year 9999. The default value of the property is 2099-12-31.
To use the property enter a replacement value in the format yyyy-mm-dd that contains the year ‘9999’. Example: ‘99990101’ (January 1st 9999). The value NULL is supported.
MinDateReplacement Allows replacing the SAP dates with the year 0000. The default value of the property is 1970-01-01.
To use the property enter a replacement value in the format yyyy-mm-dd that contains the year ‘0000’. Example: ‘00000000’. The value NULL is supported.

Parameterizing Using Expression Properties #

The recommended way of parameterization is to use SSIS expressions to set Data Flow Properties.
Data Flow Properties are displayed in the “Properties” window of the Data Flow Task. These properties are not to be confused with Custom Properties that are displayed in the “Properties” window of the component. When parameterizing the Data Flow Properties, the Custom Porperties are overwritten.

For more information on property expessions, see Microsoft Documentation: Use Property Expressions in Packages.

How to Use Expressions for Data Flow Properties

  1. Create an SSIS package, see Getting Started.
  2. Right-click the Data Flow canvas and select Properties. The window “Properties” opens.
  3. Add an Expression by clicking using […]. The window “Property Expression Editor” opens.
    Expressions
  4. Select a property from the drop-down list under Property.
  5. Open the “Expression Builder” using the […] button under Expression.
  6. Build your Expression and click [Evaluate Expression] to check for correct syntax.
  7. Confirm your input with [OK].

Available Data Flow Properties

List of Data Flow Properties that can be parameterized:

Property Name Description
[Xtract OHS].[ConvertsDates] Enable or disable conversion of SAP dates, see Custom Properties.
[Xtract OHS].[ConvertsTimes] Enable or disable conversion of SAP times.
[Xtract OHS].[InvalidDateReplacement] In case of invalid date values you need to enter a default date value. Every invalid value will be converted into this value, see Custom Properties.
[Xtract OHS].[MaxDateReplacement] Converts the SAP date 9999XXXX to the entered value, see Custom Properties.
[Xtract OHS].[MinDateReplacement] Converts the SAP date 00000000 to the entered value, see Custom Properties.

Parameterizing Using SSIS Variables #

SSIS variables store values that an SQL Server Integration Services package and its containers, tasks, and event handlers can use at run time.
To use SSIS variables for parameterization of Xtract components, place an @-symbol before the variable name.

The following example shows how an SSIS variable named @vDate is used as a parameter in an Xtract component:

  1. Choose an input field that supports SSIS variables.
  2. Instead of entering a fixed a value, enter an SSIS variable using the @-notation.
  3. Open the SSIS variables window and add an SSIS variable with the same name, but without the @-symbol.
  4. Make sure to select the correct data type.
  5. Use the variable by entering values directly into the Value field or by assigning dynamic values in SSIS.

Available Parameters for SSIS Variables

List of input fields that allow entry of an SSIS variable:

Field Name Comment
Package Size Sets the package size in bytes, see Extraction Settings.
Row Limit Corresponds to the field Row limit in the OHS component, see Extraction Settings.
Gateway host Corresponds to the field Gateway host in the OHS component, see Third party tool Settings.
Gateway service Corresponds to the field Gateway service in the OHS component, see Third party tool Settings.
Program ID Corresponds to the field Program ID in the OHS component, see Third party tool Settings.