While I am not new to the world of coding (and not just T-SQL) I am somewhat new to Powershell. I cobbled together a monitor process that works with GoldenGate a while back and just recently made it less ugly and improved its performance. Beyond that, I am still on the green side.
Enter my current project – automating the SQL portion of our deployments using Powershell. Before this I was taking the list of items to be deployed and generating my own scripts to run using the Red Gate SQL Compare tool. Then came the day when I called one of the scripts from a developer in Powershell. There was no going back. I was hooked. I immediately saw how amazing this could be. And also how my work load would drop.
While I am doing my part in Powershell, there is also some other development going on with the end the developers use – in other words, things are in flux. I have code for the old way they do things and the new way. The new is better in that some of the things I need are built-in to certain aspects so I am able to grab pieces of information and get the things from that I need. With the old, this is not the case. Instead I am passing that information in through parameters.
I had the idea of passing in strings that would then be broken up and inserted into a multidimensional array. With that, I could loop through the array one element at a time, have all the things I needed for each deployment and just sit back and watch the magic happen.
Uhhhhh…did I mention I was green in Powershell?
I had another array already going so I thought I had this. I don’t know if it was because it was late in the day, or if I was a little too confident in my array abilities, but the darn thing was not happening the way I had envisioned. I set the code aside to revisit later with a fresh pair of eyes and a brain that wasn’t so full.
Fast forward to the following week – went back to the same code and FIGURED OUT HOW TO DO IT IN 5 MINUTES. Not only that, but it was far less code than I had previously attempted before.
Here is how I have handled this. Obviously, I would be replacing the text in the strings with information that is relevant to my deployments but the concept is the same. Once I have the array created, I have all the elements I need and it is just a matter of knowing where they are in the array and accessing them.
$strings = @("The cat in the"
,"hat ate a rat"
,"and then sat and"
,"got fat from the rat")
$array = New-Object System.Collections.ArrayList
$array.Clear();
foreach($item in $strings)
{
$array.Add(@($item.Split(" "))) | out-null;
}
for ($i = 0;$i -lt ($array | Measure-Object).count;$i++)
{
Write-Host $array[$i][0]
}
For the sample I am writing the first element. For my project the first element might be the server name for the deployment or some other relevant piece of information and it would be in the same place throughout the array. Since variables and their contents persist in Powershell I added the statement to clear the array before it is loaded. I am also wrapping the code up in a function to keep things tidy.
If you find this useful let me know. Enjoy. 🙂
