SQL to Create CSV file and upload it using SSH

On a project I had in the past I had to create a comma separated file from a query and upload the results to a secure server using SSH.

Here is the commands I created to do it all in one script.


exec master.dbo.xp_cmdshell 'sqlcmd -S . -d Admin -E -s, -W -h -1 -Q "set nocount on SELECT * FROM YOURTABLE" > D:\YOURFILE.csv'
go

declare @thisdate smalldatetime
declare @thisyear nvarchar(4)
declare @thismonth nvarchar(2)
declare @thisday nvarchar(2)
declare @thislist varchar(8)
declare @query varchar(1000)

-- This block of code create the date stamp on the file name with a particular format
set @thisdate = getdate()
set @thisyear = year(@thisdate)
set @thismonth = month(@thisdate)
set @thisday = day(@thisdate)

if len(@thismonth) = 1 set @thismonth = '0' + @thismonth
if len(@thisday) = 1 set @thisday = '0' + @thisday
print @thismonth
print @thisday
set @thislist = @thismonth + @thisday + @thisyear
print @thislist
-- Rename the file if you need to make it unique
set @query = 'master.dbo.xp_cmdshell "rename D:\YOURFILE.csv YOURFILE_'+ @thislist +'.csv"'
print @query
exec(@query)
go
-- Calling WINSCP using a saved profile in the script
master.dbo.xp_cmdshell 'D:\YOURWINSCPPATH\WinSCP.exe -script=D:\SSHScript.txt'
go
-- Move your file to an archive is highly recommended so you can easily upload manually.
master.dbo.xp_cmdshell 'move D:\*.csv D:\Archive\'