Skip to main content

Transferring Data from Other Applications

Executing SSIS Runs

Data can be imported to interface tables from other systems into the Clausion database for example using the SQL Server Integration Services (SSIS), component of the Microsoft SQL Server database software.

Clausion interface specialists have good experience on defining the runs, and they should at least be involved in the interface project as advisory member.

Executing SSIS run

The Clausion system includes a custom template for executing SSIS runs.

  • Mark the run with "X" in the first column of the template and click save to execute.

With the default template you may not execute several manual runs at the same time.

Adding and modifying runs

You can add and modify automatic runs on the SSISLaunch Auto worksheet of the template:

  • Add a name, description, data type, year and period for the run and save.

When you save the custom template, manual runs selected on the SSISLaunch Manual sheet are also executed. The template usage requires SSIS runs to be defined in the server. Clausion interface specialist instructs the customer in more detail during the interface implementation project.

Executing anyERP Runs

Location: AnyERP Runs (task ID: AnyERPLauncher)

Basware anyERP is used for contacting ERP systems and performing specific tasks, such as transferring data. anyERP runs are created using Basware anyERP Adapter. Clausion interface specialists define the runs.

Tasks for executing anyERP runs from Clausion client are created in User Interface. One task may include several runs which are run automatically one after another when the task is executed.

Depending on the task settings, you may be able to select which runs to execute by selecting the check box in the first column of the runs. If allowed, enter values for the parameters (depending on the task settings, you may be able to only view them). To start the run, click Execute.

Tasks for executing anyERP runs can be added to the user interface with user interface task parameters. For the parameter settings, refer to Clausion User Interface Task Parameters.

Data Uploader

The Data Uploader user interface is only available in the Clausion Web Client. The Data Uploader is functional only in Clausion Cloud.

[v24.10 and later:] There are two different types of transfers: data upload and dimension upload. [v24.10 and latercontent ends]

The standard data upload sets the document series as either 10000 or 20000 based on whether the account is an internal account or not. For internal accounts, the document series is set as 20000, otherwise as 10000.

When the document series is 20000, the amounts are multiplied by -1.

The Data Uploader UI supports also other document series in addition to document series types 1 and 2. Document number is declared in the mapping screen’s Target column with the code BKVOUCHUDID. The document number must exist in the database. For example, when reading type 7 content when the document number is 70003, that number must be found in the database.

You can receive email notifications on the status of integration run which can be Completed, Completed with Errors, or Failed. To receive this notification, insert an email address to the email field on the Basic Settings of the User Interface. After this, the configuration can be added to the IntegrationExecutorService.exe.config file by integration consultant. In addition, if an error occurs while sending out the email, the status information is recorded in DataUploader_Tenant1.log log file as well.

Customer environment .NET version must be .NET 4.8, .NET 4.8.1, or newer to support this feature.

In Clausion Cloud environments, data can be imported from other systems into Clausion using Data Uploader. The data upload settings are managed on the Data Uploader screen (task ID: IntegrationMnt).

Adding data upload settings and scheduling data uploads

Before you start

  • The imported data must be saved in a CSV file stored in a folder defined in the IntegrationExecutorService.exe.config file.

  • The file does not need to include corresponding columns for all Clausion fields, and it can also include additional columns (missing columns will receive a NULL value and additional columns will be ignored).

  • The default delimiter in the CSV file is |, but it can be changed in the IntegrationExecutorService.exe.config file.

The data upload settings include a number of predefined steps. These standard data upload options can be used as such or as a template for defining custom steps.

We do not recommend that you change the standard data upload options on your own. If you want to make changes, contact your Clausion consultant.

