The JSON Processing components consists of two components to transform existing JSON data into column data, and two other components that transform column data into JSON structure.

With JSON components, developers have full flexibility and control over their JSON data structure. Developers are able to add, select and deselect nodes and structure their data as needed for input or output.

Now with our 2020 Release Wave 1, we've added a new "Key Value Pivot" Output Type in our JSON Extract/Source components, which can be used to work with key/value pairs values. Discover how you can use this new feature to extract key/value pair JSON structure into tabular format.
Blog Post: Extracting Key/Value Pairs JSON Structure into Tabular Format

Additionally, our JSON Source component now supports using variables as the source and using @[ResponseStatusCode] token for expression evaluation.

The JSON components, part of the SSIS Productivity Pack, supports as many data inputs and outputs as needed.

SSIS JSON Components

SSIS JSON Source

The SSIS JSON Source component allows developers to retrieve JSON documents from an HTTP URL or a local file. From there the component will then break up the structure and produce column data which can be consumed by downstream SSIS pipeline components. With JSON Source component developers are able to integrate with virtually any web API including those REST APIs with OAuth2 authentication requirements.

Also available is a JSON Extract component which is a transformation component that can be used to extract any complex JSON document or column data already in SSIS pipeline by transforming it into tabular data structure which can be further consumed by downstream SSIS pipeline components.

Either component will support as many outputs as needed based on the JSON structure to transform all necessary data into column data to use as required in the downstream pipeline.

SSIS JSON Source component
SSIS JSON Source component - design JSON
SSIS JSON Extract component

SSIS JSON Destination

Using SSIS JSON Destination, developers are able to merge input data based on the JSON data structure defined in the component. You can then specify the component to write the merged JSON document to a local file or you can send it to a HTTP URL to perform a RESTful service call.

Also available is JSON Merge component which is a transformation component that can be used within an ETL project to merge input data into JSON hierarchical structure to use in downstream pipeline.

Either component will support as many data inputs as needed to create the desired JSON hierarchical structure. The transformed JSON data can then be used for many scenarios including sending to a REST web service, or save to a file stored locally or in the cloud.

SSIS JSON Destination component
SSIS JSON Destination component - design JSON
SSIS JSON Merge component

JSON Components: data integration made easy

Our solution is easy to use, offering code-less integration between the JSON Components and virtually any other application or database system. Our solution is built utilizing Microsoft SQL Server Integration Services (SSIS), a performant and flexible ETL platform, allowing your team to take advantage of the technologies and skills they already have. The drag-and-drop user interface makes it easy to set up powerful integration within a matter of minutes.

JSON Components are available within the JSON & XML Processing components of our SSIS Productivity Pack, a large collection of premium and unique ETL tools to enable greater development productivity. We also offer several additional connectivity solutions to easily integrate JSON Components with applications such as Microsoft Dynamics 365, SharePoint, Salesforce, and many more.

See our full product offerings for more information.

Other SSIS JSON & XML Processing Components

Our JSON & XML Processing components make it easy to integrate with different services within the ETL process. Each component available has been specifically developed to expand and enhance data migration and integration processes. In addition to JSON, the JSON & XML Processing components include: