SSIS CSV File Destination
 PreviousNext

CSV Destination for CSV File can be used to write data in CSV file format. You can automatically split large files by row count or size at runtime. It also supports writing files directly in compressed format such as GZip (*.gz).

Download SSIS PowerPack

Content

Video Tutorial


Coming soon...

Step-By-Step

In this tutorial we are going to load data from SQL Server Table using OLEDB Source and ZS CSV File Destination. You can use ZS DummyData Source too for practice it.
  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. Now, Drag and Drop SSIS Data Flow Task from SSIS Toolbox.
    SSIS Data Flow Task - Drag and Drop
  4. Double click on the Data Flow task to see Data Flow designer surface.
  5. Here, In Visual Studio, drag and drop the OLE DB Source and CSV File Destination in the design surface and join the tasks with the blue arrow.
    SSIS CSV File Destination and OLE DB Source - Drag and Drop
  6. Here, you can use our Free ZS Dummy Data Source too. Click here for more information.
  7. But, we are going through OLE DB Source so need OLEDB Connection and File Connection for CSV File Destination.

How to create OLE DB Connection and File Connection.

  1. Lets, create first OLE DB Connection.
  2. 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 OLEDB - Connection
  3. Now, click on New Button for create Connection.
    SSIS OLEDB - Connection
  4. Let's Configure Connection Manager, just Follow steps one by one as we have created.
    SSIS OLEDB - Connection
  5. Click on Test Connection to see correct configure it.
  6. Click on OK button to save connection configure setting UI.

How to Create File Connection.

  1. Let's, Right click on Connection Managers Panel to Create File Connection, so you can use Destination Path, and Context Menu will appear, Select New Connection from the Context Menu. In the File Connection Manager, select the Usage type and specify the path of the CSV file. 
    CSV File : You can download good one CSV file from here for practice.

https://zappysys.com/downloads/files/test/invoices.csv
https://zappysys.com/downloads/files/test/invoices.csv.gz
https://zappysys.com/downloads/files/test/invoices.csv.zip
    SSIS New File Connection
  2. Click on OK button to save connection configure setting UI.

How to Extract Data from SQL Server Table to CSV File Destination.

  1. Double click on OLE DB Source for configure it.
  2. Lets Configure in Connection Manager, just follow below image steps.
    SSIS OLEDB Connection Configure
  3. Click OK to Save OLEDB Source Editor UI Settings.
  4. Lets configure CSV File Destination, Double click on it, In the File Connection Manager, select the file connection created before.
    add connection manager to SSIS csv destination
  5. In the Component Properties, set the Append property to False, enter the File Path and set the Overwrite file if exists to True.
    append data in SSIS destination
  6. In the Input Columns Tab, check the columns that you want as load data.
    ssis check input columns
  7. Click on OK button to save configure setting UI.
  8. Finally, run the package and the data will be exported from source to destination immediately.
    Create new SSIS connection

How to do file split using CSV File Destination.

  1. Double click on CSV File Destination to configure it.
  2. In the Component Properties set Enable File Split to True and File Split Mode BySize.
    Enable file split in CSV destination SSIS
  3. Click OK button to save configure setting UI.
  4. Thats all, Now you can run or execute package and you will see numbers of CSV file in Destination Folder.

How to do file compression using CSV File Destination.

  1. Double click on CSV File Destination to configure it.
  2. In the Component Properties set Compression Type to GZip or Zip and File Split Mode BySize.
    Enable compression to zip and gzip in SSIS
  3. Click OK button to save configure setting UI. and You can Run or Execute.

How to do maximum number of rows per file using CSV File Destination.

  1. Double click on CSV File Destination to configure it.
  2. In the Component Properties you can set Max Rows Per File and Max Size Per File as per your need.
    Maximum size per file
  3. Click OK button to save configure setting UI. and You can Run or Execute.

Properties

Property Name Description
LoggingMode LoggingMode determines how much information is logged during Package Execution. Set Logging mode to Debugging for maximum log.

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

Option Description
Normal [0] Normal
Medium [1] Medium
Detailed [2] Detailed
Debugging [3] Debugging
PrefixTimestamp When you enable this property it will prefix timestamp before Log messages.
AccessMode File path access mode (e.g. direct or variable)

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

Option Description
Direct [0] Direct
Variable [1] Variable
FilePathVariable Variable name which holds file path where you want to write data
CompressionType Compression format for source file (e.g. gzip, zip). If you compress files then add correct extension to file path manually (e.g. *.gz or *.zip).

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

