SSIS Secure FTP CSV Destination
 PreviousNext

SSIS Secure FTP Destination Connector for CSV File can be used to write data in CSV file format to Secure FTP Storage. 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 to ZS Secure Ftp CSV File Destination. You can use ZS DummyData Source too for practice it. We are going to use local FTP Server, Here, you can also Download and Install Rebex Tiny SFTP Server.
  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 Secure FTP CSV File Destination in the design surface and join the components with the blue arrow.
    SSIS Secure FTP CSV File Destination and OLE DB Source - Drag and Drop
  6. Here, you can use our Free ZS Dummy Data Source for practice. Click here for more information.
  7. But, we are going through OLE DB Source so we need OLE DB Connection and Secure SFTP Connection for Secure FTP CSV File Destination.

How to create Secure SFTP Connection.

  1. Right click on Connection Managers Panel to Create New SFTP Connection for Server, and Context Menu will appear, Select New Connection from the Context Menu.
    SFTP Task Operations - Connection
  2. Select ZS-SFTP Connection Manager from the Connection Managers list and Click on Add Button.
    SFTP Task Operations - Connection
  3. Now in Connection Manager UI, Select SFTP - SSH File Transfer Protocol and Enter your Host name and Select Logon Type Username and Password and Enter it, and Click Test Connection. If test successful then hit OK to close the connection manager dialogbox.
    SFTP Task Operations - Connection
  4. Click on OK to save Secure FTP configure setting UI.

How to create OLE DB Connection.

  1. Let's, Right click on Connection Managers Panel to Create OLE DB Connection, so you can use Source and Context Menu will appear, Select New OLE DB 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 Test Connection to see correct configure it.
  5. Click on OK button to save connection configure setting UI.

How to Extract Data from SQL Server Table to Secure FTP 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 Settings UI.
  4. Lets configure Secure FTP CSV File Destination, Double click on it, In the File Connection Managers, select the Secure FTP connection created before.
    SSIS Secure FTP CSV File Destination - Connection Configure
  5. In the Component Properties, set the Append property to False, enter the File Path and set the Overwrite file if exists to True.
    SSIS Secure FTP CSV File Destination - Component Properties
  6. In the Input Columns Tab, check the columns that you want load data.
    SSIS Secure FTP CSV File Destination - 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.
    SSIS Secure FTP CSV File Connection - 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}=;

Setting UI

SSIS Secure FTP CSV File Connection - Setting UI
SSIS Secure FTP CSV File Connection - Setting UI
SSIS Secure FTP CSV File Connection - Setting UI
SSIS Secure FTP CSV File Connection - Setting UI

See Also

Articles / Tutorials

Copyrights reserved. ZappySys LLC.