SSIS CSV File Source
Turn CSV files and Web API responses into clean rows for your SSIS data flow.
Read single or multiple CSV files, Web API responses, or compressed zip / gzip files without extracting them to disk. Handle missing columns, comments, and bad rows, then send data to SQL Server or another SSIS destination, all without a script component.
The trial includes CSV File Source and every other PowerPack component
- Reads zip and gzip files
- Runs in your environment
- Also runs in Azure Data Factory
- OAuth, Basic auth, TLS 1.2
At a glance
What CSV File Source can do
CSV File Source reads CSV data from local files or a Web API call, including compressed files, and gives you parser, pivot, and error-handling options so you can load SQL Server like any other SSIS source, with no script component needed.
Files & wildcards
Read one CSV file or many files with a wildcard pattern.
- Wildcards such as
c:\data\cust-1*.csvorc:\data\subfolder\*.csv - Optionally include files from sub folders
- Optional
__FilePathand__FileNameoutput columns - Move processed files to an archive folder
Web API & HTTP
Read CSV returned by a Web API call.
- Set URL, HTTP method, headers, and body
- REST API pagination, with an option to cancel infinite pagination on UI save
- Custom headers, cookies, and proxy
Zip & gzip in stream mode
Read compressed CSV files without extracting them to disk.
- Compression formats: GZip, Zip, and TarGZip
- Works for local files and Web URLs (e.g.
invoices.csv.zip)
OAuth & Basic authentication
Pass credentials through a connection (UserID / Password or OAuth).
- OAuth and HTTP Basic authentication
- HTTPS with TLS 1.2 (SSIS 2014 or higher) or TLS 1.3
- Option to ignore SSL certificate errors
Advanced parser options
Control how each line is parsed.
- Trim fields, trim headers, and skip header or data rows
- Comments in the file (e.g. lines starting with
#) - Ignore blank lines and rows with empty values in all fields
- Custom delimiter, quote character, encoding, and culture
Missing columns & pivot
Handle uneven files and reshape data.
- Missing columns at the end are filled with null values
- Pivot mode turns columns into rows (Pivot_Name / Pivot_Value)
Advanced error handling
Decide what counts as an error and where bad rows go.
- Throw error when column count mismatch is detected
- Throw error when no record is found (empty file)
- Redirect bad rows to the error output
Dynamic paths & command line
Build paths at runtime or read from another program.
- Variable placeholders such as
c:\data\{{User::MyFolder}}\*.csv - Parse command-line standard output (e.g.
cmd:>curl -k https://httpbingo.org/get) - Date / time parsing options
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
See all features
- Read CSV data from a Web API call or from file(s) stored on local disk
- Read single or multiple files (wildcard pattern supported, e.g.
*.csv) - Read zip and gzip compressed files (stream mode)
- OAuth and HTTP Basic authentication along with many security features
- Missing columns at the end are auto-filled with null values
- Data pivot (transform columns into rows)
- Comments in the file
- Advanced error handling such as throw error for an empty file or when the column count does not match
- Advanced parser options such as trim fields, trim headers, and skip rows
- Ignore blank lines and blank rows (no data in any column)
- Dynamic file path using a variable placeholder (e.g.
c:\data\{{User::MyFolder}}\*.csv) - 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 https://httpbingo.org/get) Support for SQL Server 2025, 2022, 2019, 2017, 2016, 2014, 2012 (32/64-bit) and Azure Data Factory
SQL Server 2012–2025
Four ways to read CSV
One component for local files, Web APIs, and compressed CSV.
Pick where the CSV comes from. Preview rows in the UI, then map columns to SQL like a normal data flow.
Load CSV files into a SQL table, including wildcards.
Set Access Mode to File path or web URL and enter one file or a pattern such as c:\data\subfolder\*.csv to read more than one file.
- Single file or many files without a Foreach loop
- Optionally include files from sub folders
- Move processed files to an archive folder
Read CSV returned by a Web API.
Enter the URL, then set the HTTP method, headers, and body on the Settings tab. Add pagination, authentication, cookies, or a proxy when the API requires it.
- GET, POST, PUT, and DELETE methods
- Pagination modes and end rules
- OAuth or Basic authentication through a connection
Read zip or gzip CSV without extracting it.
On the GZip/Zip Compression tab, set the file compression format to Zip (for .zip files) or GZip (for .gz files). The same works for a Web URL such as invoices.csv.gz.
- GZip, Zip, and TarGZip
- Stream mode, no temporary files on disk
- Preview the decompressed rows before you run
Build the path at runtime from a variable.
Use a variable placeholder in the folder path or select a variable that holds the file name. You can also read CSV text directly, typed in or from a variable.
- Placeholders such as
{{User::MyFolder}} - Direct value or value from a variable
- Command-line output with
cmd:>
Inside the UI
Key settings, as they appear in SSDT.
Real screens from the CSV File Source product page, help file, and pivot guide. Step through URLs, files, compression, parsing, pivot, and errors, or open any shot at full size.
Read CSV from a Web API
Set Access Mode to File path or web URL, enter the API URL, and configure the HTTP method and headers on the Settings tab.
Pivot example
Turn columns into rows with one checkbox.
From the ZappySys pivot guide: a small Persons.csv file read with CSV File Source, then pivoted on the Pivot Columns to Rows tab.
-
Input CSV
Persons.csv as shown in the guide’s data preview (values as published).
FirstName,LasteName,Email,Phone Hohn,doe,john@zs.com,555-555-1111 Amy,Alison,alison@zs.com,555-555-2222 Bella,Ava,ava@zs.com,555-555-3333 -
Enable pivoting
Option
EnablePivot = TrueIn the UI: check Enable Pivoting of child level properties on the Pivot Columns to Rows tab, then click Preview.
Output columns Property names appear in
Pivot_Nameand values inPivot_Value. -
Output rows for your data flow
Pivot_Name Pivot_Value FirstName Hohn LasteName doe Email john@zs.com Phone 555-555-1111 FirstName Amy LasteName Alison Email alison@zs.com Phone 555-555-2222 First 8 of 12 rows shown. Connect these rows to SQL Server or another SSIS destination.
Data from How to Pivot CSV Data in SSIS. The guide also shows how to keep FirstName and LastName as parent columns with JSON Generator and JSON Parser Transform.
Articles
Worked examples that use CSV 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 CSV File Source on the canvas.
What CSV inputs does CSV File Source support?
CSV from local files (single or multiple with wildcards), a Web API URL, compressed zip / gzip files, or a CSV string typed in or stored in a variable. Command-line output with cmd:> is also supported.
How do I read many CSV files without a loop?
Use a wildcard path such as c:\data\subfolder\*.csv. Optional __FileName and __FilePath output columns identify which local file produced each row. See local files above.
Can it read zip or gzip files directly?
Yes. Set the file compression format to Zip or GZip (TarGZip is also available) and the file is read in stream mode without extracting it to disk.
What if some rows have fewer columns?
Missing columns at the end are auto-filled with null values. You can also choose to throw an error when the column count does not match.
Can I send bad rows somewhere else?
Yes. Set the error handling to redirect rows (RD_RedirectRow) and connect the error output to a separate destination.
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 CSV File Source help file for properties, pagination, and error handling. System requirements are on the installation page.
See also
Pair it with the rest of the CSV toolbox.
What customers say
Start your free trial today
Install SSIS PowerPack and drag CSV File Source onto a Data Flow.
Preview real data before you map a destination, with no script component required.
- Data Flow Source in SSISCSV 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.