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:

  1. #Variable to hold variable  
  2. $SQLServer = "XX.XX.XXX.XX"  
  3. $SQLDBName = "TestDB"  
  4. $uid ="domain\userID"  
  5. $pwd = "password123"   
  6. #SQL Query  
  7. $SqlQuery = "SELECT * from tableName;"  
  8. $SqlConnection = New-Object System.Data.SqlClient.SqlConnection  
  9. $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True;"  
  10. $SqlCmd = New-Object System.Data.SqlClient.SqlCommand  
  11. $SqlCmd.CommandText = $SqlQuery  
  12. $SqlCmd.Connection = $SqlConnection  
  13. $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter  
  14. $SqlAdapter.SelectCommand = $SqlCmd   
  15. #Creating Dataset  
  16. $DataSet = New-Object System.Data.DataSet  
  17. $SqlAdapter.Fill($DataSet)  
  18. $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.
  1. #Declaring Variable  
  2. $sourceSFTPIP = "xx.xx.xxx.xx"  
  3. #IP address of the SFTP server  
  4. $LocalFilePath = "C:\test.csv";  
  5. $SFTPPath = ".\sharedFilePath\"   
  6. # folder location inside SFTP server  
  7. $secpasswd = ConvertTo - SecureString "password" - AsPlainText - Force  
  8. # the below object is used to key in the username and password automatically rather than promt the user to username and password  
  9. $mycreds = New - Object System.Management.Automation.PSCredential("username", $secpasswd)  
  10. # Module need to use SFTP Path  
  11. Install - Module - Name Posh - SSH# get sftp password this command can be used  
  12. if user need to feed in the user name and password at the time of running the script  
  13. # $credential = Get - Credential# Creating PS session to be used  
  14. $session = New - SFTPSession - ComputerName $sourceSFTPIP - Credential $mycreds - AcceptKey  
  15. Move File using ps session  
  16. Set - SFTPFile - SessionId $session.SessionId - LocalFile $LocalFilePath - RemotePath $SFTPPath  
  17. $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.