SSIS Export CSV File Task
 PreviousNext

SSIS Export CSV File Task is your one stop solution to generate CSV files from Single or Multiple tables. We worked hard to take care most painful tasks for exporting data from any relational source to CSV. This completely dynamic approach without worrying about changing columns inside table. We worked hard to take care most painful tasks for exporting data to CSV format.

Download SSIS PowerPack

Content

Step-By-Step

In this tutorial we will learn how to load data into CSV file from SQL Server Source.
  1. Firstly, You need to Download and Install SSIS ZappySys PowerPack.
  2. Once you finished first step, Open Visual Studio and Create New SSIS Package Project.
  3. In visual studio just Drag and Drop ZS Export CSV Task in design panel.
    SSIS Export to CSV File Task - Drag and Drop
  4. Now, We need OLEDB Connection For Source.

How to create OLE DB Connection.

  1. Let's, Right click on Connection Managers Panel to Create OLEDB Connection, so you can use Source and Context Menu will appear, Select New OLEDB Connection from the Context Menu.
    SSIS Export to CSV File Task - Connection
  2. Now, click on New Button for create Connection.
    SSIS Export to CSV File Task - Connection
  3. Let's Configure Connection Manager, just Follow steps one by one as we have created.
    SSIS Export to CSV File Task - Connection

How to extract data from SQL Server Table to CSV file using Export CSV File Task.

  1. Double click on ZS Export CSV Task for Configure it.
  2. Let's, Generate CSV files for selected tables/views. Set Source Connection we have created, click on Select/Edit for Select Source Table/Queries you want to export.
    SSIS Export to CSV File Task - Generate CSV files for selected tables/views
  3. Now, set Target Path and Other Options, Select Target Location from Browse Button. Check on Overwrite target file(s) if you want it. You can Check on Compress file to *.gz format.
    SSIS Export CSV File Task - Select Target Path and other options
  4. Click OK to save Export CSV File Task setting UI.
  5. Once you are done, Just Execute SSIS Export CSV File Task.
    SSIS Export CSV File Task - Execution Log

How to extract data with Split and Group By column name using Export CSV File Task.

  1. Double click on ZS Export CSV Task for Configure it.
  2. In the Split Options Tab, check on Enable Split By Size/Rows and specify Group By Column then new file will be created for each unique value of Group By Column (e.g. If you specify Group By Column = Country then one file will be created for each Country).
    SSIS Export CSV File Task - Split CSV Data into Multiple files
  3. Let's, Configure in CSV Options, use Column Delimiter and you can change Date/Time output format as per your required.
    SSIS Export CSV File Task - CSV Date Formatting, Encoding Options
  4. Click OK to save Export CSV File Task setting UI.

Properties

