SSIS CSV Generator Transform
 PreviousNext

SSIS CSV Generator Transform can be used to generate single or multiple CSV documents from any type of datasources (e.g. SQL Server, MySQL, Flat File, Excel) inside dataflow task.

Download SSIS PowerPack

Content

Video Tutorial

Coming soon...

Step-By-Step

In this tutorial we will learn how to generate CSV file from source using ZS CSV Generator Transform (In this case its 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. Now, Drag and Drop SSIS Data Flow Task from SSIS Toolbox.
    SSIS Data Flow Task - Drag and Drop
  4. Double click on SSIS Data Flow Task.
  5. Here, In Visual Studio, drag and drop the OLEDB Source and ZS CSV Generator Transform in the design panel and join the components with the blue arrow.
    SSIS CSV Generator - Drag and Drop
  6. In the above step you can use our Free ZS Dummy Data Source too. Click here for more information.
  7. We need OLEDB Connection for Data 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 OLEDB - Connection
  2. Now, click on New Button for create Connection.
    SSIS OLEDB - Connection
  3. Let's Configure Connection Manager, just Follow steps one by one as we have created.
    SSIS OLEDB - Connection
  4. Click on OK button to save connection configure setting UI.

How to generate CSV file using CSV Generator Transform.

  1. Double click on OLE DB Source for configure it.
  2. In the Connection Manager tab, just follow below image steps.
    SSIS OLEDB Connection Configure
  3. Click on OK button to Save OLEDB Source Editor UI Settings.
  4. Now, double click on ZS CSV Generator Transform to configure it.
  5. Set Output Mode to Output Single CSV for multiple input rows. Right click on Mappings and select Add Element(s) Below this node. In Add/Edit Attribute dialogbox for Select Add Multiple (Bound) and Click on Select All for all columns.
    SSIS CSV Generator - Drag and Drop
  6. Once you finished above step settings, you will see in left panel like below image.
    SSIS CSV Generator - Drag and Drop
  7. Here you can give Header and Footer as per your need.
    Header and Footer Options for SSIS CSV Generator Transform
  8. In the Option Tab, you can change Date/Time output format and other setting.
    Options for SSIS CSV Generator Transform
  9. Click on OK to save CSV Generator Transform UI settings.
  10. From the SSIS toolbox drag and drop Trash Destination on the data flow designer surface.
    SSIS Trash Destination - Drag and Drop
  11. Now single click on the CSV Generator Transform, once you see blue arrow from source ... connect it to Trash Destination.
  12. Double click on Trash Destination to Configure it.
    SSIS Trash Destination - Configure
  13. Thats all, Just Save and Run or Execute the Project.
    SSIS CSV Generator 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.
OutputMode Determines CSV format and output mode (e.g. one CSV for all input rows or one CSV for each input row)

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

Option Description
SingleFileFormat [0] Output Single Csv for multiple input rows (Batching Allowed)
MultiFileFormat [1] Output One Csv Document per parent row
LayoutInfo Defines shape of output CSV string
DateTimeFormat Default output format for datetime columns
Culture Culture setting for this task. This option is useful to control output format for certain data types (e.g. number, decimal) based on regional settings. In USA dot is used for decimal indicator but in Germany it may be comma. Leave this blank if you want to use system default settings.
UseColumnForHeaderFooter Document Header/Footer text come from upstream column rather than direct string
DocumentHeader Header text for document (Added before each document in the output)
DocumentFooter Footer text for document (Added after each document in the output)
ArrayBatchSize Specifies how many rows to include in each batch (Array records for single dataset array mode). 0=Include all in one batch. This setting is only valid for Mode=[Single Dataset Array]
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.
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.
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
AlwaysQuote Quotes around value regardless data type (E.g. quote around Numbers too). This option is only valid if you enable QuotesAroundValue.

Settings UI

SSIS CSV Generator - Setting UI
SSIS CSV Generator - Setting UI

Articles / Tutorials

Click here to see all articles for [SSIS CSV Generator Transform] category
How to read YouTube API data in SSIS (Videos, Channels, Playlists)

How to read YouTube API data in SSIS (Videos, Channels, Playlists)

Introduction In last few articles we saw how to read data from various Google Services. In this article we will see how to read YouTube API data in SSIS. This blog mainly focus on SSIS approach but steps mentioned to call Google APIs can be useful for any developer regardless which programming language or toolset […]

How to batch REST API requests in SSIS (Bulk Operation)

How to batch REST API requests in SSIS (Bulk Operation)

Introduction In our previous article we saw how to POST data to REST API using few different ways. Now let’s go one step further and discuss another common scenario to batch REST API requests in SSIS. For efficient data transfer many API provides you to submit multiple records in a single request. In this article […]

Copyrights reserved. ZappySys LLC.