How to SFTP file via SSIS

 

We are using SSIS 2008R2 and the default FTP component does not support Secure FTP. A workaround is to utilize the Winscp program via Execute Process Task

utility and prepare a Winscp script for this purpose. Link below has all the details

 

 

http://winscp.net/eng/docs/guide_ssis

SQL Server SQLCMD Basics

https://www.simple-talk.com/sql/sql-tools/sql-server-sqlcmd-basics/

How to output table or query to a flat file

Recently, I have a request to output a table/query to a flat file and gets Sftp to an external server

First step is to prepare the .csv file with column names.

It appears that we have two options here using xp_cmdshell

1. BCP : need to add header.csv to the contents.csv

Sample code below:

BCP "DECLARE @colnames VARCHAR(max);SELECT @colnames = COALESCE(@colnames + ',', '') + column_name from my_db_name.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='my_table_name'; select @colnames;" queryout HeadersOnly.csv -c -T -Smy_server_name

BCP my_db_name.dbo.my_table_name out TableDataWithoutHeaders.csv -c -t, -T -Smy_server_name

copy /b HeadersOnly.csv+TableDataWithoutHeaders.csv TableData.csv

del HeadersOnly.csv
del TableDataWithoutHeaders.csv

2. SQLcmd it will include headers and add space paddings . Also the file extension is limited compared to BCP

 

Good article to share

http://stackoverflow.com/questions/1355876/export-table-to-file-with-column-headers-column-names-using-the-bcp-utility-an

Variables and columnName inside Dynamic Sql

IN SSIS package,

In expression, if you want to show the value of the variable, use double quote “, if you want to put the value under the single quote , use +’” @val +”’

 

FOR Dynamic TSQL

 

if you need to put quotation for the string column in the String concatenation, use four quotations ‘