Showing posts with label SFTP. Show all posts
Showing posts with label SFTP. Show all posts

Wednesday, March 11, 2026

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:



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.

Updating your godaddy certs during the G2 intermediate hierarchy to the TLS Root CA - R1 hierarchy

  If you use GoDaddy TLS certificates you should be aware of some changes during the transition from the G2 Intermediate certificates to the...