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.