Set variables in SSIS package at runtime

Posted by timvw on May 19, 2011 No comment

The documentation on dtexec Utility (SSIS Tool) says the following:

/Set propertyPath;value

(Optional). Overrides the configuration of a variable, property, container, log provider, Foreach enumerator, or connection within a package. When this option is used, /Set changes the propertyPath argument to the value specified. Multiple /Set options can be specified.

At first sight this works like a charm but as soon as your value has a space dtexec seems to get confused :( It took me a couple of websearches to find a post that suggests the following:

dtexec /SET \Package.Variables[User::TheVariable].Properties[Value];\”"; space”\”

It seems that this works like a charm :) Because i’m lazy i wrapped this in a powershell function:

function PackageOption()
{
	param($name, $value);
	"$name;\`"`"$value\`"`"";
}

And now you can use it as following in your deployment script:

$TheVariableOption = PackageOption -Name "\Package.Variables[User::TheVariable].Properties[Value]" -Value "some thing";
&dtexec /File "$package" /Set $TheVariableOption;

How to handle (short) bursts of work

Posted by timvw on May 9, 2011 No comment

Here is a summary of how businesses around me handle short bursts of work:

  • When i go to the doctor:
    • find an empty seat in the waiting room
    • observe who’s before me
    • wait untill all those people have been seen by the doctor


  • When i go to the supermarket:
    • join the queue at the end
    • wait untill al before me have been helped


  • When i go the the bakery:
    • get a number from a ticketing machine at the entrance
    • wait untill a seller indicates he’s willing to process my requests


  • When i go to my car dealer:
    • hand over car keys
    • give my mobile phone number
    • relax in the loung untill they come find me (or send me an sms)


Invoke parameterized stored procedure with PowerShell

Posted by timvw on March 21, 2011 No comment

Here is a quick snippet that demonstrates how you can invoke a parametrized stored procedure with PowerShell:

$CreateTraceCommand = $SqlConnection.CreateCommand();
$CreateTraceCommand.CommandType = [System.Data.CommandType] "StoredProcedure";
$CreateTraceCommand.CommandText = "sp_trace_create";
$TraceIdParameter = $CreateTraceCommand.Parameters.Add("@traceid", [System.Data.SqlDbType] "Int");
$TraceIdParameter.Direction = [System.Data.ParameterDirection] "Output";
$OptionParameter = $CreateTraceCommand.Parameters.Add("@options", [System.Data.SqlDbType] "Int");
$OptionParameter.Value = [int] 2;
$TraceFileParameter = $CreateTraceCommand.Parameters.Add("@tracefile", [System.Data.SqlDbType] "NVarChar");
$TraceFileParameter.Value = [string] "c:\temp\test";
[void] $CreateTraceCommand.ExecuteNonQuery();
$TraceId = $TraceIdParameter.Value;
Write-Host "You created a trace with id: $TraceId";

Global hotkeys for iTunes on windows

Posted by timvw on March 16, 2011 1 comment

These days i use iTunes as media player. Unlike winamp this program does not seem to support system-wide hotkeys to control playback. This is the place where a productivity tool like AutoHotkey comes into the rescue ;) .

I have configured the following keys:

  • Move to next song: [Ctrl] + [Alt] + [Right]
  • Move to previous song: [Ctrl] + [Alt] + [Left]
  • Toggle play/pause: [Ctrl] + [Alt] + p
  • Turn volume up: [Ctrl] + [Alt] + [Up]
  • Turn volume down: [Ctrl] + [Alt] + [Down]
^!right::
DetectHiddenWindows , On
ControlSend , ahk_parent, ^{right}, iTunes ahk_class iTunes
DetectHiddenWindows , Off
return

^!left::
DetectHiddenWindows , On
ControlSend , ahk_parent, ^{left}, iTunes ahk_class iTunes
DetectHiddenWindows , Off
return

^!p::
DetectHiddenWindows , On
ControlSend , ahk_parent, {space}, iTunes ahk_class iTunes
DetectHiddenWindows , Off
return

^!up::
DetectHiddenWindows , On
ControlSend, ahk_parent, ^{UP}, iTunes ahk_class iTunes
DetectHiddenWindows , Off
return

^!down::
DetectHiddenWindows , On
ControlSend, ahk_parent, ^{DOWN}, iTunes ahk_class iTunes
DetectHiddenWindows , Off
return

Navigation to the webpage was canceled

Posted by timvw on March 16, 2011 1 comment

Earlier today i ran into the ‘Navigation to the webpage was canceled’ when i tried to open a CHM file:

Navigating to the file properties allows me to unblock the file:

And now i can happily use the CHM file:

Consume locally build software without overloading your $PATH

Posted by timvw on March 12, 2011 No comment

How do you consume locally build software? For a while now i have used the following approach:

  • Create a ~/bin folder
  • Add that ~/bin folder to my $PATH
  • Add symlinks from binary to ~/bin (ln -s ~/src/git-tfs/GitTfs.Vs2010/bin/debug/git-tfs.exe git-tfs)

Please let me know about your strategy..

