Description

Attendees will be able to create new extracts for the purpose of integrating with third party vendors or to utilize in-house data projects. Import/Export methods include bulk insert, using powershell (SQLPS) for extracting data, SSIS Packages, and automating these and other common DBA tasks.


Time Allotment
120 Minutes


Expected Outcomes


Upon completing this Aeries conference session, attendees should be familiar with:

  • Creating and maintaining data extracts using Powershell
  • Creating and maintaining data extracts using SQL Server Integration Services (SSIS)
  • Check the history of running jobs and monitor job status
  • Scheduling tasks using Task Scheduler
  • Using an FTP client to send data to SFTP sites
  • Creating temp tables for the purpose of importing/inserting data into Aeries tables
  • Have a better understanding of general security practices in regards to working with student data


Content:


Powershell:

PowerShell is a task-based command-line shell and scripting language built on .NET. PowerShell helps system administrators and power-users rapidly automate tasks that manage operating systems and processes.

Powershell can be used to automate many common Aeries DBA tasks.

Below are some common powershell prerequisites to assist in exporting data;

You will have to run under SQLPS (SQL PowerShell extensions which will be on a typical SQL server or you must install SQL feature pack files...)

SQL Server 2016 Feature Pack (newest available connects to down level servers just fine)

https://www.microsoft.com/en-us/download/details.aspx?id=52676

1. ENU\x64\SQLSysClrTypes.msi

2. ENU\x64\SharedManagementObjects.msi

3. ENU\x64\PowerShellTools.msi

Open Powershell ISE as Administrator.

Run the following command;

Import-module SQLPS


SSIS Packages:

SSIS is a platform for data integration and workflow applications. It features a data warehousing tool used for data extraction, transformation, and loading (ETL). The tool may also be used to automate maintenance of SQL Server databases and update data.

https://docs.microsoft.com/en-us/sql/integration-services/import-export-data/import-and-export-data-with-the-sql-server-import-and-export-wizard?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/integration-services/integration-services-ssis-packages?view=sql-server-2017


Security:

https://support.aeries.com/solution/articles/14000080138-network-security-basics

https://support.aeries.com/solution/articles/14000067944-introduction-to-aeries-security


Loading requirements.ps1

Set-ExecutionPolicy -ExecutionPolicy Unrestricted


Loading SQLPS.ps1

Import-Module SQLPS


Adding WinSCP functions to Powershell

https://winscp.net/eng/docs/library_powershell 


Loading WinSCP.ps1

Add-Type -Path "D:\WinSCP\WinSCPnet.dll"




SQL Export.ps1

#-------------------SQL connection config
$SQLuser = "test"
$SQLpassword = "test"
$SQLdatabase = "DST18000AeriesDemo_ID"
$SQLServer = "localhost"
#-------------------SQL query
$SQL = "
    select cd[SchoolCode], nm[SchoolName] 
    from loc
"
#-------------------export file settings
$Delimiter = "," 
#$Delimiter = "`t" # use `t for tab-delimited.
$CSVfile = "LOC.csv"
$IncludeHeader = $true       # set to $false to remove header from CSV file.
$UseQuotes = $true           # set to $false to not use double quotes.

Invoke-Sqlcmd -ServerInstance $SQLServer -database $SQLdatabase -username $SQLuser -password $SQLpassword -query $SQL | Export-Csv $CSVfile -NoTypeInformation -Encoding UTF8 -Delimiter $Delimiter

if ($UseQuotes -eq $false) { (Get-Content $CSVfile) | Foreach-Object {$_ -replace '"', ''} | Out-File $CSVfile -Encoding utf8 }
if ($IncludeHeader -eq $false) { (Get-Content $CSVfile) | Select-Object -Skip 1 | Out-File $CSVfile -Encoding utf8 }


SQL export with .sql input file

#-------------------SQL connection config
$SQLuser = "test"
$SQLpassword = "test"
$SQLdatabase = "DST18000AeriesDemo_ID"
$SQLServer = "localhost\sqlexpress"
#-------------------SQL query
$SQLfile= "LOC.sql"
#-------------------export file settings
$CSVfile = "LOC.csv"
$Delimiter = "," 
$IncludeHeader = $true       # set to $false to remove header from CSV file.
$UseQuotes = $true           # set to $false to not use double quotes.

Invoke-Sqlcmd -serverinstance $SQLServer -database $SQLdatabase -username $SQLuser -password $SQLpassword -InputFile $SQLfile | Export-Csv $CSVfile -NoTypeInformation -Encoding UTF8 -Delimiter $Delimiter

