Automating SQL Data Dump and SFTP File Transfer with Powershell and WinSCP on Windows

We work with a third party that takes care of a few systems for us that we automated and to make that work we had to script an automated process to export data from our SQL server and automatically upload it to an SFTP Server.

For this setup we are using WINSCP for doing the file transfer and a combination of a bat file and powershell for exporting the data out of SQL and automating the process.  I am using windows for automating this process.

What will be required:

WINSCP

Powershell

So install powershell and WINSCP on to the system.  

Open powershell as and administrator and install the Clobber the WINSCP Module and the SQLServer module

Install-Module -Name PowershellGet

Install-Module -Name SQLServer

Install-Module -Name WinSCP

Once done we will make two files, they will be running from the same directory.

SQLDataExport.ps1 and gen_sql_data.bat

The powershell file is fairly straight forward. It essentially gets and runs the sql query and saves it to a directory as a txt or csv.

SQLDataExport.ps1

$serverInstance = "$address, $port" 

$databaseName = "$databaseName" 

$Username = "$databaseUser" 

 $Password = "$databasePassword" 

$filePath = "$PATH\$FILENAME" + (Get-Date).ToString("yyyyMMddTHHmmss") + ".txt" 

Import-Module SqlServer 

$sqlQuery = "$YOURSQLQUERY" 

Invoke-Sqlcmd -ServerInstance $serverInstance -Username $Username -Password $Password -Database $databaseName -TrustServerCertificate -Query $sqlQuery | Out-File $filePath -Encoding UTF8

BatFile (This is where all the FTP Magic happens)

@ECHO OFF

powershell.exe -executionpolicy remotesigned -File $PATHTOPOWERSHELLSCRIPT.ps1

timeout /t 120

echo "FTP Started" >> seed_exReport.rpt

date /t >> exReport.rpt

time /t >> exReport.rpt

echo option batch abort > ftpPutCmds.txt

echo option confirm off >> ftpPutCmds.txt

echo open sftp://$USERNAME@$SFTPURL -privatekey=$SFTPPRIVATEKEY.ppk -passphrase=$PASSWORD -hostkey=acceptnew >> ftpPutCommands.txt

echo option transfer binary >> ftpPutCmds.txt

echo cd seed >> ftpPutCmds.txt

echo put $SQLDATA_*.txt >> ftpPutCmds.txt

echo close >> ftpPutCmds.txt

echo exit >> ftpPutCmds.txt

"C:\Program Files (x86)\WinSCP\WinSCP.exe" /console /script=ftpPutCmds.txt /log="$PATH2Error.log" /loglevel=1* /logsize=5*100M

echo "FTP Finished" >> seed_exReport.rpt

IF EXIST *.txt (move *SQLDATA_*.txt Old\) REM move old file to a folder called old

date /t >> exReport.rpt

time /t >> exReport.rpt

echo. >> exReport.rpt

Once done you can setup the task scheduler to run the batfile.  You will need the script to start in the directory that the bat file and powershell file are in.