PowerShell Script - SCCM Remove In progress packages from all Distribution Points

 function ExecuteSqlQuery ($Server, $Database, $SQLQuery) { 

     $Datatable = New-Object System.Data.DataTable 

      

     $Connection = New-Object System.Data.SQLClient.SQLConnection 

     $Connection.ConnectionString = "server='$Server';database='$Database';trusted_connection=true;" 

     $Connection.Open() 

     $Command = New-Object System.Data.SQLClient.SQLCommand 

     $Command.Connection = $Connection 

     $Command.CommandText = $SQLQuery 

     $Reader = $Command.ExecuteReader() 

     $Datatable.Load($Reader) 

     $Connection.Close() 

      

     return $Datatable 

 }

Cls

$packagetoremove  = Get-Content -LiteralPath "D:\Script-New\ToremovePackagesFromInProgressDPs\Input.txt"

$count = $report.count

$series = 1

Foreach ($p in $packagetoremove)

{

 [string] $Server= "Servername" 

 [string] $Database = "CM_123" 

 [string] $UserSqlQuery= $("SELECT vSMS_DPStatusDetails.PackageID,v_Package.Name ,vSMS_DPStatusDetails.DPName,CASE

WHEN vSMS_DPStatusDetails.MessageState = 1 THEN 'Success'WHEN vSMS_DPStatusDetails.MessageState = 2 THEN 'InProgress'WHEN vSMS_DPStatusDetails.MessageState = 4 THEN 'Failed'END AS [State]

FROM            vSMS_DPStatusDetails INNER JOIN

                         v_Package ON vSMS_DPStatusDetails.PackageID = v_Package.PackageID

WHERE vSMS_DPStatusDetails.PackageID = 'CM0004DF' and vSMS_DPStatusDetails.MessageState != '1'

GROUP BY vSMS_DPStatusDetails.DPName, vSMS_DPStatusDetails.MessageState,vSMS_DPStatusDetails.PackageID,v_Package.Name

ORDER BY State") 

  # declaration not necessary, but good practice  

  $Report = New-Object System.Data.DataTable 

 $Report = ExecuteSqlQuery $Server $Database $UserSqlQuery

 Foreach ($app in $report)

 {

  $pkgid = $app.PackageID

  $dp = $app.DPName

  $appname = $app.Name

   Write-Host "Removing "  $appname" from "  $dp ------- $series " From total of " $count

 

  Remove-CMContentDistribution -ApplicationName '$appname'  -DistributionPointName $dp -Force

  $app = " "

  

  $series++ 

   

 }

 $p = " "

 $report = " " 

 }