if ($UseQuotes -eq $false) { (Get-Content $CSVfile) | Foreach-Object {$_ -replace '"', ''} | Out-File $CSVfile -Encoding utf8 }
if ($IncludeHeader -eq $false) { (Get-Content $CSVfile) | Select-Object -Skip 1 | Out-File $CSVfile -Encoding utf8 }

SQL export with multiple .sql files

$MyDir = [System.IO.Path]::GetDirectoryName($myInvocation.MyCommand.Definition) # because WinSCP likes to run from the script's directory.
chdir $mydir

$user = "test"
$Pass = "test"
$DB = "DST18000AeriesDemo"
$Server = "localhost"

$files = Get-ChildItem -Path $mydir –File -Filter *.sql

foreach ($file in $files) {
    $csv = $file.BaseName + ".csv"
    write-host "Creating $csv extract from " $file.Name "..." -NoNewline
    Invoke-Sqlcmd -serverinstance $Server -database $DB -username $User -password $Pass -InputFile $file | Export-Csv $csv  -NoTypeInformation -Encoding ASCII
    #(Get-Content students.csv) | Foreach-Object {$_ -replace '"', ''}| Out-File students.csv -Encoding ASCII
    write-host "Done."
}


Upload to SFTP only using WinCSP library

# Load WinSCP .NET assembly
Add-Type -Path "D:\WinSCP\WinSCPnet.dll"

$CSVfile = "loc.csv"

# Setup session options
$sessionOptions = New-Object WinSCP.SessionOptions -Property @{
    Protocol = [WinSCP.Protocol]::Sftp
    HostName = "localhost"
    UserName = "test"
    Password = "test"
    SshHostKeyFingerprint = "ssh-rsa 1024 cc:3d:0c:d5:a4:25:ec:59:90:b9:09:97:96:9c:45:55:5b:13:11"    
}


$session = New-Object WinSCP.Session

# Connect
$session.Open($sessionOptions)
Write-Output "open"
$transferOptions = New-Object winscp.TransferOptions
$transferOptions.TransferMode = [WinSCP.TransferMode]::Ascii

$session.PutFiles($CSVfile, "/test/",$false, $transferOptions)
Write-Output "uploaded"

# Disconnect, clean up
$session.Dispose()



Export and send to SFTP server using WinSCP library

Add-Type -Path "D:\WinSCP\WinSCPnet.dll"

$SQLsvr = "localhost"
$SQLdb = "DST18000AeriesDemo"
$SQLun = "test"
$SQLpw = "AoT1fTR1HlOLleZ4IvUI"
$SQL = " select cd, nm from loc"

$CSVfile = "d:\test\loc.csv"
$Delimiter = ","  
# $Delimiter "`t"  # tab delimited
$hasHeader = $false
$useQuotes = $false

$SFTPServer = "test.rebex.net"
$SFTPuser = "demo"
$SFTPpass = "password"
$upath = "/upload"

$results = invoke-sqlcmd -ServerInstance $SQLsvr -Database $SQLdb -Username $SQLun -Password $SQLpw -Query $SQL


$results | export-csv -Path $CSVfile -NoTypeInformation -Delimiter $Delimiter

if ($useQuotes -eq $false) { (Get-Content $CSVfile) | Foreach-Object {$_ -replace '"', ''} | Out-File $CSVfile -Encoding utf8 }
if ($hasHeader -eq $false) { (Get-Content $CSVfile) | Select-Object -Skip 1 | Out-File $CSVfile -Encoding utf8 }


$sessionOptions = New-Object WinSCP.SessionOptions -Property @{
    Protocol = [WinSCP.Protocol]::Sftp
    HostName = $SFTPServer
    UserName = $SFTPuser
    Password = $SFTPpass
    GiveUpSecurityAndAcceptAnySshHostKey = "True"
    #SshHostKeyFingerprint = "ssh-rsa 1024 "    
}


$session = New-Object WinSCP.Session


try
{
    # Connect
    $session.Open($sessionOptions)
    $transferOptions = New-Object winscp.TransferOptions
    $transferOptions.TransferMode = [WinSCP.TransferMode]::Ascii
    Write-Host "uploading $ufile..." -NoNewline
    $session.PutFiles($CSVfile, $upath, $false, $transferOptions)
    Write-Host "Done."  
}
finally
{
    # Disconnect, clean up
    $session.Dispose()
}