X hits on this document

340 views

0 shares

1 downloads

0 comments

37 / 131

Load

LKM SQL to MSSQL (BULK)

Loads data from any Generic SQL source database to a Microsoft SQL Server staging area database using the native BULK INSERT SQL command.

This LKM unloads the source data in a temporary file and calls the Microsoft SQL Server BULK INSERT SQL command to populate the staging table. Because this method uses the native BULK INSERT, it is often more efficient than the “LKM SQL to SQL” or “LKM SQL to MSSQL” methods when dealing with large volumes of data.

Consider using this LKM if your source data located on a generic database is large, and when your staging area is a Microsoft SQL Server database.

Reverse- Engineer

RKM MSSQL

Retrieves metadata for MSSQL objects: tables, views and synonyms, as well as columns and constraints. This RKM reverse-engineers columns that have a user defined data type and translates the user defined data type to the native data type.

Specific Requirements

Some of the Knowledge Modules for Microsoft SQL Server use specific features of this database. The following restrictions apply when using such Knowledge Modules. Refer to the Microsoft SQL Server documentation for additional information on these topics.

Using the BULK INSERT command

  • 1.

    The file to be loaded by the BULK INSERT command needs to be accessible from the Microsoft SQL Server instance machine. It could be located on the file system of the server or reachable from a UNC (Unique Naming Convention) path.

  • 2.

    UNC file paths are supported but not recommended as they may decrease performance.

  • 3.

    For performance reasons, it is often recommended to install Oracle Data Integrator Agent on the target server machine.

Using the BCP command

  • 1.

    The BCP utility as well as the Microsoft SQL Server Client Network Utility must be installed on the machine running the Oracle Data Integrator Agent.

  • 2.

    The server names defined in the Topology must match the Microsoft SQL Server Client connect strings used for these servers.

  • 3.

    White spaces in server names defined in the Client Utility are not supported.

  • 4.

    UNC file paths are supported but not recommended as they may decrease performance.

  • 5.

    The target staging area database must have option "select into/bulk copy"

  • 6.

    Execution can remain pending if the file generated by the BCP program is empty.

  • 7.

    For performance reasons, it is often recommended to install Oracle Data Integrator Agent on the target server machine.

Using Linked Servers

Knowledge Modules Reference Guide

37/131

Document info
Document views340
Page views343
Page last viewedSun Dec 04 16:17:03 UTC 2016
Pages131
Paragraphs3942
Words42855

Comments