Enabling Additional Tracing for Data Collection (MDW)

If sysssislog or syscollector_execution_log_internal_message don’t give us a clue of what is the issue, we can enable additional tracing by adding a registry key as posted here

To enable tracing in a remote computer, we can execute this PowerShell script

 
$computer = "mycomputer"
$cred = Get-Credential mydomain\myadminaccount
Enter-PSSession $computer -Credential $cred

Push-Location 
if (-not(Test-Path("HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\SQLTools")))
{
    Set-Location "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server"
    New-Item -Name SQLTools
}
if (-not(Test-Path("HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\SQLTools\dcexec")))
{
    Set-Location "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\SQLTools"
    New-Item -Name dcexec
    New-ItemProperty -Name Components -PropertyType string -Path dcexec -Value "DCEXEC,TxDataCollector,DataCollectorController,DataCollectorTasks,Microsoft.SqlServer.Management.CollectorTasks.dll"
    New-ItemProperty -Name Tracelvl -PropertyType dword -Path dcexec -Value "4294967287"
    New-ItemProperty -Name Traceloc -PropertyType dword -Path dcexec -Value 3
    New-ItemProperty -Name Prefix -PropertyType string -Path dcexec -Value "date,time,pid,tid"
    New-ItemProperty -Name LogDir -PropertyType string -Path dcexec -Value "c:\temp\tracing"
    New-ItemProperty -Name LogFileMode -PropertyType string -Path dcexec -Value "Unique"
}
Pop-Location
Exit  

Then restart data collection using the script of a previous post

A trace file per process id will be created on c:\temp\tracing, once the error in question occurs, we can open the respective file to see the information logged. File name example:

c:\Temp\Tracing\dcexec_04_27_2015_09_22_28_PID30316_n.log

To disable tracing in a remote computer, we can execute this PowerShell script

 
$computer = "mycomputer"
$cred = Get-Credential mydomain\myadminaccount
Enter-PSSession $computer -Credential $cred

Push-Location 
if (Test-Path("HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\SQLTools"))
{
    Remove-Item "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\SQLTools" -Recurse 
}
Pop-Location  
Exit  

Then restart data collection using the script of a previous post