Extracting data using multiple SQL scripts using a single PowerShell script
Extracting data using multiple SQL scripts using a single PowerShell script
Introduction
This is an adaptation of the script 'Installing multiple SQL scripts using a single PowerShell script'
This PowerShell script demonstrates how to read from a source file which SQL files need to be processed.
Each SQL script will be read and executed against the MS SQL server.
Each SQL script can contain multiple batched SQL commands.
Each batch will be processed separately.
The returned data will be saved in a separate CSV file with a datetime stamp in its filename.
Using the code
The solution consists of at least three files:
-
Generate_CSV_FromSqlFiles-PS2.ps1
The actual Powershell script. This one runs under Powershell version 2 and 3
-
Generate_CSV_FromSqlFilesList.txt
This file holds a list of all the SQL files that need to be processed.
The processing is done using the top down approach. Comments can be added on a single line by placing # in front of it.
Empty lines will be ignored.
Note: This file must be placed in the same folder as where the SQL files are stored.
-
Example.sql
This is an example sql script file that can be used by the script Generate_CSV_FromSqlFiles-PS2.ps1.
Important to note here is that eventhough the different SQL statements can be separated with the batch separating command GO.
Each batch must contain a select statement.
Setting up
Step 1: Configuring the PowerShell Script variables
Within the Generate_CSV_FromSqlFiles-PS2.ps1 the function Initialize needs to be modified to reflect your environment.
# Initialize the Script variables
function Initialize
{
#UNC mapping configuration. Configure this first then do the General Configuration!
$script:UNCPath = "<unc path where the result file(s) will be stored>";
#Provide the Domain\User name that will access the UNC Path
$script:LogInName ="<domain>\<username>";
#Provide the password for the above LogInNam
$script:LogInPwd = "<password>";
# Make sure only upper case letter is used. Using Lower Case will break this script!
# The mapped drive letter will be Z. Change this to reflect your own environment
# So do not remove the .Upper() command!
$script:MappedDriveLetter ="Z".ToUpper();
#General configuration items
$Script:SQl_File_Location = "<source path to sql files>";
$Script:SQLInstallList = "Generate_CSV_FromSqlFilesList.txt";
$Script:ConnectionString ="Server={0};database=<databasename>;user id={1};password={2};Trusted_Connection={3}";
#Set the source path and folder to hold the logging information of the current run.
$Script:HtmlLogFile = [string]::Format("<source path to logfile>\Logs\ExtractedDataCsvLogFile{0:yyyy_MM_dd}.html", [DateTime]::Now);
#Setup the SQL database connection and login information
$script:SqlInstance ="<servername>\<instancename>";
$script:SqlLogInName = "";
$script:SqlLogInPwd = "";
$script:UseTrustedConnection = $true;
#Set the SQL Command Timeout to 60 seconds. Increase this if the server takes longer than this to respond
$script:CommandTimeOut = 60;
#Provide the CSV separator Character. Default is semi-coln
$script:CSV_SeparatorChar = ";";
#Do not prefix the CSVTargetFolder with a backslash \.
#To target the root of the share set this variable to empty string ""
$script:CSVTargetFolder = "";
#Set up logging collector
$Script:InstalledListSB = New-Object System.Text.StringBuilder;
Create-HtmlHeader
#Configure SMTP
$Script:SMTPServer = "<smtp server for sending error emails>";
$Script:FromSender = "NoReply-ExtractCsv@domainName.ext";
# Use ; to separate multiple email recipients
$script:ToRecipient = "Recipient_1@domainName.ext;Recipient_2@DomainName2.ext2";
$script:Subject = "Script Execution";
#Track whether a script error has occurred or not
$script:ErrorHasOccured = $false;
$script:ScriptHadError = $false;
#Now Construct the TargetFolder and Map the Network Drive
#Map the drive now and construct the correct folder name that will receive the result folder.
#The result will be stored in the $script:MappedTargetFolder variable
MapTo-TargetFolder $Script:UNCPath;
}
The following variables need to be set to the appropriate value
-
UNCPath
Provide here between the quotes the actual UNC path that will be used to create a drive mapping to. UNC paths have the format \\servername\sharename
Hidden shares can be used.
-
LogInName
This is the user login name that has read-write access to the UNCPath. The default format is domainName\UserName.
-
LogInPwd
Provide here the password that belongs to the LogInName.
-
MappedDriveLetter
The drive letter that will be used to map the unc path to so that the data can be written to a 'normal' drive letter.
The provided drive letter is the letter that will be used by default. If this drive letter is unavailble the next drive letter
closer to the letter A will be used. I.e. letter Z is unavailble then letter Y will be used. The default value should be letter Z.
-
SQl_File_Location
This is the folder that holds the SQL files that will be executed and where the SQLInstallList file resides.
-
ConnectionString
This is the connection string for a SQL Server. Replace <databasename> with the actual database name to which a connection will be made
-
HtmlLogFile
Replace <source path to logfile> with the actual folder path to the Log folder where the log files will be stored.
-
SqlInstance
Replace <servername>\<instancename> with the actual servername and instance name to the database.
If the database does not use instance name than remove the \<instancename> portion.
-
SqlLogInName
SqlLogInPwd
UseTrustedConnection
When UseTrustedConnection has the value of $true then any value supplied in SqlLoginName and SqlLoginPwd will be ignored.
When UseTrustedConnection has the value of $false then a SqlLoginName and both SqlLoginName and SqlLoginPwd need to be set to the correct login credentials.
-
CommandTimeOut
The SqlCommand execution timeout in seconds. When the sql command takes longer than the CommandTimeOut to complete there are two options to fix this issue
Option 1: Increase the value of the CommandTimeOut
Option 2: Revisit the SQL Select statement and see if the performance can be improved by optimizing the query it self.
-
CSV_SeparatorChar
The CSV field separator character that will be used while writing the data to the csv file
-
CSVTargetFolder
This is the folder on the share that will hold the csv data files. Leave this blank if the data must be written to the root folder of the share
-
SMTPServer
Provide here the SMTP server name that accepts relaying.
-
FromSender
The email address that will appear in the FROM section.
-
ToRecipient
List of email addresses of the recipients that will receive an email when the script runs into error.
Place a semicolon between the different email addresses. Or just provide a single email address.
- Subject
The base Subject name of the email that is being sent
Step 2: Setting PowerShell ExecutionPolicy
The PowerShell Execution Policy needs to be set on the machine that will execute this PowerShell script.
By default the Execution Policy is set to restricted.
This inhibits the execution of any PowerShell script.
This limitation can be modified through the PowerShell's Set-ExecutionPolicy command.
See also the PowerShell help : Get-Help Set-ExecutionPolicy.
Ideally one would use a signed script but if the script will undergo revisions then the unrestricted execution policy could be used.
Be aware that this unrestricted policy setting may pose a security risk for the machine that runs the PowerShell script.
Step 3: Testing the setup
Now that everything is set, open the PowerShell Script in PowerShell ISE environment and run the script.
If everything is correctly setup the script will execute the SQL scripts and one or more CSV files will be created at the supplied UNC Path.
Also for each executed SQL statement there should be a log file generated as well in the folder location as supplied at HtmlLogFile variabel.
Step 4: Scheduling the task
In order to run the PowerShell script as a scheduled task the script needs to be called through the following command line command:
powershell.exe -NoProfile -NonInteractive -File "\\path\Generate_CSV_FromSqlFiles-PS2.ps1"
Points of interest
The default execution policy for PowerShell is restricted. This means that even the self written PowerShell scripts can only be executed interactively.
Not all .NET Methods are available in PowerShell. For instance under PowerShell 1 and 2 the System.Net.Mail.SmtpClient does not possess the method .Dispose(); This method is available in PowerShell 3.
In PowerShell 3 the following can be done
$SplitChars = @("\r","\n");
$HelpText = $TmpText.Split($SplitChars, [System.StringSplitOptions]::RemoveEmptyEntries);
The above cannot be done under PowerShell 2. There you need to loop through the $TmpText and deal with the empty lines.
With relative little effort the SQLConnection, SQLCommand objects can be replaced with different Connection dan Command objects. e.g. with OleDbConnection and OleDbCommand objects.
The connection string must be changed accordingly and the supplied SQL Select statements need to be compatible with the new database connection. An overview of different connection strings can be obtained at www.connectionstrings.com
License
Freeware. You can modify the script to suit your needs. Please do leave the original credits in tact.