Versions Compared

Version Old Version 24 New Version Current
Changes made by

Gouri Bhagchandani

Kalpana Malladi

Saved on

Key

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

...

Table of Contents
minLevel1
maxLevel2
absoluteUrltrue

Overview

You can use this Snap to execute stored procedures in the Azure Synapse SQL Database.

...

Snap Type

The Azure Synapse SQL - Stored Procedure Snap is a WRITE-type Snap.

Prerequisites

  • Valid Azure Synapse SQL Account.

  • Access to the Azure Synapse database and the required permissions to write.

Support for Ultra Pipelines  

Works in Ultra Pipelines. 

Limitations

  • Multiexcerpt include macro
    nameAzure_Synapse_SQL_Driver_Limitation
    templateData[]
    pageAzure Synapse SQL - Bulk Load
    addpanelfalse

  • This Snap depends on metadata for executing Stored Procedures. There is a JDBC limitation to access System stored procedure metadata. Therefore, you cannot use this Snap to execute the system Stored Procedures.

  • This Snap does not display any error when you use invalid Stored Procedures. Therefore, we recommend that you use the Suggestions list to populate the available stored procedures list from the server.

Known Issues

None.

Snap Views

Type

Format

Number of Views

Examples of Upstream and Downstream Snaps

Description

Input 

Document

  • Min: 0

  • Max: 1

  • Mapper

  • JSON Generator

If an input view is defined, then values from an incoming document can be used as input parameters to the stored procedure.

Output

Document

  • Min: 2

  • Max: 2

  • Mapper

  • JSON Formatter

If the procedure contains any OUT parameters, they are written to the output view.

Error

Error handling is a generic way to handle errors without losing data or failing the Snap execution. You can handle the errors that the Snap might encounter when running the Pipeline by choosing one of the following options from the When errors occur list under the Views tab:

  • Stop Pipeline Execution: Stops the current Pipeline execution when the Snap encounters an error.

  • Discard Error Data and Continue: Ignores the error, discards that record, and continues with the remaining records.

  • Route Error Data to Error View: Routes the error data to an error view without stopping the Snap execution.

Learn more about Error handling in Pipelines.

Snap Settings

Info

  • Asterisk ( * ): Indicates a mandatory field.

  • Suggestion icon ((blue star)): Indicates a list that is dynamically populated based on the configuration.

  • Expression icon ((blue star) ): Indicates the value is an expression (if enabled) or a static value (if disabled). Learn more about Using Expressions in SnapLogic.

  • Add icon ( (blue star) ): Indicates that you can add fields in the field set.

  • Remove icon ( (blue star)): Indicates that you can remove fields from the field set.

Field Name

Field Type

Description

Label*

Default Value: Azure Synapse SQL - Stored Procedure
Example: Azure Synapse SQL - Stored Procedure

String

Specify a unique name for the Snap.

Schema Name

Default Value: N/A
Example: dbo

String/Expression/Suggestion

Specify the schema name of the database where the Stored Procedure resides.

Package Name

Default Value: N/A
Example: "snapdedicatedsql"

String/Expression/Suggestion

Specify the package name that contains the Stored Procedures.

Stored Procedure Name

Default Value: N/A
Example: SelectAllEmployee

String/Expression/Suggestion

Specify the Stored Procedure name to execute. If a package is defined, then it will only return the procedures of that package. If a schema is defined and no package is defined, then the Snap returns all procedures of that schema.

Parameter Values

Use this field set to specify advanced parameters that you want to include in the request.

Parameter Value

Default Value: None
Example: TEST_VALUE1

String/Expression

Select the input parameter values for the stored procedure. The parameter values are bound to the input columns in the order they appear in the list. Therefore, you must define the values in the same order as they appear in the Stored Procedure definition.
For example, a stored procedure named createGreeting has three parameters p1, p2, and p3.  
p1 and p2 input parameters are of type VARCHAR2. Then you need to provide two string values.  The first value goes to p1 and the second to p2.

Note

Do not use quotation marks when setting input parameters unless they are part of the input value itself, because they are treated as escaped plain characters. For example, if the desired input value is TEST_VALUE1, set the input parameter as TEST_VALUE1, if you set it as "TEST_VALUE1", it is recognized as \"TEST_VALUE1\".

Snap Execution

Default Value: 
Example: Validate & Execute

Dropdown list

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.

Example

Calling a Stored Procedure

This example Pipeline demonstrates how to call a simple stored procedure.

...

Step 3: Connect the File Writer Snap to the JSON Formatter Snap to write the employee.json to the SnapLogic database.

...

Download this Pipeline. 

Downloads

Info

  1. Download and import the Pipeline into SnapLogic.

  2. Configure Snap accounts as applicable.

  3. Provide Pipeline parameters as applicable.

Attachments
patterns*.slp, *.zip
sortByname

Snap Pack History

Expand

Insert excerpt
Azure Synapse SQL Snap Pack
Azure Synapse SQL Snap Pack
nameAzure_Synapse_SQL_SPH
nopaneltrue

...

Related Content