SSIS Performance Counters missing

Recently, I overcame an instance where my SSIS Performance Counters were missing out of PerfMon on my development machine. This made it incredibly hard to keep track of what was running and it's system-level impact once I kicked it off in SSMS. Sure, I can use the reports to see the messages and the overview report, yes, but what I really want is to know whether or not it's paging to disk!

Specifically, I want these
http://msdn.microsoft.com/en-us/library/ms137622(v=sql.110)

Buffers spooled
Buffer memory

So, I tried

Restarting Perfmon
Hey, sometimes the simplest solution is still the answer! It wasn't this time, though.

Reinstalling the counters
This involved a call to the command line. When I remember the syntax (or find it in my notes...) I'll update it here.

Setting the Performance Logs and Alerts service to start automatically
This didn't do anything, either, but it did set me up for...

Set the Performance Logs and Alerts service to use "Local Service"
It was in the process of re-enabling the perfmon service to use this account that I received a message to the effect of "Enabling this account to log on as a service". While I am still not sure which patch or update killed that ability, the SSIS counters now show up, and work as intended.

A quick way to load the baseball DB

I found the quickest way to load and reload the baseball database I've converted was to do two things:

1. Cut the db into individual files
2. Load those files one by one along with some logging

This is all accomplished through good, old-fashioned and very fast command line calls to SQLCMD .

There's a couple of great things about this script.

1. It runs fast, even in a VM.
2. It uses integrated security - no need to jump through hoops to make it work.
3. The output is directed to a very specific file, which is rebuilt with every command.

The downsides of this approach aren't really "cons" as in "pros vs. cons", but more like limitations. For example:

1. The file output of SQLCMD is overwritten every time. In command line mode, there is no SQLCMD flag to indicate I'd like the file "appended to" instead.
2. The output file can be appended through concatenation, though, which, while a separate command line call, is still pretty simple.

Here's the script. I've saved this in my VM as a file on the desktop named "Make Lahman.cmd"

cls
sqlcmd -S localhost -d lahman -b -I -i lahman2013_tables.sql -o tempout.txt
type tempout.txt > finalout.txt
sqlcmd -S localhost -d lahman -b -I -i "lahman master.sql"  -o tempout.txt
type tempout.txt >> finalout.txt
sqlcmd -S localhost -d lahman -b -I -i "lahman Fielding.sql"  -o tempout.txt
type tempout.txt >> finalout.txt
sqlcmd -S localhost -d lahman -b -I -i "lahman Batting.sql"  -o tempout.txt
type tempout.txt >> finalout.txt
sqlcmd -S localhost -d lahman -b -I -i "Pitching.sql"  -o tempout.txt
type tempout.txt >> finalout.txt
sqlcmd -S localhost -d lahman -b -I -i "Teams.sql"  -o tempout.txt
type tempout.txt >> finalout.txt
sqlcmd -S localhost -d lahman -b -I -i "PitchingPost.sql"  -o tempout.txt
type tempout.txt >> finalout.txt
sqlcmd -S localhost -d lahman -b -I -i "lahman ManagersHalf.sql"  -o tempout.txt
type tempout.txt >> finalout.txt
sqlcmd -S localhost -d lahman -b -I -i "lahman AllstarFull.sql"  -o tempout.txt
type tempout.txt >> finalout.txt
sqlcmd -S localhost -d lahman -b -I -i "TeamsHalf.sql"  -o tempout.txt
type tempout.txt >> finalout.txt
sqlcmd -S localhost -d lahman -b -I -i "TeamsFranchises.sql"  -o tempout.txt
type tempout.txt >>finalout.txt
 sqlcmd -S localhost -d lahman -b -I -i "Salaries.sql"  -o tempout.txt
type tempout.txt >> finalout.txt
 sqlcmd -S localhost -d lahman -b -I -i "Schools.sql"  -o tempout.txt
type tempout.txt >> finalout.txt
 sqlcmd -S localhost -d lahman -b -I -i "SchoolsPlayers.sql"  -o tempout.txt
type tempout.txt >> finalout.txt
 sqlcmd -S localhost -d lahman -b -I -i "SeriesPost.sql"  -o tempout.txt
type tempout.txt >> finalout.txt
 sqlcmd -S localhost -d lahman -b -I -i "lahman Managers.sql"  -o tempout.txt
type tempout.txt >> finalout.txt
 sqlcmd -S localhost -d lahman -b -I -i "lahman HallOfFame.sql"  -o tempout.txt
type tempout.txt >> finalout.txt
 sqlcmd -S localhost -d lahman -b -I -i "llahman FieldingPost.sql"  -o tempout.txt
type tempout.txt >> finalout.txt
 sqlcmd -S localhost -d lahman -b -I -i "lahman BattingPost.sql"  -o tempout.txt
type tempout.txt >> finalout.txt
 sqlcmd -S localhost -d lahman -b -I -i "lahman AwardsSharePlayers.sql"  -o tempout.txt
type tempout.txt >> finalout.txt
 sqlcmd -S localhost -d lahman -b -I -i "lahman AwardsShareManagers.sql"
type tempout.txt >> finalout.txt
 sqlcmd -S localhost -d lahman -b -I -i "lahman AwardsPlayers.sql"
type tempout.txt >> finalout.txt
 sqlcmd -S localhost -d lahman -b -I -i "lahman AwardsManagers.sql"
type tempout.txt >> finalout.txt
 sqlcmd -S localhost -d lahman -b -I -i "lahman Appearances.sql"
type tempout.txt >> finalout.txt

Not a sports fan, but....

I am NOT a sports fan.

