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