December 13, 2023 at 4:27 pm
Hello
Can someone help me with code to send file to sftp server please:
Here is my code:
# SQL Server query
$Query = "SELECT name from tblName"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=CWR-PP02;Database=testdb;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $Query
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
# Convert the DataSet to a DataTable
$DataTable = $DataSet.Tables[0]
# Create the text file
$filePath = "d:\Test\Test1.txt"
$stream = [System.IO.StreamWriter] $filePath
# Write the headers
$columnHeaders = $DataTable.Columns.ColumnName -join '|'
$stream.WriteLine($columnHeaders)
# Write the data
foreach ($row in $DataTable.Rows) {
$line = [System.String]::Join("|", $row.ItemArray)
$stream.WriteLine($line)
}
# Close the stream
$stream.Close()
December 14, 2023 at 5:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
December 19, 2023 at 4:53 am
Depending on the size of your dataset you may be able to use the Export-CSV command in powershell, getting to sftp requires another library though, something like WinScp.
Powershell allows you to import compatible libraries into your script and then use objects from them. The WinScp gui will actually generate code for you to run a transfer from a sample. You then take that sample code, paste into your script and use credentials you’ve generated from ConvertTo-SecureString.
Here’s an example from stackoverflow.
https://stackoverflow.com/questions/38732025/upload-file-to-sftp-using-powershell
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply