Tutorial Gateway

  • C
  • C#
  • Java
  • Python
  • SQL
  • MySQL
  • Js
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Go Programs
    • Python Programs
    • Java Programs

Breakpoints in SSIS

by suresh

Breakpoints in SSIS are very useful to understand the Data Flow at multiple levels. For example, you can use the SSIS breakpoints to understand the variable values at the Pre-Execution, Post-Execution, each Iteration, etc.

Let us see the step by step approach to configuring the Breakpoints in SSIS with an example. For this SSIS Breakpoints demonstration, we are going to use the below shown SSIS package.

I suggest you to refer Execute SQL Task in SSIS Full Row Set to understand the complete package setup and the data that we used.

Breakpoints in SSIS 1

And the variable used for this SSIS Breakpoints package are:

Breakpoints in SSIS 2

and the data that we used for the above package is:

Breakpoints in SSIS 16

Breakpoints in SSIS Example

In this example, we show you how to add Breakpoints to the Foreach Loop Container and the Data Flow task. To add SSIS breakpoints on a container, right-click on the SSIS Foreach loop container will open the context menu. Please select the Edit Breakpoints.. option from it.

Breakpoints in SSIS 3

Once you select the Edit Breakpoints.. option, a new window called Set Breakpoints will open.

Breakpoints in SSIS 4

The following are the list of SSIS Breakpoints and respective descriptions.

Break ConditionDescription
When the Task or Container receives the OnPreExecute event

This event raised or called when a task is about to execute. It is useful to watch the variables of the task or a container before it runs.

When the Task or Container receives the OnPostExecute event

This event raised or called immediately after the task is completed or executed. The OnPostExecute event is useful to watch the task or a container variable after the task finishes.

When the Task or Container receives the OnError event

The OnError event called when a task or container occur an error

When the Task or Container receives the OnWarning eventThe OnWarning event called when a task or container throws a warning.
When the Task or Container receives the OnInformation eventYou can use this OnInformation event when a container or task has to provide any information
When the Task or Container receives the OnTaskFailed eventThis SSIS Breakpoint event is called when the task failed
When the Task or Container receives the OnProgress eventThis event is called to update the task execution progress.
When the Task or Container receives the OnQuerycancel eventWhile processing the task, You call this event at any time. It helps to cancel the execution at any time.
When the Task or Container receives the OnVariableValueChanged event

The OnVariableValueChanged event called when the variable value changes. To raise this event, you have to set the RaiseChangeEvent of the variable to TRUE.

When the Task or Container receives the OnCustomEvent event

If you want to raise any task defined events, use this OnCustomEvent event.

For this SSIS Breakpoints demo, we are selecting the Breakpoints on the Preexecute event, and Post Execute event.

Breakpoints in SSIS 5

The SSIS Breakpoints allow four different options for the Hit Count type, and they are:

Hit Count typeDescription
AlwaysExecution will be suspended when the breakpoint hit.
Hit Count equalsExecution suspended when the Hit Count is equal to the number of times breakpoint has occurred.
Hit Count Greater than or Equal toExecution suspended when the Hit Count is greater than or equal to the number of times breakpoint has occurred.
Hit Count MultipleIf you set this option to 4, execution suspended every fourth time.
Breakpoints in SSIS 6

Click OK to close the SSIS Breakpoints configuration. From the below screenshot, you can see the red circle on the Foreach loop container.

Breakpoints in SSIS 7

Let us run the SSIS Breakpoints package and check the variables flow in the local’s window.

Breakpoints in SSIS 8

I think there are too many system variables in the local’s window. So, let me select the required variables (user-defined variables), and right-click on them will open the context menu. Please select the Add Watch option.

Breakpoints in SSIS 9

Now you can that the Watch window is showing only user-defined variables. Let me Hit the continue button

Breakpoints in SSIS 10

Now you can see that the variable values replaced with the last inserted row.

Breakpoints in SSIS 11

Let me remove the breakpoints on the Foreach loop container and add the SSIS breakpoints on the Data flow task.

Breakpoints in SSIS 12

Let us run the SSIS Breakpoints package. As you can, variables updated with the first row.

Breakpoints in SSIS 13

Clicking on the Continue button, variables updated with the second row

Breakpoints in SSIS 14

Once the table inserted, the task will complete.

Breakpoints in SSIS 15

