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.


Install Visual Studio 2019 and the "Data Storage and Processing" workload:



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"



SSIS and PowerShell Examples Attached: