Using the JSON Extract Task
The JSON Extract Task is an SSIS control flow task component used for sending an HTTP request and receiving a response. A common use case for this component is downloading a file.
The component includes two pages to configure how the request should be sent and how the response should be processed:
- General
- Error Handling
General Page
The General page of the JSON Extract Task allows you to specify the general settings of the component.
- Connection Properties
-
- Connection Manager
-
The JSON Extract Task requires a connection in order to connect to the web service. The Connection Manager drop-down will show a list of all HTTP Connection Managers that are available to your current SSIS package.
- Code Page
-
Specify the Code Page of the file.
- HTTP Method
-
The method that will be used when sending HTTP requests. Available methods include:
- GET
- POST
- PUT
- DELETE
- PATCH
- Relative Path
-
The Relative Path field allows you to specify a string containing the relative location of the URL that the request will be sent to. The base URL comes from the connection manager. For example, if you want to download a file called "file1.txt" from http://www.example.com/ you would create a connection manager with the base URL http://www.example.com/ and in the JSON Extract Task set the Relative Path to "file1.txt".
- Request Settings
-
- Query String Parameters
-
The Query String Parameters grid allows you to add query string parameters and values that will be appended to the URL of the request.
- Requester Headers
-
The Requester Headers grid allows you to add HTTP headers that will be used for the request.
-
- Request Body -Body
- The Body field allows you to configure the body of your request. If the connection manager is using the GET method, such as when downloading files, the body would typically be left empty.
Output
The Output page lets you pick how to parse the JSON output.
- JPath: The Jpath for the JSON node can be provided here.
- Output Variable: Select the variable from a drop-down field that allows a variable to be chosen.
- Error On Not Found: Check this option to raise an error on value or node not found in Jpath.
- Expression fx Icon
-
Click the blue fx icon to launch SSIS Expression Editor to enable dynamic updates of the property at run time.
Error Handling Page
The Error Handling page allows you to specify how errors should be handled when they happen.
There are three options available.
- Fail on error
- Write error to variable: This allows you to choose the variable to write the error to.
- Ignore error