Microsoft SQL Server
This page shows how to connect to Microsoft SQL Server and write SAP data to a Microsoft SQL Server Database or a Microsoft Azure SQL Database. The setup in Xtract Universal.iQ consists of 3 main steps:
Connect to an SQL Server
To create a connection to a Microsoft SQL Server:
- Open the Connections menu.
- Click [Add Connection].
- Enter a name in the Connection Name field, e.g., the name of your server.
- Select Microsoft SQL Server from the Connection Type dropdown. The corresponding settings open.
- Enter the server address of the Microsoft SQL server in the system settings, e.g.,
myServer.exampleor192.168.1.1. - Select a connection method in the system settings and enter the corresponding endpoint.
- Select a connection method in the authentication settings and enter the corresponding credentials.
- Enter the name of an existing database in the database settings.
- Click [Test Connection] to validate the connection parameters. A status message opens in the bottom right corner of the window.
- If the connection is successful, click [Save].
The connection is now listed in the Connections menu and can be assigned to services.
Next, assign the destination to a service.
Assign Microsoft SQL Server to a Service
To assign the Microsoft SQL Server destination to a service:
- Open the Services menu.
- Select one or more services.
- Click [ Edit].
- Select an existing Microsoft SQL Server connection from the Destination dropdown.
- Click [Save].
When running the service, the data is written to the Microsoft SQL Server database.
Next, check if the service-specific destination settings match your needs.
Connection Settings
Connection settings define how Xtract Universal.iQ connects to Microsoft SQL Server. To open the connection settings:
- Open the Connections menu.
- Select a connection of type Microsoft SQL Server.
- Click [ Edit].
The connection settings of the Microsoft SQL Server connection open and can be modified.
System
The System section contains settings that define the Microsoft SQL Server to connect to.
Server Name
Enter the host address of the SQL Server using the following syntax:
| Syntax | Example |
|---|---|
[ServerName] | myServer |
[ServerName].[Domain] | myServer.example |
[IP Address] | 192.168.1.1 |
Port
Enter the port number for the SQL Server connection. The default SQL Server port is 1433.
Authentication
The Authentication section contains settings that define how to connect to Microsoft SQL Server.
Authentication Method
The following authentication methods are supported for Microsoft SQL Server:
| Authentication Method | Description |
|---|---|
| SQL Server Authentication | Connects to Microsoft SQL Server using user credentials. The connection is not encrypted. |
| SQL Server Authentication (TLS) | Connects to Microsoft SQL Server using user credentials and Transport Layer Security encryption. This option adds the parameters Encrypt = On and TrustServerCertificate = Off to the connection string, see Microsoft Documentation: TLS considerations for database connectivity. For more information about the encryption, see Microsoft Documentation: Enable Encrypted Connections to the Database Engine. |
User name
The username for the SQL Server authentication.
Password
The password for the SQL Server authentication.
Database
The Database section contains settings that define which Microsoft SQL Server database to connect to.
Database
Enter the name of the Microsoft SQL Server database to connect to.
Test Connection
Click [Text Connection] to validate your connection settings. A status message is displayed at the bottom right corner of the screen.
Destination Settings
Destination settings define how and where a service saves its output data, such as the target folder and filename. To open the destination settings:
- Open the Services menu.
- Select a service that uses the Microsoft SQL Server connection.
- Click [ Edit]. The service settings open.
- Open the tab Destination Settings.
The destination settings for Microsoft SQL Server are open and can be modified.
File Name
Determines the name of the target table. The following options are available:
| Option | Description |
|---|---|
| Same as service name | Adopt the name of the service. |
| Custom | Define a name of your choice in the Custom File Name input field. |
Append timestamp
Adds a timestamp in the format _YYYY_MM_DD to the filename of the service.
Column Name Format
Defines the format of the column name. The following options are available:
| Option | Description |
|---|---|
| Code | The SAP technical column name is used as column name in the destination e.g., MAKTX. |
| PrefixedCode | The SAP technical column name is prefixed by SAP object name and the tilde character e.g., MAKT~MAKTX |
| CodeAndText | The SAP technical column name and the SAP description separated by an underscore are used as column name in the destination e.g., MAKTX_Material Description (Short Text). |
| TextAndCode | The SAP description and the SAP technical column name description separated by an underscore are used as column name in the destination e.g., Material Description (Short Text)_MAKTX. |
| Text | The SAP description is used as column name in the destination e.g., Material Description (Short Text). |
Processing
The Processing section defines how data is written to the database using preparation, row processing and finalization steps.
Preparation
The preparation step defines the action on the target database before the data is inserted into the target table.
| Option | Description |
|---|---|
| Drop & Create | Remove table if available and create new table (default). |
| Truncate Or Create | Empty table if available, otherwise create. |
| Create | Create table if not available. |
| Prepare Merge | Prepares the merge process and creates e.g. a temporary staging table, see Merge Data. |
| None | No action. |
| Custom SQL | Here you can define your own script. |
Row Processing
The row processing step defines how the data is inserted into the target table.
| Option | Description |
|---|---|
| Insert | Insert records (default). |
| Fill merge staging table | Insert records into the staging table. |
| None | No action. |
| Custom SQL | Define your own script. |
Finalization
The finalization step defines the action on the target database after the data has been successfully inserted into the target table.
| Option | Description |
|---|---|
| Finalize Merge | Closes the merge process and deletes the temporary staging table, for example. |
| None | No action (default). |
| Custom SQL | Define your own script. |
Transaction Style
The transaction style defines how the data is transferred to the target database.
| Option | Description |
|---|---|
| One Transaction | Preparation, Row Processing and Finalization are all performed in a single transaction. Advantage: Clean rollback of all changes. Disadvantage: Possibly extensive locking during the entire extraction period. Recommendation: Only use One Transaction in combination with DML commands, e.g., "truncate table" and "insert". Using DDL commands commits the active transaction, causing rollback issues for the steps after the DDL command. Example: if a table is created in the preparation step, the opened "OneTransaction" is committed and a rollback in the next steps is not performed correctly. |
| Three Transactions | Preparation, Row Processing and Finalization are each executed in a separate transaction. Advantage: Clean rollback of the individual sections, possibly shorter locking phases than with One Transaction (e.g. with DDL in Preparation, the entire DB is only locked during preparation and not for the entire extraction duration). Disadvantage: No rollback of a previous step possible (an error in Row Processing only rolls back changes from Row Processing, but not Preparation). |
| RowProcessingOnly | Only Row Processing is executed in a transaction. Preparation and Finalization run without an explicit transaction (implicit commits). Advantage: DDL in Preparation and Finalization for DBMS platforms that do not allow DDL in explicit transactions, e.g., AzureDWH. Disadvantage: No rollback of Preparation or Finalization. |
| No Transaction | No explicit transactions. Advantage: No transaction management required by the DBMS (locking, transaction logs, etc.). This means no locking and possible performance advantages. Disadvantage: No rollback. |
Enable Debug Mode
Warning
Performance decrease!
The performance decreases when bulk insert is disabled. Disable the bulk insert only when necessary, e.g., upon request of the support team.
Enabling the checkbox Enable debug mode (slower, more detailed error messages), deactivates the default bulk insert when writing to a database.
This option enables detailed error analysis, if certain data rows cannot be persisted on the database. Possible causes for the incorrect behavior are incorrect values regarding the stored data type.
Disable the checkbox after successful error analysis, otherwise the performance of the database write processes remains low.
Note
Bulk operations are not supported when using Custom SQL statements, e.g., in Row Processing. Bulk operations lead to performance decrease. To increase performance when using Custom SQL statements, it is recommended to perform the custom processing in the Finalization step.
Date Conversion
Convert SAP Dates
Converts the SAP date format (YYYYMMDD) to a regular date format (YYYY-MM-DD), e.g., 19900101 becomes 1990-01-01. Target data uses a real date data-type and not the string data-type to store dates.
Replace SAP Dates 0000XXXX with
Converts the SAP date 00000000 to the entered value.
Replace SAP Dates 9999XXXX with
Converts the SAP date 9999XXXX to the entered value.
Replace Invalid SAP Dates with
If an SAP date cannot be converted to a valid date format, the invalid date is converted to the entered value. NULL is supported as a value. When converting the SAP date the two special cases 00000000 and 9999XXXX are checked first.
Related Links
- Knowledge Base Article: Post-Processing Column Name Style
- Knowledge Base Article: Merge Data in Database Destinations
- Knowledge Base Article: Collation Settings for MSSQL Server Destination