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!

Comments

• Anonymous
  January 01, 2003
  Opalis Integration Server and System Center Orchestrator are management applications (workflow, automation, orchestration) where any code or scripts created and leveraged are stored and executed from within the applications themselves. There should be no risk for SQL injection. Either way, these are simply very basic examples created as a reference.

• Anonymous
  January 01, 2003
  Unfortunately, the videos are only available straming from YouTube.

• Anonymous
  January 01, 2003
  Hi. Can I download the Video from a link here? - It's not set up to do so this end.. The sound Quality is great! But I really don't know Opalis - was also looking for PowerShell. cheers tho!

• Anonymous
  July 08, 2011
  I know nothing about Opalis - I just looked at this this post because it mentioned Powershell in the title :) Regarding the SQL, it doesn't look like it's parameterised, so if anyone expands on the code then it might introduce a potential SQL injection risk. Unless I'm wrong, in which case my apologies, carry on, sorry for the interruption.