Tim Van Wassenhove home

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