Export Custom CSV + SFTP Integration

SSIS and Import/Export
1

Hello there,

I've a database in Microsoft SQL 2008r2, so what I need to do, I want to create an API or anything to extract 1 csv with custom table in CSV format and upload it to SFTP server every 4 hours, so what I need is to create integration between my database with the custom CSV table format with another platform using the SFTP connection.

Kindly help and instruct me on doing it.

2

You can schedule a PowerShell job step in the SQL Server Agent. You will have to play with the details yourself but in outline:

. C:\Batch\Scripts\PutFileSFTP.ps1

$SQLparams = @{
  'ServerInstance' = '.';
  'Database' = 'YourDB';
  'ErrorAction' = 'Stop';
  'Query' = 'SELECT * FROM dbo.YourTable' }

$Exportparams = @{
  'Path' = 'C:\Feeds\YourFile.csv' }

Invoke-Sqlcmd @SQLparams |
	Export-CSV @Exportparams -NoTypeInformation

$params = @{
	'LocalFile' = 'C:\Feeds\YourFile.csv';
	'RemotePath' = '/';
	'SFTPHost' = 'sftp01.somewhere.com';
	'UserName' = 'usersftp';
	'KeyFile' = 'C:\Batch\Scripts\SomeWhere.key'; #blank if no keyfile
	'CredPrefix' = 'C:\Batch\Scripts\credSomeWhere' } #blank if no password file

PutFileSFTP @params

The Credential file can be generated with something like the following in a temporary PowerShell agent job. (This will generate the file for the SQL Server Agent Account.)

#https://www.red-gate.com/simple-talk/sysadmin/powershell/powershell-and-secure-strings/
$LocalFilePath='C:\tmp'  
if (-not (test-path $LocalFilePath\cred_$env:UserName.txt ))
{
    "password" | ConvertTo-SecureString -AsPlainText -Force | ConvertFrom-SecureString | Out-File $LocalFilePath\cred_$env:Username.txt
}

The contents of PutFileSFTP.ps1 will be something like:

<#
PutFileSFTP.ps1
Uploads a file to a sftp site

PutFileSFTP -LocalFile <path> -RemotePath <path> -SFTPHost <URL> -UserName <string> -KeyFile <path> -CredPrefix <path>

eg
. C:\Batch\Scripts\PutFileSFTP.ps1

$params = @{
	'LocalFile' = 'C:\Batch\Outgoing\Outgoing.csv';
	'RemotePath' = '/';
	'SFTPHost' = 'sftp01.somewhere.com';
	'UserName' = 'usersftp';
	'KeyFile' = 'C:\Batch\Scripts\SomeWhere.key'; #blank if no keyfile
	'CredPrefix' = 'C:\Batch\Scripts\credSomeWhere' } #blank if no password file

PutFileSFTP @params

#>
Function PutFileSFTP
{
    Param($LocalFile, $RemotePath, $SFTPHost, $UserName, $KeyFile, $CredPrefix)
	Try
	{
		$ErrorActionPreference = "Stop"
		If ($CredPrefix -eq '')
		{
			$Password = new-object System.Security.SecureString
		}
		Else
		{
			$Content = "{0}_{1}.txt" -f $CredPrefix,$env:UserName
			$Password = get-content $Content -ErrorAction Stop| ConvertTo-SecureString	
		}
		$Credential = New-Object System.Management.Automation.PSCredential ($UserName, $Password) 

		Write-Output "$((Get-Date -Format s)) Upload $LocalFile to $SFTPHost$RemotePath."
		Write-Output "$((Get-Date -Format s)) Connecting to $SFTPHost..."
		
		Try
		{
		
			If ($KeyFile -eq '')
			{
				$session = New-SFTPSession -ComputerName $SFTPHost -Credential $Credential -AcceptKey
			}
			Else
			{
				$session = New-SFTPSession -ComputerName $SFTPHost -Credential $Credential -KeyFile $KeyFile -AcceptKey
			}

			Write-Output "$((Get-Date -Format s)) Uploading file..."
			Set-SFTPFile -SFTPSession $session -RemotePath $RemotePath -LocalFile $LocalFile -OverWrite
			Write-Output "$((Get-Date -Format s)) Upload complete."
		}
		Finally
		{
			if ($session = Get-SFTPSession -SessionId $session.SessionId)
			{
				Write-Output "$((Get-Date -Format s)) Disconnecting session..."
				$session.Disconnect()
			}
			$null = Remove-SftpSession -SftpSession $session
		}		
	}
	Catch
	{
		$ErrorMessage = $_.Exception.Message
		$FailedItem = $_.Exception.ItemName
		$emailFrom = "You@you.com"
		$emailTo = "You@you.com"
		$subject = "$SFTPHost Upload Problem"
		$body = "$ErrorMessage"
		$smtpServer = "mail.you.com"
		$smtp = new-object Net.Mail.SmtpClient($smtpServer)
		$smtp.Send($emailFrom, $emailTo, $subject, $body)

        Write-Output "$((Get-Date -Format s)) $ErrorMessage"
		Throw $ErrorMessage
		Break
	}
}

Good luck!

ps You will also have to install the Posh-SSH module on the server.

Install-Module -Name Posh-SSH