SSIS Azure Blob Source for JSON File

⚡ Key Benefits

• Multi-File Wildcard Support — Read one or many JSON files in a single operation using wildcard patterns (e.g. *.json)
• Nested JSONPath Extraction — Extract data from any level of nesting using path expressions (e.g. Orders nested under a Customer node)
• Compressed File Streaming — Read GZip or Zip compressed JSON files directly from Blob Storage without extracting to disk first
• Dynamic File Paths — Use SSIS variable placeholders to build file paths at runtime (e.g. /myroot/{{User::MyFolder}}/*.json)
• Design-Time Data Preview — Inspect live data from Azure Blob Storage directly in the component UI before executing the package

📸 Visual Overview & Setup

1. Create Azure Storage Connection 🔍 View Full Size	2. Browse & Select JSON File(s) 🔍 View Full Size	3. Configure JSON File Options 🔍 View Full Size
4. Design-Time Data Preview 🔍 View Full Size	5. Read Compressed Files (GZip / Zip) 🔍 View Full Size	6. Error Handling — Redirect Bad Records 🔍 View Full Size

🔧 Key Features

File Selection & Access

• Read single or multiple JSON files from Azure Blob Storage using wildcard patterns (e.g. *.json)
• Visual Blob Browser UI — browse containers and select files without typing paths manually
• Dynamic file paths via SSIS variable placeholders (e.g. /myroot/{{User::MyFolder}}/*.json)

JSON Parsing

• Extract data from any nesting level using JSONPath expressions (e.g. Orders nested under Customer)
• Data pivot — transform JSON property columns into rows for EAV-style processing
• Flexible DateTime parsing options with configurable format handling

Performance & Reliability

• Stream GZip and Zip compressed JSON files without extracting to disk first
• Redirect bad records to an error output for graceful error handling
• Compatible with SQL Server 2012–2025 (32/64-bit) and Azure Data Factory

💡 Common Use Cases

Common real-world scenarios for reading JSON files from Azure Blob Storage in SSIS:

• Daily JSON Export Ingestion: Read JSON files dropped into Blob Storage by upstream systems and load into SQL Server.
  Example: Process all orders_*.json files arriving daily in a Blob container and upsert into a staging table.
• Nested API Response Processing: Parse complex nested JSON structures using JSONPath to flatten them into tabular rows.
  Example: Extract order line items nested inside customer records from a single JSON export file.
• Compressed Archive Handling: Read GZip-compressed JSON exports directly without a manual extraction step.
  Example: Process nightly *.json.gz data dumps from Azure Blob without any intermediate decompression task.
• Dynamic Multi-Tenant Pipelines: Build file paths at runtime using SSIS variables for tenant- or date-aware processing.
  Example: Use /exports/{{User::TenantID}}/{{User::RunDate}}/*.json to process the correct tenant partition each run.

🎯 Summary

Replace custom Azure SDK scripting with a drag-and-drop JSON source that reads directly from Azure Blob Storage.
Browse containers visually, extract nested JSON with JSONPath, stream compressed files, and build dynamic paths with SSIS variables — all without writing a single line of code.

Trusted by Developers & IT Teams Worldwide Built for SSIS Workflows: Purpose-built for high-performance ETL and complex integration scenarios. Expert Technical Support: Direct access to engineers via email and remote screen-share sessions. Proven Enterprise Scale: Trusted by 3000+ teams across 90+ countries, including Fortune 500.

SSIS PowerPack Includes SSIS Azure Blob JSON File Source Read reviews on Capterra →