Some PowerShell functions to work with SSIS packages

Posted by timvw on March 12, 2011 1 comment

Here are some powershell functions (using XPath) that come in handy when working with SSIS packages:

function FindConnectionManagerNames {
	param($fileName)
	$xml = 1 (Get-Content $fileName);
	$ns = New-Object Xml.XmlNamespaceManager $xml.NameTable;
	$ns.AddNamespace("DTS", "www.microsoft.com/SqlServer/Dts");
	$xml.SelectNodes("//DTS:ConnectionManager/DTS:Property[@DTS:Name='ObjectName']", $ns) | Foreach { $_."#text"; }
}

function GetConnectionManagerConnectionString {
	param($fileName, $connectionManagerName)
	$xml = 1 (Get-Content $fileName);
	$ns = New-Object Xml.XmlNamespaceManager $xml.NameTable;
	$ns.AddNamespace("DTS", "www.microsoft.com/SqlServer/Dts");
	$xml.SelectSingleNode("//DTS:ConnectionManager[DTS:Property='$connectionManagerName']/DTS:ObjectData/DTS:ConnectionManager/DTS:Property[@DTS:Name='ConnectionString']", $ns)."#text";
}

function FindVariables {
	param($fileName)
	$xml = 1 (Get-Content $fileName);
	$ns = New-Object Xml.XmlNamespaceManager $xml.NameTable;
	$ns.AddNamespace("DTS", "www.microsoft.com/SqlServer/Dts");
	$xml.SelectNodes("//DTS:Variable/DTS:Property[@DTS:Name='ObjectName']", $ns) | Foreach { $_."#text"; }
}

function GetVariable {
	param($fileName, $variableName)
	$xml = 1 (Get-Content $fileName);
	$ns = New-Object Xml.XmlNamespaceManager $xml.NameTable;
	$ns.AddNamespace("DTS", "www.microsoft.com/SqlServer/Dts");
	$xml.SelectSingleNode("//DTS:Variable[DTS:Property='$variableName']/DTS:VariableValue", $ns)."#text";
}

Dynamic scriptblock with PowerShell

Posted by timvw on March 12, 2011 No comment

Earlier this week i tried to run a command on a remote computer but it did not seem to work:

$name = 'tim';
$computer = 'localhost';
Invoke-Command -ComputerName $computer -ScriptBlock { Write-Host "Hello $name" }

Because powershell serialises the { Write-Host “Hello $name” } as a string this ends up at the remote computer as { Write-Host “Hello $null” }. In order to send our ‘dynamic’ command string over the wire we have to make sure it is serialised correctly:

$scriptBlock = $executioncontext.InvokeCommand.NewScriptBlock("Write-Host `"Hello $name`"");
Invoke-Command -ComputerName $computer -ScriptBlock $scriptBlock;

Some tips for .Net developers using git on cygwin

Posted by timvw on March 12, 2011 1 comment

Here are some tips that i want to share with fellow .Net developers that use git on cygwin.

First of all i defined some aliases in my ~/.bashrc:

# open explorer in the current working directory
alias explorer='explorer.exe "`cygpath -aw \"$PWD\"`"'

# invoke MSBuild
alias msbuild='/cygdrive/c/Windows/Microsoft.NET/Framework/v4.0.30319/MSBuild.exe'

Because i don’t like the TFS source control story i use git-tfs. As a .Net developer you want to add the following to your .git/info/exclude file:

#OS junk files
[Tt]humbs.db
*.DS_Store

#Visual Studio files
*.[Oo]bj
*.exe
*.pdb
*.user
*.aps
*.pch
*.vspscc
*.vssscc
*_i.c
*_p.c
*.ncb
*.suo
*.tlb
*.tlh
*.bak
*.[Cc]ache
*.ilk
*.log
*.lib
*.sbr
*.sdf
ipch/
obj/
[Bb]in
[Dd]ebug*/
[Rr]elease*/
Ankh.NoLoad

#Tooling
_ReSharper*/
*.resharper
[Tt]est[Rr]esult*

#Subversion files
.svn

Whenever i work online i usually run these two commands consecutively: git -a -m “commit message” and git-tfs checkin -m “commit message”. Here is a small ~/bin/commit script that combines these:

#!/bin/bash
git commit -a -m "$1";
git-tfs checkin -m "$1";

Using WinMerge as difftool on cygwin/git

Posted by timvw on March 12, 2011 No comment

Last couple of weeks i have been using Git on cygwin and i got very satisfying results out of it. One thing that i wanted to tweak was the ability to use WinMerge to compare files. Here is how i do it:

Here is my ~/.gitconfig:

[user]
	name = Tim Van Wassenhove
	email = git@timvw.be

1
	external = "~/bin/git-diff-wrapper.sh"

Here is my ~/bin/git-diff-wrapper.sh (it uses cygpath to translate the paths):

#!/bin/sh
"/cygdrive/c/Program Files/WinMerge/WinMergeU.EXE" /e /ub /dl other /dr local "`cygpath -aw $1`" "`cygpath -aw $2`" "`cygpath -aw $5`"

And now we’re good to go ;)