I just had my first encounter with having to write a PowerShell script. The request seemed pretty harmless when I originally got it. The request was much like others I had gotten in the past where I needed to check to make sure that data had been loaded and kick off another process.
In the case of this new request I needed to validate a table had been updated with recent work, then move a bunch of folders to another directory where they would be processed by a third party application. Harmless enough right? Luckily the folder naming convention contained the two digit month, two digit day and two digit year as the first six characters. The rest of the folder name had to do with the application but was still significant.
I first thought this would be a nice SSIS package with a for each loop where I would get a directory listing then one by one loop through them and any folders where the date was yesterday would move to the processing folder. As I started down the SSIS route I quickly found this to be very complex. Had I been dealing with files it would have been easier as there are enumerators for files. I then turned to Google and found various C# and VB scripts that could accomplish this but I am not a .Net guy, I am a production DBA that came up through Systems Administration.
I pretty well knew at this point that I would have to do this through PowerShell. I have sat through a couple of PowerShell presentations at SQL Saturdays to try to get some exposure to it and have a good friend Aaron Nelson who is a PowerShell guru who keeps trying to get me to try more stuff with PowerShell. Last night after trying to find a way to do this with SSIS I turned to AskSSC and posted a question about my options. This morning I had one comment where I was told by Blackhawk-17 to consider VB or PowerShell to do the heavy lifting.
So what happened this morning? I finally bit the bullet and tried to have a go at it with PowerShell. I made a task list of the individual items that would need to be done in order to accomplish the task then sought out the powershell logic to make it happen. In the end I didn’t need the steps that I thought I would. I didn’t need to create an array of all the folder names and loop through them, I could simply use a wildcard with the “Move-Item” cmdlet. So how am I accomplishing this minor task with moving folders from the previous business day to a new folder? Simple, two lines of code.
First I set a variable for my date in the format of the folder structure. In my case it is MMDDYY
. $yest = (get-date).AddDays(-1).ToString(‘MMddyy’)
Then I reference the $yest variable in the command to move the folders.
. Move-Item C:PoshBranch$yest* c:PoshMoved
Pretty simple huh? I think I am sold on this PowerShell thing.
Now it is time to start building on the wee bit of knowledge I picked up today