Placed Under: SSIS

  • What is SSIS
  • Install SQL Server Data Tools
  • Learn SSIS in 28 Days
  • Create a SSIS Project
  • Create SSIS Package
  • SSIS Connection Manager
  • OLE DB Connection Manager
  • SSIS ADO Connection Manager
  • ADO.NET Connection Manager
  • SSIS Cache Connection Manager
  • SSIS Excel Connection Manager
  • SSIS File Connection Manager
  • SSIS FTP Connection Manager
  • SSIS SMO Connection Manager
  • Source Assistance in SSIS
  • Flat File Source in SSIS
  • OLE DB Source in SSIS
  • Excel Source in SSIS
  • ADO.NET Source in SSIS
  • SSIS FLAT FILE Destination
  • SSIS OLE DB Destination
  • SSIS ADO.NET Destination
  • SSIS Transformations
  • SSIS Audit Transformation
  • SSIS Aggregate Transformation
  • SSIS Aggregate Advanced Mode
  • SSIS Cache Transformation
  • SSIS Character Map
  • SSIS Conditional Split
  • SSIS Copy Column
  • SSIS Data Conversion
  • SSIS Derived Column
  • SSIS Export Column
  • SSIS Fuzzy Grouping
  • SSIS Fuzzy Lookup
  • SSIS Import Column
  • SSIS Lookup Introduction
  • Lookup – OLE DB Connection
  • SSIS Lookup in Full Cache Mode
  • SSIS Lookup – Case Sensitivity
  • SSIS Merge Transformation
  • SSIS Merge Join Transformation
  • SSIS Merge Join – Left Outer Join
  • SSIS Merge – Right Outer Join
  • SSIS Merge Join – Full Outer Join
  • SSIS Multicast Transformation
  • SSIS OLEDB Command
  • OLEDB Command – Delete Data
  • OLEDB Command-Update Data
  • SSIS Percentage Sampling
  • SSIS Pivot Transformation 2008
  • SSIS Pivot Transformation
  • SSIS Row Count Transformation
  • SSIS Row Sampling
  • Script Component as Source
  • Script Component as Destination
  • SSIS Script as Transformation
  • SSIS Sort Transformation
  • SSIS SCD Type 0
  • SSIS SCD Type 1
  • SSIS SCD Type 2
  • SSIS Term Lookup
  • SSIS Term Extraction Intro
  • Term Extraction – Extract Nouns
  • SSIS Extract Noun Phrases
  • Extract Nouns & Noun Phrases
  • Term Extraction – Exclusion Tab
  • SSIS Unpivot Transformation
  • SSIS Union All Transformation
  • SSIS For Loop Container
  • SSIS ForEach File Enumerator
  • SSIS ForEach SMO Enumerator
  • SSIS ForEach Variable
  • SSIS Foreach NodeList
  • Foreach ADO.NET Schema Rowset Enumerator
  • SSIS Bulk Insert Task
  • SSIS Data Profiling Task
  • Execute T-SQL Statement Task
  • SSIS Execute SQL Task Intro
  • SSIS Execute SQL Task Example
  • Execute SQL Task- Single Rowset
  • Execute SQL Task – Full Row Set
  • SSIS Execute Package Task
  • Execute Packages in SQL Server
  • Execute Packages in File System
  • SSIS Execute Package Project Reference
  • SSIS File System Task
  • SSIS File System Task- Copy Files
  • File System Task-Copy Directory
  • File System Task – Delete Files
  • File System Task – Delete Folder
  • File System Task -Move Directory
  • SSIS File System Task -Move File
  • File System – Move Multiple files
  • File System Task – Rename File
  • File System Task – Set Attributes
  • SSIS FTP TASK
  • SSIS Create Local Directory
  • SSIS Create Remote Directory
  • SSIS FTP Task Send Files
  • SSIS FTP – Send Multiple Files
  • SSIS FTP Task Delete Local Files
  • FTP TASK Delete Local Directory
  • FTP Task Delete Remote files
  • SSIS Delete Remote Directory
  • SSIS FTP Task Receive Files
  • SSIS FTP Receive Multiple Files
  • SSIS Script Task
  • Transfer SQL Server Objects Task
  • Transfer SQL Table Structures
  • Transfer SQL Tables with Data
  • Transfer SQL Stored Procedures
  • Transfer User Defined Functions
  • Transfer SQL Views in SSIS
  • SSIS Web Service Task
  • SSIS XML Task-Validate XML File
  • Transform XML File using XSLT
  • XML Task-XML files Differences
  • Create SSIS Catalog
  • Package Deployment using BIDS
  • Deploy Package Using SQL
  • Deploy using SQL Server Wizard
  • SSIS Breakpoints
  • SSIS Checkpoints
  • SSIS Error Handling
  • SSIS Event Handlers
  • SSIS Transactions
  • SSIS Logging
  • SSIS Parameters
  • SSIS Package Configuration
  • Configure using SQL Server
  • Config using Registry Entry
  • Conf with Environment Variable
  • SSIS XML Configuration File
  • XML Configuration File Part 2
  • SSIS Package Protection Level
  • SSIS Incremental Load
  • Incremental Load Example 2
  • SSIS Remove Double Quotes

Copyright © 2021 · All Rights Reserved by Suresh

About Us | Contact Us | Privacy Policy