PowerShell Script To Reading SQL Data, Creating CSV File And Push To SFTP/FTP Path

This article explains about the general task of quering SQL data and creating a CSV files. Also other daily task that can be automated to push data to a particular location like FTP/SFTP path.

The below powershell cmdlets are used to create csv files by querying the SQL server.

All the variables containing the server name and DB details:

#Variable to hold variable
$SQLServer = "XX.XX.XXX.XX"
$SQLDBName = "TestDB"
$uid ="domain\userID"
$pwd = "password123"
#SQL Query
$SqlQuery = "SELECT * from tableName;"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True;"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
#Creating Dataset
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$DataSet.Tables[0] | out-file "\\location\test.csv"

 

Note
When Intergrated Security ="true" is used, the cmdlets use current logged in user credentials - network credentails if Integrated Security ="false" we need to use the declared $uid and $pwd while establishing the connection.

The File is saved in the location or FTP path as test.csv

The SFTP is Secured FTP; the following powershell command helps to move any file to the SFTP location and it needs "Posh-SSH module" We need to install this module.https://github.com/darkoperator/Posh-SSH

The Powershell variable mentioned in the below code snippnet stores all the details of the SFTP or FTP server, where the files need to be moved.

#Declaring Variable
$sourceSFTPIP = "xx.xx.xxx.xx"
#IP address of the SFTP server
$LocalFilePath = "C:\test.csv";
$SFTPPath = ".\sharedFilePath\"
# folder location inside SFTP server
$secpasswd = ConvertTo - SecureString "password" - AsPlainText - Force
# the below object is used to key in the username and password automatically rather than promt the user to username and password
$mycreds = New - Object System.Management.Automation.PSCredential("username", $secpasswd)
# Module need to use SFTP Path
Install - Module - Name Posh - SSH# get sftp password this command can be used
if user need to feed in the user name and password at the time of running the script
# $credential = Get - Credential# Creating PS session to be used
$session = New - SFTPSession - ComputerName $sourceSFTPIP - Credential $mycreds - AcceptKey
# Move File using ps session
Set - SFTPFile - SessionId $session.SessionId - LocalFile $LocalFilePath - RemotePath $SFTPPath
$session.Disconnect()

We need to disconnect the session that was created to do this operation. Once it has run,  the file that is present in sharepath is moved to the SFTP server location.