Export hasil query MS SQL ke CSV

query_2_csv.ps1

#Connection Strings
$Database = "Database"
$Server = "SQLServer"

#Export File
$AttachmentPath = "C:\SQLData.csv"

# Connect to SQL and query data, extract data to SQL Adapter
$SqlQuery = "SELECT * FROM dbo.Test_Table"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Data Source=$Server;Initial Catalog=$Database;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

$DataSet = New-Object System.Data.DataSet
$nRecs = $SqlAdapter.Fill($DataSet)
$nRecs | Out-Null
#Populate Hash Table
$objTable = $DataSet.Tables[0]
#Export Hash Table to CSV File
$objTable | Export-CSV $AttachmentPath

upload_2_sftp.ps1

#Snapin : cmdlet untuk komunikasi dengan SFTP Server.
# download:
# http://www.k-tools.nl/index.php/download-sftp-powershell-snap-in/
 
#Add the SFTP snap-in
Add-PSSnapin KTools.PowerShell.SFTP
 
#Define some variables
$sftpHost = "bphmigas.waditra.com"
$userName = ""
$userPassword = ""
$localFile = "C:\Users\File\gm_20130901.csv"
 
#Open the SFTP connection
$sftp = Open-SFTPServer -serverAddress $sftpHost -userName $userName -userPassword $userPassword
 
#Upload the local file to the root folder on the SFTP server
$sftp.Put($localFile)
 
#Close the SFTP connection
$sftp.Close()