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...)


If using powershell version prior to 5.0, follow these steps:

    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


For powershell version 5.0 and greater, follow these steps:

    Run powershell as Administrator

    Run the following command;

        Install-Module SQLServer


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

(Change the path based on your local installation of WinSCP)

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


Create Export File from defined SQL variable

#-------------------SQL connection config
$SQLuser = "test"
$SQLpassword = "test"
$SQLdatabase = "DST19000AeriesDemo_ID"
$SQLServer = "localhost"
#-------------------SQL query
$SQL = "
    select cd[SchoolCode], nm[SchoolName] 
    from loc
"
#-------------------export file settings
$Delimiter = ","	     # use `t for tab-delited
$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 }


Create Export File with .sql input file

#-------------------SQL connection config
$SQLuser = "test"
$SQLpassword = "test"
$SQLdatabase = "DST19000AeriesDemo"
$SQLServer = "localhost"
#-------------------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

#post-process to clear out text qualifier ("")
if ($UseQuotes -eq $false) { (Get-Content $CSVfile) | Foreach-Object {$_ -replace '"', ''} | Out-File $CSVfile -Encoding utf8 }

#post-process to remove the header line
if ($IncludeHeader -eq $false) { (Get-Content $CSVfile) | Select-Object -Skip 1 | Out-File $CSVfile -Encoding utf8 }


SQL export with multiple .sql files

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

#define the SQL connection string
$user = "test"
$Pass = "test"
$DB = "DST19000AeriesDemo"
$Server = "localhost"

#get a list of each .sql file in the working directory
$files = Get-ChildItem -Path $mydir –File -Filter *.sql

#for each .sql file defined in $files, connect the SQL using the connection string, and create a csv file with the same name as the .sql file
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

# Load WinSCP .NET assembly
Add-Type -Path "D:\WinSCP\WinSCPnet.dll"
$MyDir = [System.IO.Path]::GetDirectoryName($myInvocation.MyCommand.Definition) # because WinSCP likes to run from the directory.
chdir $mydir

$user = "SQL_Loginname"
$Pass = "SQL_Loginpassword"
$DB = "Databasename"
$Server = "SQL_Servername"

$SFTPServer = "SFTP_servername"
$SFTPuser = "SFTP_username"
$SFTPpass = "SFTP_password"
$upath = "" #specifiy a remote path if necessary



$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."
}


# ----------------------------------------------------------------------SFTP options
# Setup session options
$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

    $ufiles = Get-ChildItem -Path $mydir –File -Filter *.csv
    foreach ($ufile in $ufiles) {
        Write-Host "uploading $ufile..." -NoNewline
        $session.PutFiles($ufile.FullName, $upath, $false, $transferOptions)
        Write-Host "Done."
    }    
}
finally
{
    # Disconnect, clean up
    $session.Dispose()
}