Sometimes working with data can be challenging. As a SQL Developer, creating ETL solutions, is our responsibility all this data makes it to our users. Sometime data manipulation might be limited in SSIS, we are force to look for other alternatives. In my case, I use PowerShell, and I need a way to trap some import processing errors during the execution of the “Invoke-SQLcmd” cmdlet. Then, I will be able to investigate why some on my data wasn’t making it into my SQL tables.
I try to use PowerShell the “Try-Catch” to trap the error but it was wrong implementation to trap the error in the SQL Server PowerShell “Invoke-SQLcmd” command. So, How can I trap the command errors? Well, you can use “CommonParameters“, which are an additional set of parameters automatically provided by Windows PowerShell. In these additional set of parameters you will find the “-ErrorVariable” parameter which allow you to stores the error information in a given variable. The error variable you provide in any cmdlet will be overridden each time the cmdlet execute, unless you add an “+” in front of it so it can append to it and collect error information.
For more
Information about all “CommondParameters“, execute at the PS prompt:
[sourcecode language=”powershell”]
Help About_CommonParameters -full
[/sourcecode]
Now, my implementation was to use the “-ErrorVariable” parameter to trap and save the error message with the SQLquery script that failed. Also, I make sure to the variable to a null value after I saved the results to disk. In my scenario, the data imported could break my SQL query and I want to make sure I can identify these records.
So, I’m using the ErrorVariable parameter in my “Invoke-SQLcmd” command. Here’s a code snippet sample of it:
[sourcecode language=”powershell”]
$reccount = 1; $sqlerr = $null;
:
Foreach($item in $Data)
{
:
$outfile = "C:\ImportError\TSQLQUERY_$($reccount).sql";
$SqlQuery = "Insert into $tablename ($tblfields) values($fldsvalue);";
Invoke-Sqlcmd -Database $dbName -ServerInstance $ServerName `
-Query $SqlQuery -ErrorVariable sqlerr;
if($sqlerr -ne $null)
{
"/*Error Found – on record# [ $reccount ]:> `r`n"+$Error[0] + "*/ `r`n" `
| Out-File $outfile -Encoding ASCII;
$SqlQuery | Out-File $outfile -Encoding ASCII -Append;
"`r`n /* "+"".padleft(45,"=")+" */`r`n" `
| Out-File $outfile -Encoding ASCII -Append;
$sqlerr = $null;
}
:
$reccount++;
}
[/sourcecode]
Basically, I want to make sure my “$sqlerr” Error Variable is initialized as $null in both the beginning and after the error have been saved to the output file. Then, we can view later the Error message and the SQL script that error out to later view the output file displaying it in your SSMS or your favorite editor.
Keep in mind, this will not stop the errors from been displayed on screen. But, you could use another Common Parameter: ” -ErrorAction SilentlyContinue” to prevent displaying the errors.
Sample output file image:
I hope you find this code snippet useful!