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