Overview
The client wanted to pull all available Smartsheet data into Azure Data Lake Gen2. A Smartsheet linked service was available, but it was not available in the expected Azure Data Factory copy tool experience, which created confusion around how to complete the integration.
Challenge
- Needed full data pull across sheets, rows, columns, workspaces, and metadata.
- Standard connector path did not provide the control needed for the requirement.
- Required dynamic discovery, looping, and structured landing for future reporting.
Solution
MSPowerhouse designed a REST API-based Azure Data Factory pipeline for Smartsheet. Instead of depending only on the linked service experience, the solution used Smartsheet's API to discover available sheets and then loop through each sheet to extract detailed row and column data.
The pipeline was designed with a master-child pattern. The master pipeline handled discovery and orchestration, while child pipelines handled specific extraction tasks such as pulling sheet details, rows, metadata, or related objects. Data was landed in Azure Data Lake Gen2 in raw JSON format first, preserving the source structure for future transformation.
Technical Execution
- Azure Data Factory REST API integration.
- Smartsheet API authentication.
- Sheet discovery process.
- Looping through sheet IDs.
- Pulling sheet details, rows, columns, and metadata.
- Pagination handling.
- Dynamic folder paths in ADLS Gen2.
- Raw JSON landing by source, sheet, and run date.
- Optional future expansion for attachments, discussions, and users.
- A design that could support full loads and future incremental refreshes where supported.
Outcome
The client received a clear path for pulling Smartsheet data into Azure Data Lake Gen2 even though the built-in copy tool experience did not support the requirement cleanly.
Impact
This project showed MSPowerhouse's ability to work around connector limitations and still deliver a proper enterprise integration using APIs, ADF orchestration, and data lake storage.