I love the World Cup, and all that it brings out that's in the best of people and countries.

That said, other people ARE sports fans. Some of them have made really successful careers out of writing about it.

One of them is an outrageously good sports writer named Sean Lahman, who publishes a baseball statistics database. I've converted it to SQL 2012, and would *really* suggest you do the same.

For one, it's a good exercise in ETL / flat file loading if you don't do that sort of thing on a daily basis. For another, it'll give you all sorts of reasons to hate copy & paste enough to get into ETL - the data itself it pretty big, given that Mr. Lahman has collected stats since the beginning of baseball.

On Success

If you're used to failing, success is a surprise.

If you're used to succeeding, failure isn't.

Exception calling “LoadPackage” in SQLPSX - SSIS 2012

Here's a copy of a Stack Overflow / DBA Overflow request I recently made:

I'm in a 2012-only world, where the only server and client software I have loaded on my development machine is SQL 2012. I cannot load other SQL Server versioned tools, and I am stuck (!) on PowerShell 2.0, as any scripts created have to be eventually deployable on servers which are very likely to only have PowerShell 2.0.

I am using a script inspired by examples I found online. Here's the code:

Import-Module SSIS clear; $ErrorActionPreference = "Stop" $path = "C:\Users\<<Username>>\Documents\Visual Studio 2012\Projects\ConfigurationsTest\ConfigurationsTest"; $packageFileObjects = Get-ChildItem -path $path -Filter "*.dtsx"; foreach($packageFileObject in $packageFileObjects) { $packageObject = Get-ISPackage -path $packageFileObject.FullName; foreach($variable in $packageObject.Variables) { Write-Output "$($packageObject.Name); } }

When using SSIS 2012 and SQLPSX, I get the following error.

Exception calling "LoadPackage" with "2" argument(s): "The package failed to load due to error 0xC0011008 "Error loa ding from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.". This occurs when CPackage::LoadFromXML fails. " At C:\Users\<<Username>>\Documents\WindowsPowerShell\Modules\SSIS\SSIS.psm1:727 char:19 + $app.LoadPackage <<<< ($path, $null) | add-Member -memberType noteProperty -name DisplayName -value $name -passthru } + CategoryInfo : NotSpecified: (:) [], ParentContainsErrorRecordException + FullyQualifiedErrorId : DotNetMethodException

I've verified that my $env:PSModulePath contains the folders where SQLPSX is installed. I've copied the SSIS.psm1 to a different location , also found in $env:PSModulePath , to see if the service running couldn't access my user directory. I've adjusted SQLServer.psm1 with references to 2012 versions of the files. (As found here:)

 Also, I've modified the header section of ssis.psm1 to reflect the version 11.0 assembly, like this:

if ( $Args[0] -eq 2005 ) { add-type -AssemblyName "Microsoft.SqlServer.ManagedDTS, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" #add-type -Path "C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SQLServer.ManagedDTS.dll" } else { add-type -AssemblyName "Microsoft.SqlServer.ManagedDTS, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" #add-type -Path "C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SQLServer.ManagedDTS.dll" }

I've read quite a bit online that talks about how great sqlpsx is, so I'm really looking forward to getting past this bug! I cannot figure out why this fails. All the syntax looks right, so I'm hoping maybe somebody else has conquered this error already, and can share their solution or workaround.

Descending into it, one table at a time

I was hunting for a script. I needed something that could

• crawl the metadata for a given DB
• get all the tables in said DB
• figure out the metadata-based relationships (via the foreign keys)
• and then walk the chain of relationships, but do it backwards so I could disable the keys in proper order without conflict

And so I found this:

http://sqlblog.com/blogs/jamie_thomson/archive/2009/09/08/deriving-a-list-of-tables-in-dependency-order.aspx

Really love this!

Another stuffing, and it's not even a holiday

Another version of the "get stuffed" script

http://raresql.com/tag/merge-or-combine-multiple-rows-records-to-single-column-record-with-comma-delimiters/

coalesce is NOT the answer

"These are not the droids you're looking for" ? ? ?

http://msmvps.com/blogs/robfarley/archive/2007/04/08/coalesce-is-not-the-answer-to-string-concatentation-in-t-sql.aspx

Short version? Use STUFF() instead.

safebrowsing.lavasoft.com

I had all sorts of problems with slowness in Chrome. Sites wouldn't load, things were going badly during high-bandwidth operations, and all network-related activities were all but grinding to a halt. It was awful. Funny enough, my PC was slow enough during all this that I was able to watch the "sites loading" messages in the bottom left corner of the browser.

That led me to hunt down information on safebrowsing.lavasoft.com

I found the following information online about safebrowsing.lavasoft.com. I'm really rather frustrated at having to uninstall a sneaky redirector. Not cool.

Here's the quote from the post:

I think it was called something like
Ad-Aware Safe Browsing
or so. 

https://productforums.google.com/forum/#!msg/chrome/HrIuwR2DW_Q/U6kBbWr4HRgJ

Born To Win

Just read this:

Born To Win

...it's one of those books that I found myself asking "Why didn't I do this sooner?"

Also, they have an online companion to the source:

Don't know much about the civil war

Just read this:

Don't Know Much About the Civil War: Everything You Need to Know About America's Greatest Conflict but Never Learned

...amazing book with tons of information. Was delivered flawlessly via audiobook.

Trust works!

Just read this:

Trust Works!: Four Keys to Building Lasting Relationships by Ken Blanchard

...liked it. Seemed complicated at first, but it's actually pretty simple.

8 Ways to great

Just read this:

8 Ways To Great by Doug Hirschhorn

...and liked it. Really useful stuff.