Microsoft Azure Synapse SQL Connector

Back Next

Configuring an SQL transformation

Your mapping includes user IDs in the data flow. You want to include user names in addition to user IDs. You have a stored procedure that matches user IDs with user names in the database. You add an SQL transformation to your mapping, select the stored procedure, and map the userId incoming field with the userId input field in the stored procedure. Add an SQL transformation in a Microsoft Azure Synapse SQL mapping.

You check the Output Fields tab for the SQL transformation to confirm that it includes the username field. When you run the mapping, the username value is returned with the user ID.

Configuring a mapping with Source, Target, and SQL transformations

This example lists the tasks required to configure an SQL transformation that calls a stored procedure in Microsoft Azure Synapse SQL:

Enter a name and description for the SQL transformation.

In the Incoming Fields
tab, define field rules that determine the data to include in the transformation.

In the Properties panel of the SQL transformation, click the SQL
tab.

In the SQL
tab, perform the following tasks:

Select the connection to the database. You can select the connection or use a parameter.

Set the SQL type to Stored Procedure
.

Click Select
to select the stored procedure from the database, or enter the exact name of the stored procedure to call.

The stored procedure name is case-sensitive.

If you add a new stored procedure to the database while you have the mapping open, the new stored procedure does not appear in the list of available stored procedures. To refresh the list, close and reopen the mapping.

In the Field Mapping tab, specify how to map incoming fields to the input fields of the selected stored procedure.

Define advanced properties for the transformation according to your requirement.

To configure an SQL transformation using the SQL type as SQL entered query, see

Transformations

in the Data Integration help.

Process SQL queries and stored procedures using an SQL transformation

Download Guide

Watch

Comments

Cloud Connectivity Homepage

Communities

Knowledge Base