You can use this Snap to execute an SQL Server bulk load. This Snap uses the bcp utility program internally to perform the bulk load action. The input data is first written to a temporary data file, and then the bcp utility program loads the data from the data file into the target table.

Supported Accounts

• SQL Server Account
• SQL Server Dynamic Account

• This is a Write-type Snap.

Snap Type

The SQL Server - Bulk Load Snap is a Write-type Snap that inserts bulk data in one request.

Prerequisites

• Install the BCP utility on the Groundplex nodes where you want to execute this Snap.

1. Download and install the BCP Utility in your Windows or Linux environment.
   1. Installing BCP on Linux
   2. Installing BCP on Windows
2. Verify that you can run the bcp command. To verify BCP installation, enter bcp on the terminal or the command line console and press Enter.

   /u01/sqlncli> bcp

   The output should look similar to the following. These are the command-line options that can be used with the BCP utility. If you see this output, it indicates that the BCP utility is installed and ready for use.

   
    [-a packet_size]
       [-b batch_size]
       [-c]
       [-C { ACP | OEM | RAW | code_page } ]
       [-d database_name]
       [-e err_file]
       [-E]
       [-f format_file]
       [-F first_row]
       [-G Azure Active Directory Authentication]
       [-h"hint [,...n]"]
       [-i input_file]
       [-k]
       [-K application_intent]
       [-l login_timeout]
       [-L last_row]
       [-m max_errors]
       [-n]
       [-N]
       [-o output_file]
       [-P password]
       [-q]
       [-r row_term]
       [-R]
       [-S [server_name[\instance_name]]
       [-t field_term]
       [-T]
       [-U login_id]
       [-v]
       [-V (80 | 90 | 100 | 110 | 120 | 130 ) ]
       [-w]
       [-x]
             

3. Ensure the path to the bcp command is correctly provided in the Snap.

Support for Ultra Pipelines

• Does not support Ultra Tasks.

Behavior Change

• Before the 4.33patches21119 release, empty strings and null values were treated as null when loaded into the SQL server. However, starting from the 433patches21119 release, data in the format of an empty string inserted into a string-based column is stored as an empty string in the SQL server. Similarly, inserting null data into a string-based column is stored as null in the SQL server.

  To ensure consistent handling of both empty strings and null values, we recommend you to update the data to match how you would like it to be represented in the database before performing a bulk load operation.

Known Issues

The SQL Server - Bulk Load Snap returns 0 rows copied for tables containing spatial-type columns.

Snap views

Snap settings

bcp.exe %* -C 1252

Table Creation

When attempting to load data, if the specified table does not exist and the Create table if not present checkbox is selected, the Snap creates the table along with the necessary columns and data types to accommodate the values in the first input document. If you want the table to have the same structure as a source table, you can connect the second output view of a Select Snap to the second input view of this Snap. The additional view in the Select and Bulk Load Snaps transmits metadata about the table, enabling the replication of a table from one database to another.

The metadata document, read by the second input view, contains a snapshot of the JDBC DatabaseMetaData class. By manipulating this document, you can modify the generated 'CREATE TABLE' statement. For instance, if you wish to rename the 'name' column to 'full_name,' you can utilize a Mapper (Data) Snap that sets the path '$.columns.Name.COLUMN_NAME' to 'full_name'. The document encompasses the following fields:

• columns - Contains the result of the getColumns() method with each column as a separate field in the object. Changing the COLUMN_NAME value will change the column's name in the created table. Note that you do not need to change the field name in the row input documents if you change a column name. The Snap automatically translates from the original name to the new name. For example, when changing from name to full_name, the name field in the input document will be put into the "full_name" column. You can also drop a column by setting the COLUMN_NAME value to null or the empty string. The other fields of interest in the column definition are:
  • TYPE_NAME - The type to use for the column. If this type is not known to the database, the DATA_TYPE field will be used as a fallback. If you want to explicitly set a type for a column, set the DATA_TYPE field.
  • _SL_PRECISION - Contains the result of the getPrecision() method. This field is used along with the _SL_SCALE field for setting the precision and scale of a DECIMAL or NUMERIC field.
  • _SL_SCALE - Contains the result of the getScale() method. This field is used along with the _SL_PRECISION field for setting the precision and scale of a DECIMAL or NUMERIC field.
• primaryKeyColumns - Contains the result of the getPrimaryKeys() method with each column as a separate field in the object.
• declaration - Contains the result of the getTables() method for this table. The values in this object are just informational at the moment. The target table name is taken from the Snap property.
• importedKeys - Contains the foreign key information from the getImportedKeys() method. The generated CREATE TABLE statement will include FOREIGN KEY constraints based on the contents of this object. Note that you will need to change the PKTABLE_NAME value if you changed the name of the referenced table when replicating it.
• indexInfo - Contains the result of the getIndexInfo() method for this table with each index as a separate field in the object. Any UNIQUE indexes in here will be included in the CREATE TABLE statement generated by this Snap.

Troubleshooting

bcp.exe %* -C 1252