Importing Data from an External Application

You can import external data into IBM® Controller from a source outside the Controller application using an ETL tool or other reporting solutions.

For example, you can create batch jobs for importing data or check the status of a batch job. The import is performed through stored procedures and staging tables that are installed with Controller.

From the external application, you can insert rows with information from a GL system into a staging table in the Controller database and place it in the Controller batch queue to be run immediately or later on. For more information on how to set up the report, see Parameters for Importing Data to the Controller Batch Queue.

You can also publish external data to a datamart, for more information see Publish Datamarts.

Because an import is logged as successful if at least one record is imported, always check the log report to see if there are records that were not imported successfully.

Staging Tables in the Controller Database

These tables can be used to import data into Controller.

Table 1. Staging tables used to import data into Controller
Table name Description
XSTAGEFACT Used for import of data
XSTAGEACC Used for import of accounts
XSTAGECOMP Used for import of companies
XSTAGECURRRATES Used for import of currency rates
XSTAGEDIM1 Used for import of extended dimension 1
XSTAGEDIM2 Used for import of extended dimension 2
XSTAGEDIM3 Used for import of extended dimension 3
XSTAGEDIM4 Used for import of extended dimension 4

Parameters for Importing Data to the Controller Batch Queue

These are the parameters to use when exporting data from another application into the Controller staging tables. To use these parameters, call the stored procedure named usp_triggerimportbatchjobs.

Note that the Batch Id is set automatically by Controller. For more information, see The Batch Queue.

Table 2. Parameters for importing data to the Controller batch queue
Number Name Description
1 pImpId Import ID. This is the same ID that will be written to the st_id column in the staging table. This process will populate that column.

Data type: nvarchar(30)

2 pImpSpec Import specification name. This name must match a predefined import specification in IBM Controller.

Data type: varchar(12)

3 pImpSpecType Import specification type: D = data A= account R = currency rates C = company 1 = dim 1 2 = dim 2 3 = dim 3 4 = dim 4

Data type: varchar(1)

4 pImpSpecParams Import specification parameters. This parameter is required only if your import specification is defined to require prompted information. The parameter must include the response required to the prompt question(s).

Data type: nvarchar(255)

5 pCtrlUser The IBM Controller user name.

Data type: varchar(8)

6 pSchedType Type of scheduling:

0 = On hold

1 = Immediately

2 = One time only

Data type: Int

8 pExecTime The date and time when the batch job should be executed. The date and time format should be the same as the date and time settings of the database.

Data type: Datetime