Using the Azure Functions Destination

Azure Functions Destination component can be used to execute Azure Functions using HTTP triggers. The major difference between the Source and Destination components is that Azure Functions Destination component take in input values, whereas in Azure Functions Source component, the input values are static. You can choose the items (Variables or Columns) from the Tree view and drag and drop it to any property field to merge that value at runtime.

It has three pages.

  • General
  • Columns
  • Error Handling

General

This page allows you to configure the general settings of the component.

SSIS Azure Functions Destination Component Editor - General

Connection Manager

The Azure Functions Destination Component requires a connection in order to connect the specific Azure Functions. The Connection Manager drop-down will show a list of all connection managers that are available to your current SSIS packages.

URL

The URL specifies the route of the function, and it is usually in the below format:

http://<APP_NAME>.azurewebsites.net/api/<FUNCTION_NAME>

Authorization Level

Authorization level controls whether the function requires an API key and which key to use. You can choose from the below three types depending on how it has been specified in the Azure portal for the function.

  • Function - Default value. Uses a function key, found in the 'keys' management panel on the portal.
  • Admin - Uses your master key, found in the 'keys' management panel on the portal.
  • Anonymous - No API key is required.
Authorization Code

The code of the function generated from the Azure portal is to be specified here which goes in the request header.

Method

An array of the HTTP methods to which the function responds. If not specified in the Azure portal, the function responds to all HTTP methods. You can choose from the below:

  • DELETE
  • GET
  • HEAD
  • OPTIONS
  • PATCH
  • POST
  • PUT
  • TRACE
Body

Specify the values to be in the body of the request. You can choose the Editor pane to accept the below.

  • HTML
  • JSON
  • Plain Text
  • SQL
  • XML
Query String

The Query String values can be specified here. You can click on the “+” button to add the Query String Name and the Query String Value.

Headers

Specify any header values to go with the request. You can click on the “+” button to add the Header Name and the Header Value

Preview Button

The preview button launch the Preview dialog which will send the first request based on the current settings of the component, and show a preview of the response. You can execute the function with the button “Execute Function” and get the response in the second window.

Expression fx Icon

Click the blue fx icon to launch SSIS Expression Editor to enable dynamic update of the property at run time.

Generate Documentation Icon
Click the Generate Documentation icon to generate a Word document which describes the component's metadata including relevant mapping, and so on.

Columns

The Columns page of the Azure Functions Destination Component shows you the available columns based on the settings in the Document Designer page.

SSIS Azure Functions Destination Component Editor - Columns

In the Grid, there are two columns that determines the output from the component.

  • Output Field - The Output fields that are available from the Source component.
  • Data Type - The datatype details of the output field. You click on the field to toggle it between nvarchar, ntext or image etc depending on how you need the output.
Add Response Header Column

This button can be used to ads a response header column that will be received in the responses.

SSIS Azure Functions Destination Component - Add Custom Header Column

Hide Unselected Fields
When the Hide Unselected Fields checkbox is checked unselected output columns will be hidden.
Hide Selected Fields
When the Hide Selected Fields checkbox is checked used selected columns will be hidden.

Error Handling

This page allows you to specify how errors should be handled when they happen along with the name of the output column.

SSIS Azure Functions Destination Component Editor - Error Handling

Error Handling has three options available.

  1. Fail on error
  2. Redirect rows to error output
  3. Ignore error

When the Redirect rows to error output option is selected, rows that failed to be sent will be redirected to the 'Error Output' output of the Transformation Component. As indicated in the screenshot below, the blue output connection represents rows that were successfully sent, and the red 'Error Output' connection represents rows that were erroneous. The 'ErrorMessage' output column found in the 'Error Output' may contain the error message that was reported by the component.

SSIS Azure Functions Destination - Redirect Rows to Error Output