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 for extracting data, Python, and automating these and other common DBA tasks. 



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 Python
  • Scheduling tasks using Task Scheduler
  • Using an FTP client to send data to SFTP sites
  • Creating templates for the purpose of importing and inserting data into Aeries


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


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"


Python:

Python is the second most popular coding language in the world.  It is a powerful tool for data analysis.  It is also a great tool used for data extraction, transformation, and loading (ETL).