SSIS Amazon S3 JSON File Source
Read JSON files from Amazon S3 Storage.
SSIS Amazon S3 Source for JSON File can be used to read JSON files from Amazon S3 Storage (i.e. AWS S3 Service). You can extract data from single or multiple files (wildcard pattern supported). Also you can read compressed files (*.gz) without extracting files on disk.
The trial includes Amazon S3 JSON File Source and every other PowerPack component
- Data Flow Source in SSIS
- Runs in your environment
- Also runs in Azure Data Factory
- Fully functional 30-day trial
At a glance
What Amazon S3 JSON File Source can do
SSIS Amazon S3 Source for JSON File can be used to read JSON files from Amazon S3 Storage (i.e. AWS S3 Service). You can extract data from single or multiple files (wildcard pattern supported). Also you can read compressed files (*.gz) without extracting files on disk.
Read many S3 files
Read a single file or multiple files stored in Amazon S3 using a wildcard pattern (e.g. *.json).
Recursiveincludes files from sub foldersEnableMultiPathModereads a list of paths in one step- Dynamic path with variable placeholders, e.g.
/myroot/{{User::MyFolder}}/*.json
Extract nested data
Use a path expression to extract data from any level (e.g. Orders nested under Customer node).
Filterexpression, e.g.$.Users[*].UserNameIncludeParentColumnsadds parent properties to each rowExcludedPropertiesandMaxLevelsToScan
Read compressed files
Read Zip and GZip compressed files in stream mode, without extracting them on disk.
FileCompressionType: GZip, Zip or TarGZip
Array flattening & transforms
Reshape arrays into columns or rows.
EnableArrayFlatteningwithMaxArrayItemsToFlattenArrayTransformationType: 2-dimensional arrays, key/value to columns, columnless arrays and more
Data pivot
Transform columns into rows with the pivot option.
EnablePivotoutputsPivot_NameandPivot_ValuecolumnsIncludePivotPathadds the parent hierarchy asPivot_Path
Date & number parsing
Control how dates and decimal values are parsed.
DateParseHandling: keep as string, DateTime or DateTimeOffsetFloatParseHandlingDecimal for high precision / scale- Treat blank number, bool or date values as NULL
Raw document output
Pass documents downstream without parsing them into columns.
OutputRawDocumentoutputs raw JSON stringsOutputFilePath/OutputFileNameadd source file columnsEnableCustomReplacesearch/replace (including regex) before parsing
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
Flexible AWS credentials
Connect through the Amazon Storage Connection Manager with access keys, temporary credentials or the default instance credentials.
CredentialType: key-based, temporary token (withSessionToken) or default (IAM Role or anonymous)- Proxy server support with optional proxy authentication
ForcePathStylefor S3-compatible services such as MinIO, Ceph or Cloudian
See all features
- Read single or multiple files stored in Amazon S3 (wildcard pattern supported, e.g. *.json)
- Path expression to extract data from any level (e.g. Orders nested under Customer node)
- Read Zip and GZip compressed files in stream mode
- Data pivot (transform columns into rows)
- Dynamic file path using variable placeholders (e.g.
/myroot/{{User::MyFolder}}/*.json) - Include parent columns with filtered rows, with optional column prefix
- Array flattening and 2-dimensional array transformations
- Date/time and high precision decimal parsing options
- Custom search/replace (including regex) before the document is parsed
- Output raw JSON documents instead of parsed columns
- Output source file path and file name as columns
- Key-based, temporary token or default (IAM Role) credentials, with proxy support
Support for SQL Server 2025, 2022, 2019, 2017, 2016, 2014, 2012 (32/64-bit) and Azure Data Factory
SQL Server 2012–2025
How it works
From the toolbox to your first run.
Key steps from the Amazon S3 JSON File Source page and help file, with the screens you will see in SSDT.
Amazon S3 JSON File Source – Read from JSON File Options
Double-click ZS Amazon S3 JSON File Source, select your Amazon Storage connection and choose the JSON file, or use a wildcard pattern (e.g. *.json) to read many files.
Set a Filter path expression such as $.Users[*] to pick the array you want as rows, then click Preview.
Amazon S3 JSON File Source – Select JSON File(s) with the S3 file browser
Browse your S3 buckets and folders and pick the JSON file to read, instead of typing the path by hand.
The path can also hold variable placeholders, e.g. /myroot/{{User::MyFolder}}/*.json, so the file is chosen at runtime.
Amazon S3 JSON File Source – DateTime Parsing Options
DateParseHandling decides whether date strings stay as text or become DateTime or DateTimeOffset (keeping the time zone).
Set FloatParseHandling to Decimal when you need high precision / scale for numeric values.
Amazon S3 JSON File Source – Redirect Bad Records (Error Handling)
Redirect records that fail to convert to the error output instead of stopping the package, and handle them in a separate path.
Inside the UI
Key settings, as they appear in SSDT.
Real screens from the Amazon S3 JSON File Source product page. Step through them, or open any shot at full size.
Amazon S3 JSON File Source – Read from JSON File Options
Select the connection, choose the S3 file or a wildcard pattern, and set the filter expression.
Articles
Worked examples that use Amazon S3 JSON File 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 Amazon S3 JSON File Source on the canvas.
What is Amazon S3 JSON File Source?
SSIS Amazon S3 Source for JSON File can be used to read JSON files from Amazon S3 Storage (i.e. AWS S3 Service). You can extract data from single or multiple files (wildcard pattern supported). Also you can read compressed files (*.gz) without extracting files on disk.
How do I extract nested arrays, such as orders under each customer?
Set the Filter path expression to the array you want as rows, for example $.Customers[*].Orders[*]. Turn on IncludeParentColumns to add the parent (customer) properties to each row, and ParentColumnPrefix to keep column names apart.
How do I read many JSON files from S3 in one go?
Use a wildcard pattern (for example *.json) and set Recursive to include sub folders, or turn on EnableMultiPathMode and list several paths separated by a new line or ::. OutputFileName / OutputFilePath add the source file as a column.
Can it read GZip or Zip files without extracting them?
Yes. Set FileCompressionType to GZip, Zip or TarGZip and the file is read in stream mode, without being extracted on local disk.
Can I pass whole JSON documents downstream without parsing them?
Yes. Turn on OutputRawDocument to output each document as a raw JSON string instead of parsing it into columns.
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.
What customers say
Start your free trial today
Install SSIS PowerPack and drag Amazon S3 JSON File Source onto the Data Flow.
Read JSON files from Amazon S3 Storage.
- Data Flow Source in SSISAmazon S3 JSON File 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.