8 Minute Demo – PowerShell to SQL - Runbook Options

Good Evening Readers/Viewers!

I figured it was time to create another “8-Minute-Demo” for my favorite video series… So I have come back with something that I have recently been implementing for any Runbook implementing PowerShell data extraction for SQL data storage. It is a very simple concept, that up until lately I have been avoiding in favor of the “Query Database” object. Once you switch methods, I believe you will be as impressed with the performance gain as I have been.

Also in this video, I use the new System Center 2012 Orchestrator BETA for the demonstrations. You will get a chance to see (if you haven’t already given it a test drive) the new look and feel, in action! (You will also see me stumble through the new verbiage – It is a process. I mean really, I have been saying “object” instead of “activity” for a very long time now. :) )

Enough talk, roll tape!

Now, for the promised PowerShell from the video:

Single-Value Data PowerShell to SQL Example

#Create Connection to SQL Server and DB $connString = "Data Source=.;Initial Catalog=Demo;Integrated Security=SSPI" $connection = New-Object System.Data.SqlClient.SqlConnection($connString) $connection.Open() $sqlcmd = $connection.CreateCommand()

#Get Information $stringData = "Hello World!" $intData = 42 $dateData = get-date

#Create SQL INSERT Statement $CommandText = "INSERT [PowerShelltoSQLDemo] ([String_Data],[Int_Data],[Date_Data]) VALUES ('$stringData',$intData,'$dateData')"

#Execute SQL INSERT Statement $sqlcmd.CommandText = $CommandText $InsertCount += $sqlcmd.ExecuteNonQuery() $connection.Close()

$InsertCount

Multi-Value Data PowerShell to SQL Example

#Create Connection to SQL Server and DB $connString = "Data Source=.;Initial Catalog=Demo;Integrated Security=SSPI" $connection = New-Object System.Data.SqlClient.SqlConnection($connString) $connection.Open() $sqlcmd = $connection.CreateCommand()

#Get Information $processData = get-process | Select-Object -ExpandProperty Name

foreach ($d in $processData) { #Create SQL INSERT Statement $CommandText = "INSERT [PowerShelltoSQLDemo] ([String_Data],[Int_Data],[Date_Data]) VALUES ('$d.Name',0,CURRENT_TIMESTAMP)"

    #Execute SQL INSERT Statement $sqlcmd.CommandText = $CommandText $InsertCount += $sqlcmd.ExecuteNonQuery() }

$connection.Close()

$InsertCount

DISCLAIMER: The project artifacts within the above are currently compatible and tested against System Center 2012 Orchestrator BETA installed on Windows 2008 R2. Older versions of OIS and/or the Windows OS where OIS is installed may require that these artifacts need to be modified to accommodate. This solution is AS IS, is not specifically supported and you should use it at your own risk. Please be aware of the impact each policy has on the intended targets before testing. ALWAYS use a test environment to vet your OIS/SCO projects.

enJOY!