To add new data upload settings:

  1. Click Open to open the options.

  2. Click Add. If you are executing a data upload, add an ID for the data upload, select a data type, and save. The new data upload is added to the Data upload drop-down list. [v24.10 and later:] If you are executing a dimension upload, add an ID, select a dimension, a data type, and Structures as the integration type. Save selections. [v24.10 and latercontent ends]

  3. Under Basic settings, if you are executing a data upload, add a name for the data upload in the languages used in your system. [v24.10 and later:] If you are executing a dimension upload, add an email address for the dimension upload to which information about the upload status can be sent, and a name in the languages used in the system. [v24.10 and latercontent ends]

  4. Under Conversion tables, select the conversion tables to be used in transferring data if needed. [v24.10 and later:] Conversion tables are available only for the data upload option. [v24.10 and latercontent ends]

  5. Under Steps, define the actions to be performed and their order. The standard data upload options are included by default. If you want to make changes to them, contact your Clausion consultant.

    You can upload multiple files at the same time by adding several file reading steps to an upload run (after de-selecting the standard upload checkbox). In the database content of each source file is saved to separate raw data tables. Note that the standard interface steps process forward until the report data tables only data of the file imported in the first file reading step. Admin user or interface specialist must add steps for processing the other raw data tables.

    #FILEMOVE parameter can also be used move files to a new folder (for example, History folder) after an integration run. Please note that the integration status must be either Completed (successful) or Completed with errors.

  6. Under Schedule, you can define one or several schedules for running the data upload:

    • In the Schedule column, add the time of the run in cronstring format where fields in use are: minutes, hours, days, month, day of week. For examples, see Schedule time setting examples.

    • From the Period and Year drop-down lists, select the period and year of the data to be imported.

    • In the RunUdid2 column, you can add a value for delimiting the run, for example, an organization code.

    • In the User column, add valid user login information.

  7. Map source fields to Clausion columns. Click Mapping to open the mapping settings dialogue box and add the following:

    • Position: The position of the column in the CSV file. The position must be a numerical value.

    • Source: The source column name. The source cannot include Scandinavian and other special characters.

    • Type: The type of the column (for example, float, varchar(xx)).

    • Destination: The corresponding Clausion column, if any.

    The values of the source fields are copied as such to target columns based on the mapping.

    You can re-size the dialog box by dragging to view all columns at one glance.

  8. Save the data upload settings.

Parameters used in the Data Uploader run

Parameters starting with @ are used with SQL and parameters starting with # are used internally with Data Uploader.

Setting

Description
@INTEGRATION UDID The ID of the run given in the user interface.
@INTEGRATIONRUN_GUID The unique identifier created during the run.
@MAPPING_UDID The mapping table used by the run, cannot be modified currently.
#TABLE The table used by the step.
#FILE

The file used in the run.

You can also use a wildcard character such as the asterisk ( * ) in the file name. For example, "SAP_1000*" retrieves all files with SAP_1000 in the beginning of the file name.

#FILEMOVE

For moving files directly to a specific folder in the run.

#FILEMOVE parameter must be added in the FILESTEP after the #FILE parameter. Use format #FILEMOVE=History/SAPSALDOT*_%TIMESTAMP_INT%.csv. If %TIMESTAMP_INT% is not added, the file moves to the new folder without the time and date value as a file name suffix.

You can also use a wildcard character such as the asterisk ( * ) in the file name. For example, "SAP_1000*" moves all files with SAP_1000 in the beginning of the file name
#FILEHEADERTYPE Not in use.
#FILEDATADELIMETER The field delimiter of the file. If it has not been defined, Data Uploader's default delimiter is used.
#FILESKIPROWS Determines how many title rows are skipped from the beginning of the file.
#FILEDESTINATION Determines the destination file if fileaction =Move.
#FILEENCODING The encoding of the CSV file. Possible values are ASCII; UTF8, UTF7, UNICODE, BIGENDIANUNICODE, UTF32. The default value is retrieved from the config file.

Schedule time setting examples

Between each field value (numerical value or string) in the time setting there must be a space.

Schedule time setting examples

Setting

Explanation

0 3 * * *

3 a.m. every morning

0 3 * * 1-5

3 a.m. every morning from Monday to Friday

0 3,5 * * *

3 a.m. and 5 a.m. every morning

0 3 1 12 *

3 a.m. on day 1 in December

55 */2 1-18 * *

5 to every second hour, each months first 18 days

Running data uploads manually

Tasks for running data uploads manually can be added to the user interface with user interface task parameters. For the parameter settings, refer to Clausion User Interface Task Parameters.

To run data upload:

  1. Select the Period and Year for which you want to transfer data. The period and year can be defined for the task in the user interface task parameter settings.

  2. You can add a RunUdid2 value for delimiting the run, for example, an organization code.

  3. [v24.10 and later:] Select Integration Dimension and Integration Hierarchy for dimension data. [v24.10 and latercontent ends]

  4. Click Start.

Data upload status report

You can monitor the status of the imported data and flow logs of the executed data upload runs using the Data Upload Status custom template. Also error reports of runs that have failed or completed with errors are displayed.

To execute data upload run:

The data upload status report opens without any entries in the Flow Log column and with no data on the Run Flow Log and Run Error Report sheets.

  • Enter 'X' in the Flow Log column on the row of the run (one row at a time) and save.

When saving, the Run Flow Log and Run Error Report sheets are populated with the data according to the results.

Was this article helpful?

We're sorry to hear that.