Debugging SSIS and using Data Viewer

- SSIS package can be debugged using breakpoints and data viewer

DEMO

- Add one variable called "counter" of integer data type
- Drag For Loop Counter in control flow.
- Set the properties like this.
     InitExpression : @counter = 1
     EvalExpression : @counter <= 10
     AssignExpression : @counter = @counter + 1
- Drag Data Flow Task inside For Loop Container
- Open DFT
- Drag OLEDBSource which should point to some table
- Drag OLEDBDestination which should point to some table
- Connect source and destination
- Double click on the link between source and destination
- Go to Data Viewers and click on Add.

- Go to General tab, and make sure "Data Grid" is selected
- Go to "Grid" tab and select columns for which you want to do see the data

- now  your data flow task should look like this

- come back to control flow
- Right click on Data Flow Task and "Edit Breakpoints"
- We have different options in breakpoints, where we want to debug.

- This shows that, if Data Flow Task gets pre-executed 5th time or more, debugger will be stopped.
- Now execute the package.
- Data viewer will display the data like this.

- Now you can see exactly what data is floating between source and destination
- Click on > button 4 times
- Now it will be stopped like this

- Now you can enable watch window like this

- Now drag variable into watch window (Name column)
- it will display the value of a variable.
- You can hit F5 to proceed further.