Monitoring and Starting Data Collector Sets with Powershell

I figure it's about time I posted an update to my previous post on Monitoring, Starting, and Stopping Perfmon Performance Counter Logs using VBScript. In the years since that post, a few operating systems came out and many much more interesting languages happened by. So this update is in Powershell. You could do it in pretty much any language, but Powershell seems to be my weapon of choice lately.
The basic premise here is that we want our Data Collector Sets to always be running. Seems like a reasonable request, but for various reasons, Data Collector Sets just... stop. This script should be run regularly (every 5 minutes?) to ensure that the sets keep collecting data. In the event that the script finds some work to do, it will send you an email.
#======= START Config ======== $emailServer = "1.2.3.4"; $fromAddress = "you@yourcompany.com"; $toAddress = "you@yourcompany.com"; $ignorePattern = '^(RandomCollectorSet|OSTest)$'; #======== END Config ========= $ErrorActionPreference = "SilentlyContinue" $serverName = Get-Content env:computername; $emailBody = "" $collectors = New-Object -COM Pla.DataCollectorSetCollection; $collectors.GetDataCollectorSets($null, "Service\*"); $collectors._NewEnum | ?{$_.Status -ne 1 -and $_.Name -notmatch $ignorePattern} | %{ $_.Start($true); if($_.Status -eq 1) { $emailBody += "Started $($_.Name)`n" } else { $emailBody += "Failed to start $($_.Name)`n" } }; if($emailBody -ne "") { $smtp = new-object Net.Mail.SmtpClient($emailServer); $smtp.Send( "$serverName <$fromAddress>", $toAddress, "Started Data Collector Set(s)", $emailBody); }
If you want the script to keep any of the non-user created collectors running, modify the namespace parameter of the GetDataCollectorSets method call.
Revisiting Embedded Text Qualifiers and the SSIS Flat File Connection Manager

To quickly summarize the problem at hand, the Flat File Connection Manager doesn't support embedded Text Qualifiers. The reason this is a big issue is because the most commonly used text qualifier is a double quote (") and that's also a commonly used punctuation mark. That means if you have a CSV to import and lines that looks like this:
"one","1","two","2","th""r""ee","3","asdf"
"""left","right""","mid""dle","w"",tf","w,""tf",",""omg","omg"","
You would expect to import
| one | 1 | two | 2 | th"r"ee | 3 | asdf |
| "left | right" | mid"dle | w",tf | w,"tf | ,"omg | omg", |
That's not what you get, you get an error. I've seen a few different approaches to working around this known issue:
- Replacing embedded text qualifiers with some other token - " in this case
- Import each file as a single column and parse the row in a script task, handling error rows
What I'd like to explore in this post is using regular expressions to transform the entire file in to something that can be natively consumed by SSIS. There are two options for dealing with this:
- Tokenizing the quotation marks found within the cells
- Change the text qualifier
I chose to change the text qualifier because SSIS natively supports multiple character text qualifiers. This will allow me to pick something ridiculous that is incredibly unlikely to appear in the data naturally. Changing the text qualifier also means we won't have to add any extra transformations as we would if we tokenized the embeded quotation marks (to turn them back in to quotation marks).
Restarting Automatic Services using Powershell

What? Why would you need to restart an automatic service? Because they die sometimes - there's probably some very good reasons for this, but I don't know them. What I do know is they die from time to time and don't come back. Sometimes they never get around to starting after a reboot.
I run the following Powershell script every 5 minutes to restart the ones that got tired:
#======= START Config ======== $emailServer = "1.2.3.4"; $fromAddress = "you@yourcompany.com"; $toAddress = "you@yourcompany.com"; $ignorePattern = '^(ShellHWDetection|MMCSS|sppsvc|clr_optimization.*)$'; #======== END Config ========= Get-WmiObject Win32_Service | ? { $_.StartMode -eq 'Auto' ` -and $_.State -ne 'Running' ` -and $_.Name -notmatch $ignorePattern } | % { $svcName = $_.Name; $serverName = Get-Content env:computername; Start-Service -Name $_.Name -EA SilentlyContinue; $smtp = new-object Net.Mail.SmtpClient($emailServer); $smtp.Send( "$serverName <$fromAddress>", $toAddress, "Started service ""$svcName""", "Started service ""$svcName"" on $serverName.") }
$ignorePattern is a regular expression for identifying what services to ignore (so services that you don't care to try and restart).
Dealing With Long Running SSAS Queries using Powershell

So, your Analysis Services instance keeps eating all the memory on your server despite proper memory configuration? This happens when queries get too big for their britches - SSAS memory limits are basically soft limits. Whatever an active query requires in terms of resources, SSAS will attempt to provide it. Even if it has to page the entire operating system out to disk to do it. You can mitigate this with good cube design, better aggregations, user training, etc - but it's bound to happen. When your cube gets big enough, one of your users will blow the entire server up.
VersionOne Nag: Reminding Us To Burn Our Points
You'll forget - we all do. After a hard day of work, the last thing that pops in to your head is "hey, I should go update this story in VersionOne before I go home." Oh no, the only thing you're thinking about is how bad traffic will be, what you're having for dinner, your trip to Seattle, which bar has the best drink specials tonight, etc. So accepting that we forget to burn points now and then, why don't we just make a script to remind us? While you're at it, might as well add a reminder for when you accidentally close a story without setting the ToDo points to zero.
Luckily, since you obviously use VersionOne (because it's the gold standard), there's an API for that:



