Description
This session will discuss how to install PowerShell Core, how to create automated scripts, and how to deploy them in your environment.
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).