You have the possibility to use your own SQL statement for the three different DB process steps and / or to adapt it to your requirements.

The necessary adjustments must be made for the sections Preparation, Row & Processing and Finalization in the destination settings. To do this, select an existing extraction in Xtract Universal and click on the Destination button.

Destination-Settings

In the following example, the table KNA1 is extended by a column with the current timestamp of type DATETIME. This new column is filled dynamically using a .NET-based function.

Note: The data types that can be used in the SQL statement depend on the SQL Server database version.

Custom-SQL_Prep

Start by selecting Custom SQL in the Preparation section from the drop-down menu. Then click the Edit SQL button to edit the code. From the drop-down menu, select Drop & Create and click Generate Statement. At the end of the generated statement, add the following line and confirm with OK.

[Extraction_Date] DATETIME

In the Row Processing section, the column values from SAP are processed in the previously created columns of the SQL target table. This SQL statement is therefore left on the standard Insert as an SQL statement. At this point, no data is written from the SAP source system, but NULL values are written to the newly created Extraction_Date column.

In the last section Finalization, the NULL values are filled with the following SQL statement of the current date of the extraction and written to the SQL target table by the T-SQL command UPDATE. Confirm the input with OK.

UPDATE [dbo].[KNA1] 
SET [Extraction_Date] = '#{DateTime.Now}#' 
WHERE [Extraction_Date] IS NULL;

Custom-SQL_Final

SQL Server View of table KNA1 with the extended column Extraction_Date.

Custom_SQL_SQL_Server_Ausgabe

Creation of the SQL table ExtractionStatistics

This table provides an overview and status of the executed Xtract Universal extractions.

To do this, create an SQL table according to the following example:

CREATE TABLE [dbo].[ExtractionStatistics](
	[TableName] [nchar](50) NULL,
	[RowsCount] [int] NULL,
	[Datestamp] [nchar](50) NULL,
	[RunState] [nchar](50) NULL
) ON [PRIMARY]
GO

The ExtractionStatistics table is filled in the Finalization DB process step using the following SQL code:

INSERT INTO [ExtractionStatistics]
(
     [TableName], 
     [RowsCount], 
     [Datestamp],
     [RunState]
)
VALUES
(
     '#{Extraction.TableName}#', 
     '#{Extraction.RowsCount}#', 
     '#{Extraction.Datestamp}#',
     '#{Extraction.RunState}#'
);