Bulk load into MSSQL

Description

The Bulk load into MSSQL action bulk loads data from a file into MSSQL.

Contrary to the transform bulk loaders, this action requires a pre-existing file that is bulk loaded to MS SQL.

Options

General tab

Option Description

Action name

The name of the workflow action.

Target Table

Connection

The database connection.

Target schema

The target schema.

Target table name

The target table.

Truncate table

Whether or not to truncate the table before starting the bulk load.

Data File

Source File name

The file to bulk load into MSSQL.

Data file type

char, native, widechar, widenative

Fields terminated by

The field separation character.

Row terminated by

The row termination character.

Advanced tab

Option Description

Codepage

ACP, OEM (default), RAW or Specific. See the NOTE under Result.

Specific codepage

Code page to use when Specific is selected: a code page number such as 65001, or ACP, OEM or RAW.

Format file

Path to format file.

Browse

Browse the filesystem to choose a format file.

Fire triggers

Check constraints

Keep nulls

Keep identity

Tablock

Start at line

At which line to start.

End at line

At which line to end.

Order by

Which field to order with.

Direction

Ordering ascending (asc) or descending (desc).

Error file

Path to the error file.

Add date time

Whether or not to add the date and time.

Max errors

Batch size

Rows per batch

Add file to result filenames

Result

What this action passes on to the next action. See Action results for how results travel through a workflow.

Result Value

True

The file was loaded, with 0 errors.

False

The file doesn’t exist, the table doesn’t exist, Source File name is empty, the connection is unknown or can’t be opened, the connection is not a Microsoft SQL Server connection, Fields terminated by is empty while Data file type is char or widechar, Codepage is Specific while Specific codepage is empty or not a code page number, ACP, OEM or RAW, Error file exists while Add date time is unchecked, or SQL Server refuses the load (for example a value that doesn’t fit its column). The number of errors is 1.

Result files

The source file is added when Add file to result filenames is checked and the load succeeded. Otherwise passed on unchanged.

Result rows

Passed on unchanged.

SQL Server reads the file, so Source File name must be a path that the database server can read. The action also checks that the file exists on the machine where Hop runs, before it loads.
When the load fails on a row, the rows before it can stay in the table: with the default options, a value that doesn’t fit its column on row 2 left row 1 loaded.
SQL Server on Linux only accepts the RAW Codepage: the default, OEM, makes the load fail there.