To use PSObject Add-Member or not…

I was playing around creating a PowerShell script with the intention of add items to a custom PSObject using the Add-Member.  Well, I couldn’t get it to work or maybe I was still doing something wrong.  But then, I came up with a (kind-of) unorthodox method that I will share with you.  Please, don’t tell anyone:

First, here’s a PowerShell script function “Get-SPInventory” I found in Technet; (  There’s nothing wrong with it and work as expected:

Function Get-SPInventory {
    $wmi = Get-WmiObject Win32_OperatingSystem –comp $_ | Select CSName,BuildNumber,ServicePackMajorVersion
    $obj = New-Object PSObject
    $obj | Add-Member NoteProperty BuildNumber ($wmi.BuildNumber)
    $obj | Add-Member NoteProperty CSName ($wmi.CSName)
    $obj | Add-Member NoteProperty SPVersion ($wmi.ServicePackMajorVersion)
    $wmi = Get-WmiObject Win32_BIOS –comp $_ | Select SerialNumber
    $obj | Add-Member NoteProperty BIOSSerial ($wmi.SerialNumber)
    Write-Output $obj

So, I going to duplicate the same functionality in a different way without creating a new PSObject and not using Add-Member.  I’m going to let PowerShell to create the PSObject for me and work a little with the Select-Object.  Here’s my version:

Function Get-SPInventory2 {
        $wmi1 = Get-WmiObject Win32_OperatingSystem –comp $_ |
Select CSName,BuildNumber,ServicePackMajorVersion
        $wmi2 = Get-WmiObject Win32_BIOS –comp $_ | select SerialNumber
        $results = $wmi1|select csname,BuildNumber,@{Label=”SPVersion”;Expression={$_.ServicePackMajorVersion -as [int]};},@{Label=”SerialNum”;Expression={$wmi2.SerialNumber};}
        Write-Output $results


Now, I got both functions generating the same results.  I’m my script, the “Label=” will create the noteproperty and the “Expression=” will store the value for the $wmi2.SerialNumber. 

Let say I want to use SQL Server Management Object (SMO) to collect various information from some SQL Servers on the network.  Here’s another sample script: (make sure your SMO assembly is loaded)

$SQLSvr = [Microsoft.SqlServer.Management.Smo.SmoApplication]::EnumAvailableSqlServers($false) | Select name [Array] $SQLinfo = ForEach($SQL in $SQLSvr){
    $MySQL = new-object(‘Microsoft.SqlServer.Management.Smo.Server’) $SQL.Name
    $MySQL | Select NetName, Product, EngineEdition, Version, Platform, ProductLevel
$SQLinfo | select Netname, Product, EngineEdition, Version, ProductLevel | ft -autoimage

In this script, I’m using the SMO SQL enumerator to collect the SQL Server names so we can use the “foreach” to collect the information from each server into the output array “$SQLinfo”.   After building the output variable then you can save the results in different output format such as: CSV, TXT, XML, or event back to a SQL table.

This is another way to collect information and let PowerShell do the rest for you!  PowerShell gives Power to the scripters.

Check out this other blog about creating objects:

Happy PowerShelling!!

Here’s my SWFLCC presentation “Working with PowerShell”

Please feel free to download my presentation given at the “Southwest Florida .NET Code Camp 2010” today Saturday, Sept. 25th in the morning. Please, after downloading the file, rename it to a *.zip file.

Click here to download presentation:< >

I’m sorry that one hour wasn’t enough, but it was nice that the group wanted to see more.  Let’s plan for another user group session in that area.  I will be glad to give this session again.

Once again, Thanks to the organizers for letting me speak and to all attendees for coming over the sit at the Code Camp sessions.

Happy PowerShelling!!

Looking back at SQL Server ‘DTUtil.exe’ and PowerShell

Thanks to a SQL Server college that send me an email about a problem he was having understanding how I was using DTUtil in PowerShell and it was giving him the following error message:

The term ‘Create-My-New-Table’ is not recognized as the name of a cmdlet, function, script file, or operable program. C
heck the spelling of the name, or if a path was included, verify that the path is correct and try again.
At line:1 char:117
+ dtutil /FILE “C:\temp\Store MySSIS Package\Create-My-New-Table.dtsx” /DestS MAX-PCWIN1 /copy SQL;Create-My-New-Table
    + CategoryInfo          : ObjectNotFound: (Create-My-New-Table:String) [], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

I was able to duplicate the error and after a couple of hours I realized that I failed to mention one critical piece of information.  In my previous blog series about running the DTutil.exe tool in PowerShell, I forgot to mention that you can’t run DTutil.exe in the PowerShell console.  This is one of a few *exe commands that was meant to run under DOS Shell.

That’s the reason why I came up with a PowerShell script that will call/execute a batch file with passing parameters in order to use the DTUtil.exe tool.

Now, to help clear some of the issues I found when trying to run this command with parameters:

1. As I verfiy, DTutil.exe will need to run in a batch file (*.bat) in order to be able to run in PowerShell.   Here’s the sample error message if you try to run DTUtil dos command under the PowerShell Console:


2. Don’t use IP address. In my test it didn’t work and in the Microsoft documentation it is asking for “servername”.  So, use the SQL “Servername” or “Servername\Instance”.   My test fail when using my local machine IP address:


3. Then, in the “.. /Copy SQL; ” . Don’t leave a space between the “ SQL; ” and the DTS Package name.

4. And, the DTS Package name doesn’t need the extension “*.dtsx”

Here’s example of a valid DTutil command I use to copy my sample DTS Package file to my local SQL Server:

dtutil /FILE “C:\temp\Store MySSIS Package\Create-My-New-Table.dtsx” /DestS MAX-PCWIN1 /copy SQL;Create-My-New-Table

I hope this will help understanding this issues of running this SQL DOS command in PowerShell and take a look at my older 3 part series about “Deploying SSIS Packages” in PowerShell:

Deploying SSIS Packages using DTUtil.exe with PowerShell – Part 1:!A034D6A0DDC4E64E!1075.entry

Deploying SSIS Packages using DTUtil.exe with PowerShell – Part 2:!A034D6A0DDC4E64E!1102.entry

Deploying SSIS Packages using DTUtil.exe with PowerShell – Part 3:!A034D6A0DDC4E64E!1133.entry

I greatly appreciate the emails and I’m glad to be able help in the IT community.

Keep PowerShelling!!

Start using the Help cmdlet in Windows PowerShell

You want to get started with Windows PowerShell?  The answer is at your fingertips.  The truth is, beside all the good books, you got everything you need already included with Windows PowerShell.  Have you use the “Help” cmdlet in PowerShell?  If not, then you are missing some of the most extensive documentation PowerShell have made available since its release.  There’s a LOT of information that can help you get started in understanding and writing PowerShell scripts.

Just type “Help About_*” and press enter:


As you can see, all this information is available to you.  This will help you understand how to write functions, how to use operators, Aliases, Parameters, Hash Tables, and more.  Don’t be intimidated by all this information.  I recommend you to use “Help” and then you can search on the internet or reading a book for more in depth information.

By the way, this information can be found online under Microsoft TechNet.  Here an example link looking at the Help on “About_Operators”:


So, how many About_* topics can be found under PowerShell V2?  To find out type the following command:

(help about_*).count    (press enter)


You will find there’s a total of 95 topics.  This is only the “About_*” topics.  So, as you can see,  this information is available at your fingertips.  So, use it and start learning Windows PowerShell.  

Go ahead and try it!!!

Surprise!! SQL Server 2008 R2 has new PowerShell Cmdlets…

Yes!!  I just found out the new version of SQL Server 2008 R2 has new PowerShell Snapin called “MasterDataServicesSnapin” and it will give 7 new cmdlets:

New-MasterDataServicesDatabase – Creates a Master Data Services database.

Get-MasterDataServicesDatabase – Gets information about a Master Data Services database.

Get-MasterDataServicesDatabases – Gets information about Master Data Services databases on a specified instance of SQL Server.

Get-MasterDataServicesDatabaseServerInformation – Gets connection information for a specified instance of SQL Server.

Get-MasterDataServicesIISInformation – Gets information about Internet Information Services (IIS) on the local computer.

Set-MasterDataServicesSystemSetting – Sets the value of a specified system setting in a Master Data Services database.

Get-MasterDataServicesSystemSettings – Gets system settings from a specified Master Data Services database.

Remember! To make these cmdlets available just use the cmdlet to add the pssnapins:

Add-PSSnapIn MasterDataServicesSnapIn

Additional information can be found: