Validating the Error Packages using Powershell

Create a Powershell file and save it in a required directory (For example: File name as ValidateProject.ps1 and directory like C: or D:)

Open the command prompt (open by run as an administrator)

Enter cd.. to go back and enter the directory where your powershell file is saved (type D: or C:)

Again enter as "Powershell" will get to the windows powershell mode

after this you may required the following commands (Get-ExecutionPolicy OR Set-ExecutionPolicy RemoteSigned)

//Refer - http://technet.microsoft.com/en-us/library/ee176949.aspx

Now enter the powershell file name prefix with .\ and end with which database you want to check. (For example:  .\ValidateProject.ps1 test)

This will validate all the packages in the test database and load the result in log file
// log file path mentioned in the ValidateProject.ps1 file

Below is the example Powershell code to validate error packages:

<#
.SYNOPSIS
    Validate SSIS Packages in a project
.DESCRIPTION
    This script will execute dtexec to validate all ssis packages in a directory and write the results to a log file.
.NOTES
    File Name  : ValidateProject.ps1
    Author     : Babu Kannan M
    Requires   : PowerShell V4
.LINK
#>

Param([string]$ProjectName)

if ($ProjectName -eq "imports") {
$PackageDirectory = "D:\Kannan WorkArea\Imports20\Imports_20\"
}
elseif ($ProjectName -eq "edw") {
$PackageDirectory = "D:\Kannan WorkArea\EDW20\EDW_20\"
}
elseif ($ProjectName -eq "lsdm") {
$PackageDirectory = "D:\Kannan WorkArea\LSDM20\LSDM_20\"
}
elseif ($ProjectName -eq "padm") {
$PackageDirectory = "D:\Kannan WorkArea\PADM20\PADM_20\"
}
elseif ($ProjectName -eq "webdm") {
$PackageDirectory = "D:\Kannan WorkArea\WEBDM\WEBDM\"
}
elseif ($ProjectName -eq "masterworksdatamart") {
$PackageDirectory = "D:\Kannan WorkArea\MasterWorksDataMart\MasterWorksDataMart\"
}
elseif ($ProjectName -eq "crmdm") {
$PackageDirectory = "D:\Kannan WorkArea\CRMDM\"
}
elseif ($ProjectName -eq "fdm") {
$PackageDirectory = "D:\Kannan WorkArea\FDM\FDM\"
}
else { $PackageDirectory -eq "Invalid Project" }

$LogDirectory = "D:\Powershell_Logs\"
$LogDate = get-date -f yyyy-MM-dd
$Log = [string]::Format("{0}Build_{1}.log",$LogDirectory,$LogDate)
Remove-Item $Log

$MsgOut = [string]::Format("Please pick up log files at: {0}\{1}",$LogDirectory,$Log)
write-output $MsgOut
$MsgOut = [string]::Format("Project Path for this validation is: {0}", $PackageDirectory)
write-output $MsgOut

$PackageFiles = gci $PackageDirectory"*.*" -Include *.dtsx | Where-Object {$_.PSIsContainer -eq $false} | Select-Object name

$PkgCount = $PackageFiles.Length
if ($PkgCount -eq $null) {$PkgCount = 1} #Account for 0 length array
$FailedCount = 0

$MsgOut =  [string]::Format("Validating {0} SSIS Package[s] in the {1} Project ", $PkgCount, $ProjectName)
write-output $MsgOut

foreach ($Package in $PackageFiles) {
$cmdargs = [string]::Format("-f ""{0}{1}"" /va /Report E",$PackageDirectory,$Package.Name)
$process = New-Object System.Diagnostics.Process
$process.StartInfo.FileName = "dtexec.exe"
$process.StartInfo.Arguments = $cmdargs
$process.StartInfo.UseShellExecute = $false
$process.StartInfo.RedirectStandardOutput = $true
$process.Start() | out-null;

$result = $process.StandardOutput.ReadToEnd()
$eval = $result.contains("DTSER_FAILURE")

if ($eval -eq $True) {
$MsgOut = [string]::Format("Package: {0} failed validation.", $Package.Name)
write-output $MsgOut
Add-content $Log -value $MsgOut
$errorheader = [string]::Format("==================={0} ERROR MESSAGE===================", $Package.Name)
write-output $errorheader
Add-content $Log -value $errorheader
write-output $result
Add-content $Log -value $result

$FailedCount = $FailedCount + 1
}
$process.WaitForExit()

}

$MsgOut = [string]::Format("Build Summary: {0} Package[s] of {1} failed validation during the build", $FailedCount, $PkgCount)
write-output $MsgOut
Add-content $Log -value $BuildSummary