FIM 2010 Portal: PowerShell and writing to a database

I have a workflow that will do certain things. The workflow is triggered when changes are made to a user account in the FIM Portal. The workflow runs a PowerShell script which does a bunch of things and will write to a SQL table at the end of the workflow activity. I know the question would be why not use FIM Reporting? Don’t let me go there or maybe I like to customize. Anyway I thought it would be really simple to find a sample PowerShell script to insert records to a DB, not quite, so let me post mine. The first column is where I capture the time the record is created in the DB as well as when the workflow runs.

#Write to SQL Table
$SQLServer = ‘TLKSQLServer’
$SQLDBName = ‘TLKData’
$SqlQuery = “Insert into TLKInfo values (Getdate(),'”+$TAccount+”‘,'”+$DispName+”‘,'”+
$SAM+”‘,'”+$C+”‘,'”+$Cdate+”‘,'”+$CDisplayName+”‘,'”+$CAccountName
+”‘,’Account Flipped’)”
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = “Server = $SQLServer; Database = $SQLDBName; Integrated Security = True”
$SqlConnection.Open()
$SqlCmd = $SqlConnection.CreateCommand()
$SqlCmd.CommandText = $SqlQuery
$Sqlcmd.ExecuteNonQuery()
$SqlConnection.Close()

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s