Tuesday, August 26, 2014

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.

Tuesday, August 19, 2014

On Success

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

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

Tuesday, August 12, 2014

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=, 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=, 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.

Tuesday, August 5, 2014

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:


Really love this!