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.

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.
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

Option

Description

Codepage

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

Specific codepage

Code page to use when Specific is selected.

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, or SQL Server refuses the load (for example a value that doesn’t fit its column). The number of errors is 1. The action is also false, but with 0 errors, when the connection is not a Microsoft SQL Server connection, when Fields terminated by is empty while Data file type is char or widechar, and when Error file exists while Add date time is unchecked. The reason is logged as an error.
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.

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, or SQL Server refuses the load (for example a value that doesn’t fit its column). The number of errors is 1.

The action is also false, but with 0 errors, when the connection is not a Microsoft SQL Server connection, when Fields terminated by is empty while Data file type is char or widechar, and when Error file exists while Add date time is unchecked. The reason is logged as an error.

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. In this version, Specific sends the word Specific to SQL Server instead of the value of Specific codepage, so the load fails.

When both Start at line and End at line are set, only End at line is used: the load starts at the first line.