Introduction
Le script suivant permet :
- de récupérer le résultat d’une requête SQL dans un fichier .csv,
- de renseigner les actions réalisées dans l’EventViewer,
- de retourner un code d’erreur (0 ou 1) suite à l’exécution du script.
Prérequis
Présentation du script
Variables :
[string] $server = "localhost" : Serveur SQL
[string] $database = "base" : Base SQL
[string] $query = "SELCT * FROM Table" : Requête ou procédure stockée
[string] $extractFilePath = "C:\test.csv" : Emplacemnt de génération du fichier .csv
[string] $delimiter = ";" : Délimiteur à utiliser pour la génération du fichier .csv
[int] $skipline = 1 : Nombre de ligne à tronquer (utile si l’on souhaite retirer la ligne d’entête.
Script
# Variables d'entrée
param
(
[string] $server = "localhost",
[string] $database = "base",
[string] $query = "SELECT * FROM Table",
[string] $extractFilePath = "C:\test.csv",
[string] $delimiter = ";",
[int] $skipline = 1
)
#MAIN
function main
{
#Variables calculées
$full_date = Get-Date;
$custom_date = $full_date.ToString("yyyyMMdd");
$DataSet = New-Object System.Data.DataSet;
$eventsource ="CsvFromSQL_Powershell";
$eventlogname = "Application";
[int] $errorcode = 1;
#Génération de la source d'evenements si inexistante
if ([System.Diagnostics.EventLog]::SourceExists($eventsource) -eq $false)
{
#write-host "Creating event source $eventsource on event log $eventlogname"
[System.Diagnostics.EventLog]::CreateEventSource($eventsource, $eventlogname)
#write-host -foregroundcolor green "Event source $eventsource created"
}
else
{
#write-host -foregroundcolor yellow "Warning: Event source $eventsource already exists. Cannot create this source on Event log $eventlogname"
}
<#
$logFileExists = Get-EventLog -list | Where-Object {$_.logdisplayname -eq $eventlogname}
if (! $logFileExists)
{
New-EventLog -LogName $eventlogname -Source $eventsource
}
#>
#Execution des fonctions et récupération du code d'erreur
$errorcode = Test-SQLConn -_server $server -_database $database;
$errorcode = sql_to_dataset -_server $server -_database $database -_query $query -_dataset $DataSet;
$errorcode = dataset_to_csv -_dataset $DataSet -_extractFilePath $extractFilePath;
return $errorcode;
}
#Test connection SQL
Function Test-SQLConn ($_server, $_database)
{
$errorcode=0;
$connectionString = "Data Source=$_server;Integrated Security=true;Initial Catalog=$_database;Connect Timeout=3;";
$sqlConn = new-object ("Data.SqlClient.SqlConnection") $connectionString;
trap
{
Start-Sleep -s 1;
Write-EventLog -LogName $eventlogname -Source $eventsource -EventID 1 -EntryType Error -Message "Cannot connect to server $_server or database $_database.";
$errorcode=1;
Write-Host $errorcode;
exit
}
$sqlConn.Open()
if ($sqlConn.State -eq 'Open')
{
$sqlConn.Close();
Start-Sleep -s 1;
Write-EventLog -LogName $eventlogname -Source $eventsource -EventID 0 -EntryType Information -Message "Connected to server $_server on database $_database.";
}
}
#Rempli un dataset à partir d'une requête SQL
function sql_to_dataset
{
param($_server, $_database, $_query, $_dataset);
$connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};";
$connectionString = [string]::Format($connectionTemplate, $_server, $_database);
$errorcode=0;
try
{
$connection = New-Object System.Data.SqlClient.SqlConnection -ErrorAction stop;
Start-Sleep -s 1;
Write-EventLog -LogName $eventlogname -Source $eventsource -EventID 0 -EntryType Information -Message "The SQL connection has been created successfully.";
}
catch
{
Start-Sleep -s 1;
Write-EventLog -LogName $eventlogname -Source $eventsource -EventID 1 -EntryType Error -Message "An error occurred when creating the SQL connection. Error Message : $_.Exception.Message";
$errorcode=1;
Write-Host $errorcode;
exit;
}
$connection.ConnectionString = $connectionString;
try
{
$command = New-Object System.Data.SqlClient.SqlCommand -ErrorAction stop;
Start-Sleep -s 1;
Write-EventLog -LogName $eventlogname -Source $eventsource -EventID 0 -EntryType Information -Message "The SQL command has been created successfully.";
}
catch
{
Start-Sleep -s 1;
Write-EventLog -LogName $eventlogname -Source $eventsource -EventID 1 -EntryType Error -Message "An error occurred when creating the SQL command. Error Message : $_.Exception.Message";
$errorcode=1;
Write-Host $errorcode;
exit;
}
$command.CommandText = $_query;
$command.Connection = $connection;
try
{
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter -ErrorAction stop;
Start-Sleep -s 1;
Write-EventLog -LogName $eventlogname -Source $eventsource -EventID 0 -EntryType Information -Message "The SQL DataMapper has been created successfully.";
}
catch
{
Start-Sleep -s 1;
Write-EventLog -LogName $eventlogname -Source $eventsource -EventID 1 -EntryType Error -Message "An error occurred when creating the SQL DataMapper. Error Message : $_.Exception.Message";
$errorcode=1;
Write-Host $errorcode;
exit;
}
$SqlAdapter.SelectCommand = $command;
try
{
$SqlAdapter.Fill($_dataSet) | Out-Null -ErrorAction stop;
Start-Sleep -s 1;
Write-EventLog -LogName $eventlogname -Source $eventsource -EventID 0 -EntryType Information -Message "The Dataset has been filled successfully.";
}
catch
{
Start-Sleep -s 1;
Write-EventLog -LogName $eventlogname -Source $eventsource -EventID 1 -EntryType Error -Message "An error occurred when filling the Dataset. Error Message : $_.Exception.Message";
$errorcode=1;
Write-Host $errorcode;
exit;
}
$connection.Close();
return $errorcode;
}
#Rempli un csv avec un dataset
function dataset_to_csv
{
param($_dataset, $_extractFilePath);
$errorcode=0;
try
{
$_dataSet.Tables[0] | ConvertTo-Csv -NoTypeInformation -Delimiter $delimiter | select -Skip $skipline | Set-Content $_extractFilePath -ErrorAction stop;
Start-Sleep -s 1;
Write-EventLog -LogName $eventlogname -Source $eventsource -EventID 0 -EntryType Information -Message "The CSV file has been created successfully.";
}
catch
{
Start-Sleep -s 1;
Write-EventLog -LogName $eventlogname -Source $eventsource -EventID 1 -EntryType Error -Message "An error occurred when generating the CSV file. Error Message : $_.Exception.Message";
$errorcode=1;
Write-Host $errorcode;
exit;
}
return $errorcode;
}
main