Versions Compared

Old Version 28

changes.mady.by.user Anand Vedam

Saved on

compared with

New Version 29

changes.mady.by.user Kalpana Malladi

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

On this Page

Table of Contents
maxLevel2
excludeOlder Versions|Additional Resources|Related Links|Related Information

Troubleshooting:
Snap type:

Read

Description:

This Snap reads the data from the SQL server table on the Azure.

ETL Transformations & Data Flow

This Snap enables the following ETL operations/flows:

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.

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

Prerequisites:

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

Must install the BCP utility.


Limitations and Known Issues:

None


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.

Note

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


Views

InputThis Snap has exactly one document input view.
OutputThis Snap has exactly one document output view.
ErrorThis 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.

Note

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.

Note

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

Example: people

Default value:  [None]


Note
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]

Note

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


Note
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

Multiexcerpt include macro
nameSnap Execution
pageAnaplan Read

Multiexcerpt include macro
nameSnap_Execution_Introduced
pageAnaplan Read


Examples

Basic Use Case

In this pipeline, the Bulk Extract Snap retrieves the data from a table on the Azure Database. The output view in parsed in a JSON format.

  1. Extract: BulkExtract reads the data from the Azure Sql Database.

  2. Transform: JSON Formatter parses the data and converts it into JSON.

    

The execution displays the following output:

|

Typical Snap Configurations

The key configurations for the Snap are:

  • Without Expression: Directly passing the table name from which the data is extracted.

  • With Expressions

    • Query from an upstream Snap: The Mapper Snap passing the required Schema and Table name to the Snap.


  • Pipeline Parameter: Pipeline parameter set to pass the required Schema and Table name to the Snap.


Advanced Use Case

The following describes a pipeline, with a broader business logic involving multiple ETL transformations, that shows how typically in an enterprise environment, Bulk import/read functionality is used.

Pipeline download link is available below.

This pipeline reads and moves data from the Azure SQL database to the Redshift using the Redshift BulkLoad Snap. The data is migrated to Redshift, and the Snap helps in achieving this with high performance and ease of use. 

  1. Extract: BulkExtract reads the data from the Azure SQL Database.

  2. Transform: JSON Formatter parses the data and converts it into JSON.

  3. Load: Redshift BulkLoad will loads the data being formatted using the JSON Parser.   


Downloads

Attachments
patterns.*slp, .*zip

Insert excerpt
Azure SQL Snap Pack
Azure SQL Snap Pack
nopaneltrue