Skip to content

Parameterization

This page shows how to parameterize the Xtract ODP component using SSIS variables and SSIS expressions to set Data Flow Properties.

Parameterization using SSIS Variables

SSIS variables store values that an SQL Server Integration Services package and its containers, tasks, and event handlers can use at runtime.

You can use SSIS variables for selections. Available SSIS variables are listed in the selection settings when parameter input ( ) is selected, see Edit Selections.

Parameterization 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.

Note

Data Flow 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 Properties are overwritten.

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

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

Data Flow Properties of Xtract ODP

List of Data Flow Properties that can be parameterized:

Property Description
[Xtract ODP].[SubscriptionSuffix] Adds a suffix to the ODP subscription generated by the Xtract ODP component. This allows using an SSIS package in two different SSIS environments, e.g. QA and PROD. accessing the same SAP system. The suffix allows generating a subscriber for each SSIS environment from the same ODP component.
[Xtract ODP].[UpdateMode] Runs a delta initialization with or without extracting data on init, see Update Mode.
[Xtract.ODP].[ExtractDataOnDeltaInit] Runs a delta initialization with or without extracting data on init, see Update Mode.
[Xtract.ODP].[HierarchyName] Applies to Hierarchy DataSources only, see Select Hierarchy. Example: Loop over a list of hierarchies.
[Xtract.ODP].[PackageSize] Corresponds to the setting Package Size.

Custom Properties of Xtract ODP

The Custom Properties are properties that are unique to the component. When parameterizing the component using SSIS variables, the Custom Properties are overwritten.

The Custom Properties of the Xtract ODP component are displayed in the "Properties" window.

odp-properties

ConvertsDates

When this property is set to True (default) SAP date fields (YYYYMMDD) assume the SSIS pipeline type DT_DBDATE instead of DT_WSTR. For the date conversions to apply, set the property UseLegacyDateConversion to False. In case of invalid data in SAP date fields, the following date conversions are applied:

  • InvalidDateReplacement
  • MaxDateReplacement
  • MinDateReplacement

InvalidDateReplacement

Replaces an invalid date format. The default value 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

Replaces SAP dates with the year 9999. The default value 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

Replaces SAP dates with the year 0000. The default value 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.

ExtractDataOnDeltaInit

Runs a delta initialization with or without extracting data on init, see Update Mode.

HierarchyName

Applies to Hierarchy DataSources only, see Select Hierarchy. Example: Loop over a list of hierarchies. Example: Loop over a list of hierarchies.

PackageSize

Corresponds to the setting Package Size.

SubscriptionSuffix

Adds a suffix to the ODP subscription generated by the Xtract ODP component. This allows using an SSIS package in two different SSIS environments, e.g. QA and PROD. accessing the same SAP system. The suffix allows generating a subscriber for each SSIS environment from the same ODP component.

UpdateMode

Sets the update mode of an extraction. Permitted values are:

  • (F)ull
  • (D)elta
  • (R)ecovery
Last update: November 2, 2024