Azure SQL - Bulk Extract

On this Page

- 1.1 Settings
2 Examples
- 2.1 Basic Use Case
- 2.2 Typical Snap Configurations
- 2.3 Advanced Use Case
- 2.4 Downloads

Snap type:

Read

Description:

This Snap reads the data from the SQL server table on the Azure. It extracts the complete table data from the SQL Server DB and writes it to a locally created temporary data file. The data is then processed from the data file and written to the output view. This Snap uses the bcp utility program internally to perform the bulk load action.

Snaps that require bcp utility must be set up within your Groundplex configuration. The SnapLogic Platform does not support the installation of utilities or processes on Cloudplexes. Learn more.

Input & Output

Input: This Snap can have an upstream Snap that can pass a document output view. Such as Structure or JSON Generator.
Output: The Snap outputs one document specifying the records extracted. Any error occurred during the process is routed to the error view.

Modes

Works in Ultra Tasks.

Prerequisites:

Bulk Extract requires a minimum of SQL Server 2016 to work properly.

Must install the BCP utility.

Limitations and Known Issues:

The Azure SQL - Bulk Extract Snap fails when using the Active Directory Service Principal Certificate Authentication type because the BCP utility does not support it. We recommend that you use Active Directory Password authentication to connect to Azure SQL.

Configurations:

Account & Access

This Snap uses account references created on the Accounts page of SnapLogic Manager to handle access to this endpoint. See Configuring Azure SQL Accounts for information on setting up this type of account.

Active Directory-based authentication is not supported in Bulk Extract.

Views

Input	This Snap has exactly one document input view.
Output	This Snap has exactly one document output view.
Error	This Snap has at most one document error view and produces zero or more documents in the view.

Settings

Label

Required. The name for the Snap. You can modify this to be more specific, especially if you have more than one of the same Snap in your pipeline.

Schema Name

The database schema name. In case it is not defined, then the suggestion for the Table Name will retrieve all tables names from all the schemas. The property is suggestible and will retrieve available database schemas during suggest values.

The values can be passed using the pipeline parameters but not the upstream parameter.

Example: SYS
Default value: [None]

Table Name

Required. Table on which to execute the bulk load operation.

The values can be passed using the pipeline parameters but not the upstream parameter.

Example: people

Default value: [None]

Currently, the BCP utility in the Linux environment has a limitation while processing the table names.
When loading the data into a selected table and If the table name contains the characters '$%' or '!$', the combination works fine, however, BCP does not support if the table name contains the characters vice-a-versa as'%$' and '$!'.

Examples:
Supported by BCP: "dbo"."sqldemo#^&$%"

Not supported by BCP: "dbo"."sqldemo#^&%$"

BCP absolute path

Absolute path of the BCP utility program in JCC's file system. If empty, the Snap will look for it in JCC's environment variable PATH.

Default value: [None]

The path to the BCP executable should include the ".exe" extension to ensure the executable is actually referenced.

Currently, the BCP utility in the Linux environment has a limitation while processing the table names.
When loading the data into a selected table and if the table name contains the characters '$%' or '!$', the combination works fine, however, BCP does not support if the table name contains the characters vice-a-versa as'%$' and '$!'.

Examples:
Supported by BCP: "dbo"."sqldemo#^&$%"
Not supported by BCP: "dbo"."sqldemo#^&%$"

Maximum error count

Required. The maximum number of rows which can fail before the bulk load operation is stopped.

Default value: 10

Enable UTF-8 encoding

Specify whether UTF-8 coding must be enabled or not. If enabled, the Snap updates the BCP command to support UTF-8 encoded characters.

Default value: Selected

Snap Execution

Select one of the three modes in which the Snap executes. Available options are:

Validate & Execute: Performs limited execution of the Snap, and generates a data preview during Pipeline validation. Subsequently, performs full execution of the Snap (unlimited records) during Pipeline runtime.
Execute only: Performs full execution of the Snap during Pipeline execution without generating preview data.
Disabled: Disables the Snap and all Snaps that are downstream from it.