Microsoft SQL Server BULK INSERT Configuration

Overview

For performances purposes, Semarchy xDM Data Integration supports using Microsoft SQL Server BULK INSERT statement in load Templates.

This loader offers great performances when loading data into Microsoft SQL Server.

Semarchy xDM Data Integration extracts source data into temprary files and then load them through BULK INSERT.

This page explains how to configure Semarchy xDM Data Integration for BULK INSERT.

BULK INSERT Usage

To use BULK INSERT:

  1. Configure your Microsoft SQL Server Metadata with an FTP link if you are targetting a remote database.

  2. Select the appropriate Template in your Mapping

  3. Select the appropriate load method to use BULK INSERT.

  4. Run your Mapping

Configure the Metadata

Semarchy xDM Data Integration extracts source data into temprary files and then load them through BULK INSERT.

BULK INSERT statement requires the temporary files to be located on the same machine as the Microsoft SQL Server database.

  • If your Semarchy xDM Data Integration Runtime is installed on the same machine as the Microsoft SQL Server, the Mapping will work seamlessly, as the generated files will be accessible. You can jump to the next section.

  • If your Semarchy xDM Data Integration Runtime is not installed on the same machine, we’ll have to transfer the file on the remote Microsoft SQL Server database machine first. This can be done automatically through FTP as explained below.

Send the temporary files automatically through FTP

If your Semarchy xDM Data Integration Runtime is not installed on the same machine as the Microsoft SQL Server you can configure the Metadata to automatically send the temporary files through FTP.

Make sure that an FTP Server is accessible on the has to be installed on the same machine as the Microsoft SQL Server database.

Then, create an FTP Metadata and directory:

bulk insert configuration metadata ftp

The Absolute Path must be set. The template will use this value to find the file.

Next, drag and drop the directory on the Microsoft SQL Server Metadata. If you put it on the schema, you’ll be able to use the BULK INSERT for all the tables. Otherwise put it only on the table on which you need it.

bulk insert configuration metadata link

Rename the FTP Metadata Link to TARGET_FTP. The name is important for the Template to recognize it.

bulk insert configuration metadata link rename

Finally, make sure that in your Mapping, the "Enable File Transport" parameter is enabled on the Template.

Troubleshooting

Error - Access is denied

During execution you might encounter this exception:

com.microsoft.sqlserver.jdbc.SQLServerException: Cannot bulk load because the file "C:\Temp\test\data_L1_T_CUSTOMER_BULKLOADED.dat" could not be opened. Operating system error code 5(Access is denied).

This is due to Microsoft SQL Server not being able to open or modify the file because of permission issues. Check if Microsoft SQL Server has read/write permission on the folder containing the file.