SSIS JSON Source
Turn JSON files and REST API responses into clean tabular data for your SSIS data flow.
Read local files, REST API responses, or JSON stored in an SSIS variable. Extract nested data with JSONPath, configure authentication and pagination, and send rows to SQL Server or another SSIS destination, all without a script component.
The trial includes JSON Source and every other PowerPack component
- Streams very large JSON files
- Runs in your environment
- Also runs in Azure Data Factory
- OAuth, paging, TLS 1.2
At a glance
What JSON Source can do
JSON Source reads local files, web URLs, or a JSON string, then flattens nested arrays with JSONPath so you can load SQL Server like any other SSIS source, with no script component needed.
Nested JSON & JSONPath
Point JSONPath at the array you want, such as $.Branches[*] or $.value[*], and get rows.
- De-normalize nested objects and arrays into a flat, table-like layout
- JSONPath to pick any hierarchy or array
- Select Filter UI shows the JSON structure (arrays marked with their own icon) so you can pick a hierarchy visually
Array flattening & pivoting
De-normalize nested JSON into a flat document just like a regular database table.
- Array flattening (child items as columns)
- 2D arrays, pivoting, and regex filters
- Extract multiple arrays from one JSON file or API response (with JSON Parser Transform)
OAuth 1.0 & 2.0
The security standard adopted by major cloud services such as Google API, Facebook, Salesforce, Dynamics CRM, and Office 365.
- OAuth 1.0 and 2.0 (Google, Salesforce, Office 365, and more)
- Pass credentials through the OAuth Connection Manager
Basic, token & HMAC
Call APIs with Basic authorization (UserID / Password) or a dynamic token; HMAC covers APIs like Azure CosmosDB / DocumentDB.
- HTTP Basic and dynamic token
- HMAC (SHA256 / SHA512 / MD5)
- Basic authorization (UserID / Password), OAuth 1.0 and 2.0, or a dynamic token, via a connection
Headers, cookies & TLS
Set the URL, HTTP method, headers, and body; add cookies or a proxy when the service requires it.
- Custom headers, cookies, and proxy
- HTTPS with TLS 1.2 (SSIS 2014 or higher) or TLS 1.3
REST API pagination
Loop through every page until the API reports the last page.
- Paginate via URL parameter, body, or header
- Detect the last page by row count, status code, error message, or response size
- OData paging patterns
Large-file streaming
Streams very large JSON files record by record.
- Streaming mode instead of loading the whole file into memory, avoiding OutOfMemory errors
- Append
--FASTto the filter (e.g.$.Root.Row--FAST) to turn on streaming - Design with a small sample file, then point at the full file at runtime
- Metadata editor with scan-row control and lock
SQL Server 2012–2025 & ADF
Runs in your environment on SQL Server 2012–2025 and Azure-SSIS.
- SQL Server 2025, 2022, 2019, 2017, 2016, 2014, 2012 (32/64-bit)
- Azure Data Factory (SSIS IR)
- Design packages in Visual Studio 2017, 2019, 2022, or 2026
- Windows only (64-bit OS): Windows 7–11 or Windows Server 2012–2025
Files, URLs & JSON strings
Read JSON files (single or multiple), call a REST API, or parse a JSON string, then preview sample data.
- File mode: one file or a wildcard such as
*.jsonfor files with a similar structure - API mode: specify the URL, HTTP method, headers, and body
- Direct string mode: paste JSON or read it from an SSIS variable
See all features
- Read data from JSON files or RESTful API services (such as Twitter, Zendesk)
- De-normalize nested JSON into a flat, table-like layout, just like a regular database table
- Fast performance with large files (see how we processed 3 million rows in 3 mins from a 1.2 GB XML file)
- Extract a nested hierarchy or array using a JSONPath expression (see also: Extract multiple arrays using JSON Parser Transform)
- Support for OAuth 1.0 and 2.0, the security standard used by major cloud services (e.g. Google API, Facebook, Salesforce, Dynamics CRM, Office 365, MailChimp, and many more)
- Call OData services (read more)
- Call the Amazon AWS API (e.g. EC2, API Gateway, Lambda and more)
- Call APIs using Dynamic Token authentication or Basic authorization (i.e. UserID / Password)
- Loop through multiple files using a wildcard pattern (e.g.
*.json) - Support for array flattening (child array items as columns)
- HTTP requests with custom headers
- Support for HMAC authentication (SHA256, SHA512, MD5) for APIs like Azure CosmosDB / DocumentDB
- REST API pagination support to loop through multiple requests (explained here and here)
- Support for 2D arrays and complex transformations (see this article and this for Google BigQuery)
- Support for pivoting (convert columns to rows)
- Output raw JSON from an inner array, with optional indenting
- HTTPS / TLS 1.2 support for more secure communication
- Support for passing cookies
- Support for proxy servers
- Option to cancel infinite pagination when saving in the UI
- Option to parse output from a command-line standard output stream (e.g.
cmd:>curl -k http://httpbin.org/get) - Filter expressions (e.g.
$.store.books[?(@.price<10)]) Support for SQL Server 2025, 2022, 2019, 2017, 2016, 2014, 2012 (32/64-bit) and Azure Data Factory
SQL Server 2012–2025
Three access modes
One component for files, APIs, and JSON in a variable.
Pick how the document arrives. Preview rows in the UI, then map columns to SQL like a normal data flow.
Load JSON files into a SQL table, including wildcards.
Point at one file or a folder pattern such as *.json. Preview sample rows, then output FileName and FilePath columns so each record still knows where it came from.
- Single file or wildcard loop without a Foreach enumerator
- Files must share a similar structure
- Preview before you map the destination
Call a REST API and land JSON as rows.
Set the URL, HTTP method, headers, and body. Add pagination, OAuth, Basic auth, HMAC, cookies, or a proxy when the service requires it.
- GET or POST with custom headers and body
- Page via URL, body, or header until the feed ends
- OData, AWS APIs, Google, Salesforce, and custom endpoints
Parse a JSON string or an SSIS variable.
Paste JSON, use a built-in example, or read from a variable. Syntax highlighting and preview help you confirm the JSONPath filter before you run the package.
- Direct value or value from a variable
- Filter nested arrays in the Select Filter UI
- Built-in Example links load sample JSON quickly
- Optional command-line / stdout JSON input (for example curl)
Page through REST API results until the last page.
On the Pagination tab, pick a Paging mode such as Response Attribute Mode, which reads next-page information from the response. When the next cursor is null, not found, or matches the end indicator, the data fetch stops.
- Paginate via URL parameter, body attribute, or HTTP header value
- Next link / cursor expression (e.g.
$.pagingInfo.nextUrl) plus an optional suffix for the next URL - End rules stop paging on a status code, response size, or error message
Inside the UI
Key settings, as they appear in SSDT.
Real screens from the JSON Source UI. Step through OAuth, paging, metadata, and error output, or open any shot at full size.
Read JSON from a web URL
Set Access Mode to File path or web URL, paste the endpoint, then enter a JSONPath such as $.value[*]. Click Preview to confirm the rows before you map a destination.
JSONPath example
Point at the array you want. Leave the rest behind.
The Select Filter UI shows objects versus arrays. Expressions start at $. Use [*] for every record in an array. Below: input JSON, expression, and illustrative output rows.
-
Input JSON
One customer record with a nested Branches array.
[ { "CustomerID": "C1", "CompanyName": "Company 1", "Branches": [ { "Name": "BranchA1", "City": "City1_1" }, { "Name": "BranchB1", "City": "City1_2" } ] } ] -
Set the JSONPath filter
Filter
$.Branches[*]Each item in the Branches array becomes one row.
Include Parent Columns: On Adds parent fields such as CustomerID and CompanyName to each row, so they repeat for every branch.
-
Output rows for your data flow
CustomerID parent CompanyName parent Name City C1 Company 1 BranchA1 City1_1 C1 Company 1 BranchB1 City1_2 Connect these rows to SQL Server or another SSIS destination.
Other expressions on the same input
| Expression | What you get |
|---|---|
$ |
Every root record |
$.CompanyName |
Company name of each record |
$.Branches[*] |
All branches as rows |
$.Branches[:1] |
First branch of every record |
$.value[*] |
Typical OData / REST array |
Illustrative input and output. More than one array? See when to use JSON Parser Transform.
Articles
Worked examples that use JSON Source.
Compatibility and requirements
Designed to stay inside your SSIS runtime.
- SQL Server 2012 through 2025, 32-bit and 64-bit
- Windows only, 64-bit OS required. SQL Server Express and Web editions do not include SSIS
- Azure Data Factory SSIS Integration Runtime
- Self-hosted: credentials and data stay in your environment (external API calls still reach that service)
- Fully functional 30-day trial of SSIS PowerPack
FAQ
Questions teams ask before they drop JSON Source on the canvas.
What JSON inputs does JSON Source support?
JSON from a local file (including wildcards), a REST / web URL, or a direct JSON string, including a value stored in an SSIS variable. Optional command-line / stdout JSON input is also available for advanced scenarios.
How do I read many JSON files without a loop?
Use a wildcard path such as c:\data\cust*_???.json. Optional FileName and FilePath output columns identify which file produced each row. See file mode above.
Can it page through a REST API?
Yes. Paginate via URL parameter, request body, or HTTP header, and set the end condition. Read the REST API pagination guide for URL, path, cursor, body, header, and token paging. OData feeds can page automatically.
Does it support OAuth?
OAuth 1.0 and 2.0 through the OAuth Connection Manager, plus Basic auth, dynamic tokens, and HMAC for APIs such as Azure CosmosDB. See the authentication boxes in Features.
Which SQL Server versions can I run it on?
SQL Server 2025, 2022, 2019, 2017, 2016, 2014, and 2012 (32- and 64-bit), plus Azure Data Factory with an SSIS Integration Runtime (packages must target SQL Server 2017). See Compatibility and requirements.
Where is the full property reference?
Open the JSON Source help file for properties, error handling, and JSONPath examples. System requirements are on the installation page.
JSON family
Pair it with the rest of the JSON / API toolbox.
What customers say
Start your free trial today
Install SSIS PowerPack and drag JSON Source onto a Data Flow.
Preview real data before you map a destination, with no script component required.
- Data Flow Source in SSISJSON Source appears in the toolbox after you install PowerPack.
- Fully functional 30-day trialSSIS PowerPack, running in your environment.
- Live supportHelp during your trial and after purchase.
Open Help FileBrowse all SSIS PowerPack tasks
New purchases may be eligible for refund review within 30 days. See the refund policy.
Talk to an engineer before or during your trial.