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.

Microsoft SQL Management Studio Connection Parameters

Connecting to an SQL server remotely is pretty easy but occasionally you might need some additional help, such as if your sql database uses a non standard port.  You have a couple of options for logging into your sql server but I'm going to cover 2 ways of connecting using the Database Engine login method.

As shown if you change the server type to Database Engine then you can put either the machine name or IP address (I have ipaddress because it's easy) with a comma then the port; if your database uses a NON-STANDARD Port (something other then 1443) make sure there are no spaces before or after the comma as shown above.  Set the Authentication method to SQL Server Authentication, put in your database username and password, then connect.

The second method is using the "Additional Connection Parameters".  You must have the database hostname or ip in the server name, then select options.

You will see a number of tabs that will be displayed and you want to select "Additional Connection Parameters" but be warned this is sent in clear text.

You can then put in your connection parameters in the box displayed above.  You can't just put in the connection string you do have to put in the server name or ip in the server name field on the login tab; once that is done the Connection string overrides the GUI settings.

below is a sample connection string:

SERVER=$IP,$PORT;USER=$DBUSER;PASSWORD=$DBPASSWORD

A successful connection will then be displayed in the Object Explorer as shown below.