Property Name Description
RowDelimiter Row separator for data files (If this is not specified then New line is used). (NOTE: use \t for tab, \r\n for new line (This is default), \r for carriage return, \n for new line)
NoHeaderRow No header row with column names in the first row. By default first row is header row if this option is not specified. This option is only valid when output file format is excel or csv.
DelimiterForColumn Field separator character for data files. (NOTE: use \t for tab)
QuotesAroundValue Wrap quotes around value in data files
DateFormat Date format in csv data file (e.g. yyyy-MM-dd HH:mm:ss.fff)
Encoding Encoding of source file

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
Default [0] Default
ASCII [1] ASCII
UTF8 [2] UTF-8
UTF16 [3] UTF-16 LE (i.e. Unicode Little Endian)
UTF32 [4] UTF-32
UTF8WithoutBOM [5] UTF-8 Without BOM
UTF32WithoutBOM [6] UTF-32 Without BOM
UTF7 [7] UTF-7
UTF7WithoutBOM [8] UTF-7 Without BOM
UTF16WithoutBOM [9] UTF-16 Without BOM
BigEndian [10] UTF-16 BE (i.e. Unicode Big Endian)
BigEndianWithoutBOM [11] UTF-16 BE Without BOM
Timeout Time out for source query. 0 means no timeout
Connection Connection for source table/query from which you want to export data. This can be valid ADO.net or OLEDB connection Manager Name or ID
Source Source for the data. This can be table(s), query, view(s) or proc. If its other than table or view then you must specify --proc or --query parameter to indicate access mode. You may list multiple objects by separating them using vertical bar (e.g table1|table2|view1). You can also use pattern for objects (e.g. cust%|prod%)
Schema Schema name or pattern to search for objects when object name contains pattern (e.g. Cust%)
ExcludeViews If pattern used on object name then do not include matching views in the objects to export.
ExcludeTables If pattern used on object name then do not include matching views in the objects to export.
UseMappingFile Use custom layout and additional processing instructions per column
MappingAccessMode AccessMode for Mapping file. This determines how mapping information will be read at runtime
MappingDirectValue Mapping information in the format of XML
MappingVariable Variable which holds mapping information
MappingFilePathVariable Variable which holds mapping file path
MappingFilePath Mapping file path which contains custom layout and additional information
Target Target file or folder path
Append Append data at the end of file
Overwrite Overwrite target file if exists
GroupByColumn Split files by group by column value. e.g. if you group by Country field then you will have one file for each country. Country name will be used as file name suffix. If you specify --split option then you may have more than one file for each value in group by field depending on split size/rows you specify
Compress Compress file in gzip format
KeepRaw Keep source file after compressing it when --gzip option enabled. By default source file is deleted once its compressed.
SplitFiles Split into multiple files (when split enabled you can split by filesize or rowcount. Check --splitsize and --splitrows options).
MaxSizePerFile Maximum size per file. You can specify in bytes, kb, mb or gb. Example: 10mb, 5000kb, 1gb, 2000bytes
MaxRowsPerFile Maximum rows per file
MaxRowsToExport Maximum number of rows to export.
LoggingMode LoggingMode determines how much information is logged during Package Execution. Set Logging mode to Debugging for maximum log.

Setting UI

SSIS Export CSV File Task - Properties
SSIS Export CSV File Task - Properties

See Also

Articles / Tutorials

Click here to see all articles for [SSIS CSV Export Task] category
How to convert SQL Server to CSV in SSIS

How to convert SQL Server to CSV in SSIS

How to convert SQL Server to CSV in SSIS There are several tricks related to CSV. For example, dates, working with variables, converting text, working with stored procedures. In this article, we will work with several tips to convert SQL Server data to the CSV format using CSV. For this article, we will use the following ZappySys component: […]

SSIS Data Load – SQL Server to FTP/SFTP (Split Files, GZip)

SSIS Data Load – SQL Server to FTP/SFTP (Split Files, GZip)

Introduction In this blog post you will see how easy it is to load large amount of data from SQL Server to FTP/SFTP. SSIS Data Load – SQL Server to FTP/SFTP (Split Files, GZip). For demo purpose we will use SQL Server as relational source but you can use same steps for any database engine […]

SSIS Data Load – SQL Server to Azure Blob (Split Files, GZip)

SSIS Data Load – SQL Server to Azure Blob (Split Files, GZip)

Introduction In this blog post you will see how easy it is to load large amount of data from SQL Server to Azure Blob Storage using SSIS. We will export / compress data to multiple files. For demo purpose we will use SQL Server as relational source but you can use same steps for any […]

Loading data from SQL Server to Amazon S3 in SSIS (Split Files, GZip)

Loading data from SQL Server to Amazon S3 in SSIS (Split Files, GZip)

Introduction In this blog post you will see how easy it is to load large amount of data from SQL Server to Amazon S3 Storage. For demo purpose we will use SQL Server as relational source but you can use same steps for any database engine such as Oracle, MySQL, DB2. In this post we […]

Copyrights reserved. ZappySys LLC.