Tag Archives: PowerShell

Multiclean solution

One of my favorite powershell commands when cleaning up:

$RootFolder = 'C:\tfs'
Get-ChildItem $RootFolder bin -Recurse | Remove-Item -Recurse
Get-ChildItem $RootFolder obj -Recurse | Remove-Item -Recurse

Force the removal of a file with PowerShell

Last couple of weeks I have been generating a lot of files (and restricting their ACLs) and today I decided to remove all those files. The problem is that my user account did not have permissions on those files. Here is a small script that will first take ownership of the file, then grants FullControl permissions, and finally removes the file :)

function RemoveFile
{
	param($FileName)
	
	&takeown /F $FileName
	
	$User = [System.Security.Principal.WindowsIdentity]::GetCurrent().User
	
	$Acl = Get-Acl $FileName
	$Acl.SetOwner($User)
	$AccessRule = New-Object System.Security.AccessControl.FileSystemAccessRule($User, "FullControl", "Allow")
	$Acl.SetAccessRule($AccessRule)
	Set-Acl $FileName $Acl

	Remove-Item $FileName
}

Get-ChildItem *.txt -R | % { RemoveFile $_.FullName; }

Edit on 2011-10-19

Resetting the permissions with icacls c:\output /reset /t and then calling Remove-Item c:\output -R does the trick.

function RemoveFiles
{
 param($Directory)
 icacls $Directory /reset /t
 Remove-Item $Directory -R
}

RemoveFiles c:\output;

Launch DtExec from PowerShell

Running an SSIS package from PowerShell (using DTExec) can be as simple as:

RunPackage -File 'C:\test.dtsx' -DatabaseHost '.' -DatabaseName 'TEST';

Here are the functions that make it this simple:

function GetDtExecPath {
    $DtsPath = (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\100\DTS\Setup').SQLPath;
    $DtExecPath = (Resolve-Path "$DtsPath\Binn\DTExec.exe");
    $DtExecPath;
}

function GetDtExecPropertyPathValue() {
    param(
        $PropertyPath = '',
        $Value = ''
    );
    "$PropertyPath;\`"`"$Value\`"`"";
}

function RunPackage {
    param(
        $DtExecPath = (GetDtExecPath),
        $File = 'test.dtsx'
    );
    
    $Params = "/FILE $File";
    for($i = 0; $i -lt $Args.Length; $i += 2) {
        $PropertyPath = $Args[$i].SubString(1);
        $Value = $Args[$i+1];
        $PropertyPathValue = GetDtExecPropertyPathValue -PropertyPath $PropertyPath -Value $Value;
        $Params += " /SET $PropertyPathValue";
    } 
    
    &"$DtExecPath" $Params;
}

Set variables in SSIS package at runtime

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;

Invoke parameterized stored procedure with PowerShell

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";

Some PowerShell functions to work with SSIS packages

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

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;

Consume custom SSIS tasks without GAC installation

For a while i thought that in order to consume a custom SSIS task you had to install the assembly in the GAC. Now i know better ;)

For the designer (BIDS) you have to copy the files to:

C:\Program Files\Microsoft SQL Server\100\DTS\Tasks
C:\Program Files\Microsoft SQL Server\100\DTS\PipelineComponents

For the runtime (BIDS) you have to copy the files to:

C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies

And for dtexec you have to copy the files to:

C:\Program Files\Microsoft SQL Server\100\DTS\Binn

Here is a small powershell script that gives you the paths where you want to copy your assembly to:

# Lookup Tasks, PipelineComponents and DtExec paths
$DtsPath = (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\100\DTS\Setup').SQLPath;
$DtsTasksPath = (Resolve-Path "$DtsPath\Tasks");
$DtsPipelineComponentsPath = (Resolve-Path "$DtsPath\Pipelinecomponents");
$DtExecPath = (Resolve-Path "$DtsPath\Binn");

# Lookup VS2008/Bids path
$Vs2008Path = (Get-ItemProperty 'HKLM:\Software\Microsoft\VisualStudio\9.0').InstallDir;
$Vs2008PrivateAssembliesPath = (Resolve-Path "$Vs2008Path\PrivateAssemblies");

Polling the existence of a file with PowerShell

Sometimes you run into a situations where a given task spawns a separate thread and completes it’s work on that separate thread. Eg: sending a bit XMLA to SQL Server Analysis Services with Microsoft.AnalysisServices.Deployment.exe and then waiting for the processing to be completed. Anyway, here is a simple function that will wait untill a given file exists:

function WaitForFile($File) {
 while(!(Test-Path $File)) {
  Start-Sleep -s 10;
 }
}

Get current file in PowerShell

A while ago i wrote a small script to take care of deployment. Configuring the source folders went as following:

param(
	$BaseDir = (Get-Location).Path,
	$WebDir = (Resolve-Path "$BaseDir\web"),
	$DatabaseDir = (Resolve-Path "$BaseDir\database")
)

The problem with this code is that it only works when your current working directory is set to the location of this script. An administrator (or build system) invokes the script as following:

PS C:\Users\Admin>& 'D:\Deployments\20110124\Deploy.ps1';

Because we don’t want to annoy the consumer of our script with the burden of making sure he is in the correct directory we modified our code as following:

param(
	$BaseDir = (Split-Path $MyInvocation.MyCommand.Definition),
	$WebDir = (Resolve-Path "$BaseDir\web"),
	$DatabaseDir = (Resolve-Path "$BaseDir\database")
)

A quick win :)