If your working on SSIS and custom components, the key to debuging is the following,
Ensure you components are in the GAC and in the DTS (yes I know its every where) pipelines folder, create a strong name for your assembly. (See BOL).
The pdb, symbol files need to be in the pipeline folder as well.
I use the following as a post build event (project properties)
- copy "$(TargetDir)$(TargetName).*" "c:\Program Files\Microsoft Sql Server\90\Dts\PipelineComponents\$(TargetName).*"
- copy "$(TargetDir)$(TargetName).*" "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\$(TargetName).*"
- gacutil.exe -iF "c:\Program Files\Microsoft Sql Server\90\Dts\PipelineComponents\$(TargetFileName)"
In the debug properties I set the "Start External Program" to
and the Command line arguments to (change to your SSIS project/solution)
- "C:\Documents and Settings\simonsa\My Documents\Visual Studio 2005\Log Source Component Sample\CS\Integration Services Project1\Integration Services Project1.sln"
For debugging. Your component has to be in a seperate Visual Studio envionment to that of your SSIS package.
For design time debugging you attach to the Visual Studio exe devenv.exe (I have the full VS version, not sure if business Intelligence designer is something different). This is easily done by setting your project to launch devenv.exe. In addition you can specifiy a solution to open so that you have a base SSIS package already built. This is very useful.
For runtime debugging you need to attach to the DTSdebugHost.exe. This is only started when the you run your package. Therefore you need to set a breakpoint in your package to pause the package whilst you attach to the dtsdebughost.exe. BOL says to create a script task that pops up a msgbox I prefer to set an event handler to do this on the prevalidate event of the package. I think this ensures you debug as much as possible.
To attach to the dtsdebughost you can go to Debug|Attach to Process... where you get a list of processes to attach to. There should be 3 dtsdebughost.exe processes. One will say x86 in the Type column and one will say Managed, X86. You need the latter.
Being lazy I wrote a Visual Studio macro to do the attach for dtsdebughost. This is the code of the macro. This tries to attach either of the processes, the x86 only one doesn't work and so it checks this, if it fails it knows it got the wrong one.
Option Strict Off
Option Explicit Off
Imports EnvDTE
Imports EnvDTE80
Imports System.Diagnostics
Public Module SSISMacros
Sub AttachToDTSDebugHost()
Dim attached As Boolean = False
Dim proc As EnvDTE.Process
Dim e As System.Exception
Dim sProcess As String = "dtsdebughost.exe"
For Each proc In DTE.Debugger.LocalProcesses
If (proc.Name.Substring(System.Math.Max(proc.Name.LastIndexOf("\") + 1, 0)).ToLower() = sProcess) Then
'If proc.Programs.Count = 0 Then
Dim DebugCount As Long = DTE.Debugger.DebuggedProcesses.Count
proc.Attach()
If DTE.Debugger.DebuggedProcesses.Count > DebugCount Then
attached = True
MsgBox("attached to " & sProcess)
Exit For
End If
End If
Next
If attached = False Then
MsgBox("Cannot attach to process " & sProcess)
End If
End Sub
End Module