Option Description
None [0] None
GZip [1] GZip
Zip [2] Zip
TarGZip [3] TarGZip
FilePath File path where you want to write data. You can also use variable placeholders anywhere in the path. Example: myroot/folder/{{User::FileName}}  --OR--  myroot/folder/file_{{System::StartTime,yyyyMMdd}}.dat
Append Append data to file
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). You can type any string here including special values such as {SPACE}, {NEWLINE}, {TAB}, {CR}, {LF}, {CRLF}. To use Hex code for seperator you may use \xNN (for ascii) or \xNNNN (for unicode charcter). For example \x221E will use infinity symbol as column seperator.
OverwriteFileIfExists Indicates whether you want to overwrite target file if exists. This setting is ignored if Append=true
ColumnDelimiter Field separator for data files. (NOTE: use \t for tab, \r\n for new line (This is default), \r for carriage return, \n for new line). You can type any string here including special values such as {SPACE}, {NEWLINE}, {TAB}, {CR}, {LF}, {CRLF}. To use Hex code for seperator you may use \xNN (for ascii) or \xNNNN (for unicode charcter). For example \x221E will use infinity symbol as column seperator.
FirstRowHasColumnNames Indicates whether first row has column names (Default is true)
QuotesAroundValue Wrap quotes around value in data files
QuoteChar Character for quote indicator around value
DateTimeFormat Date time format string for any column with DT_DBDATETIME, DT_DBDATETIME2 or DB_DATE format
EnableFileSplit Enable splitting large file into multiple files (see FileSplitMode to configure splitting mode).
FileSplitMode Specifies file splitting method (i.e. split by row count).

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

Option Description
ByRowCount [0] ByRowCount
BySize [1] BySize
MaxRowsPerFile Specifies maximum rows per file when FileSplitMethod is set to ByRowCount. When this row count is reached data is written to new file.
MaxSizePerFile Specifies maximum size per file when FileSplitMethod is set to BySize. Enter size in following size format specifier allowed bytes, kb, mb, gb, tb (e.g. 100mb)
Encoding Specifies encoding for data. If you set CharacterSet property then Encoing is ignored.

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
CharacterSet Character set name (i.e. ISO-8859-1) or code page number. If you set this property then Encoding property will be ignored. To output without BOM set this property to blank and use Encoding instead.
DoNotCreateFileIfNoRows Indicates if empty file should be created or not if no input records found. This option is ignored if you enabled Append option (some destination i.e. S3 may not support append). By default empty file is created if zero input records. Set this to true if you do not want empty file on zero records.
EnableCustomEscapeSequence Enable this option to define custom replacement rules (See also CustomEscapeSequenceRule). Replacement rule should contain key=value pairs separated by semicolon. You can hard code character directly or use designated keyword (e.g. supported keywords are {doublequote} {semicolon} {tab}, {cr}, {lf}, {null}). You can also use Hex chars to search (e.g. \xff44=something). Use blank value in the second part to replace with blank. Example: {null}=\0;"="";{semicolon}=\{semicolon};{cr}=\r;{lf}=\n;{tab}=\t;{columndelimiter}=\{columndelimiter};\xff32=;\xff31=;{null}=;
CustomEscapeSequenceRule Define custom replacement rules to search and replace certain characters. This option is only valid when EnableCustomEscapeSequence=true. Replacement rule should contain key=value pairs separated by semicolon (i.e. ; ). In the key=value pairs, left side you have to define character to be searched and on the right side replacement value. You can hard code character directly or use designated keyword (e.g. supported keywords are {doublequote} {semicolon} {tab}, {cr}, {lf}, {null}). You can also use Hex chars to search (e.g. \xff44=something). Use blank value in the second part to replace with blank. Example: {null}=\0;"="";{semicolon}=\{semicolon};{cr}=\r;{lf}=\n;{tab}=\t;{columndelimiter}=\{columndelimiter};\xff32=;\xff31=;{null}=;

Settings UI

SSIS CSV File Destination - Setting UI
SSIS CSV File Destination - Setting UI
SSIS CSV File Destination - Setting UI
SSIS CSV File Destination - Setting UI

See Also

References

Articles / Tutorials

Click here to see all articles for [SSIS CSV File Destination] category
How to write data into CSV file in SSIS (GZip / Split)

How to write data into CSV file in SSIS (GZip / Split)

Introduction In this article, we will see how to read How to write data into CSV file in SSIS from SQL Server. We will also explain how to compress (gzip) and split  CSV files into multiple files. To illustrate, we will use ZappySys SSIS PowerPack, which includes several tasks to import/export data from multiples sources to […]

Load PostgreSQL Table Data to CSV File in SSIS

Load PostgreSQL Table Data to CSV File in SSIS

Introduction ZappySys provides high performance drag and drop connectors for PostgreSQL Integration. In this post you will see how to Load PostgreSQL Table Data to CSV File using SSIS PostgreSQL Source. PostgreSQL Source supports SQL language to query PostgreSQL data. PostgreSQL is a powerful, open source relational database with strong reputation for reliability, feature robustness, and performance. In […]

Copyrights reserved. ZappySys LLC.