The SSIS and T-SQL Hammer

I´m Sorry by my english, hope you guys can understand 🙂

I believe that you have read the Blog Post from Chad Miller called The T-SQL Hammer. It is a great example of productivity in Powershell.
I took the liberty to use the same name in this Blog Post , because reading his words:

“The over-reliance on a familiar tool is best described with the quote, “if all you have is hammer, everything looks like nail” and for database professionals this means using or sometimes misusing T-SQL. Whenever database administrators are presented with a scripting problem they instinctively reach for good-old-familiar T-SQL.  And why not? In many cases T-SQL provides an ideal solution to SQL Server administration scripting problems, however there are certain scenarios where another tool, Powershell provides a  more elegant solution.”

I belive we can show another example in “why  use powershell ?”.

Surely this is a great article that Rodney Landrum shows , a very interesting solution to consolidate SQL Server Error Logs and you can read in here: Consolidating SQL Server Error Logs from Multiple Instances Using SSIS

In this article he shows how to use SSIS and some T-SQL to do this operation. One of the things changed in my version is that I would not use the “merge statment” .

I have one Server and Database repository, one table repostory to save the logs and I created another control table called ServersInfo  with only two columns, ServerName and  last collection Date.

So when I access the error log, I filter by date always above or equal  the latest date recorded in this table.

But lets see the code :

First T-SQL to create the repository table and Server/date control table. Obviously you will create these tables on your server and database repository.

   1: Create Table SQLLogInfo (    ServerName varchar(50),
   2:                             LogDate datetime,
   3:                             ProcessInfo varchar(100),
   4:                             Text varchar(max)
   5:                          )
   7: Create Table ServersInfo (    ServerName varchar(100), 
   8:                             DateLastLogErrorImported datetime
   9:                          )

Now let’s populate the Servers table 9Serversinfo) with all servers to have the error logs collected.

   1: insert into ServersInfo(ServerName) values ('Colombo-pc\SQLExpress')
   2: insert into ServersInfo(ServerName) values ('Colombo-pc')

After that, the Powerrshell code : remember I am using SQLPSX

   1: #Define Server and Database Repository
   2: $ServerRepository = $env:COMPUTERNAME
   3: $DatabaseRepository = "tempdb"
   5: #Return the servers and the last collection date from error logs
   6: get-SqlData -sqlserver $ServerRepository -dbname $DatabaseRepository -qry "Select ServerName,DateLastLogErrorImported from ServersInfo" | foreach {
   8:     $ServerName = $_.Servername
   9:     $DateLastLogErrorImported = $_.DateLastLogErrorImported
  11:     get-sqlserver $ServerName | foreach {    
  13:         #If this is the first collection, takes the date 2010/01/01
  14:         if ($DateLastLogErrorImported -eq $null -or $DateLastLogErrorImported -eq "")
  15:             { $DateLastLogErrorImported = '2010/01/01' }
  17:         #retrive the error log from the cuurent server in foreach. Aplly fliter to only LogDate above and equal to Last Collection date
  18:         #And insert into Repository
  19:         $Error.Clear()
  20:         Get-SqlErrorLog -sqlserver $ServerName -lognumber 0 | where-object { $_.LogDate -ge $DateLastLogErrorImported} | foreach {
  22:                 $Text = $($_.text) -replace "'"
  23:                 Set-SqlData -sqlserver $ServerRepository -dbname $DatabaseRepository -qry "Insert into SQLLogInfo (Servername,LogDate,ProcessInfo,text) values ('$($ServerName)','$($_.Logdate)','$($_.ProcessInfo)','$Text)')"
  24:         }
  26:             #Update Information Table with the server and the last collection date.
  27:         Set-SqlData -sqlserver $ServerRepository -dbname $DatabaseRepository -qry "Update ServersInfo set DateLastLogErrorImported = getdate() where Servername = '$($Servername)'" 
  29:     }
  30: }

I’m not handling errors, but it is very simple to implement. Just a try catch  before  the SQLPSX functions e we close all possibilities of error logging them into a file. You can find this code in my Simple-Talk Articles.

With that , the data is collected and save into SQL Server table.

We can schedule to run this script once a day and OK, we will not have much information to bring the servers and I believe it is a good period for collecting the data.

You can apply summations, aggregates, filters and whatever is necessary to set up your monitoring 🙂

See you later 🙂


“Once there was this kid who
Got into an accident and couldn’t come to school
But when he finally came back
His hair had turned from black into bright white
He said that it was from when
The cars had smashed soooo hard

Mmmm Mmmm Mmmm Mmmm, Mmmm Mmmm Mmmm Mmmm
Once there was this girl who
Wouldn’t go to change with the girls in the change room
But when they finally made her
They saw birthmarks all over her body
She couldn’t quite explain it
They’d always just been there”

Crash Test Dummies
m Mmmm Mmmm Mmmm, Mmmm Mmmm Mmmm Mmmm

About Laerte Junior

Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
This entry was posted in Algo que Esqueci de Categorizar. Bookmark the permalink.

Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s