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