Breakpoints in SQL Server 2005 Integration Services SSIS

By: Jeremy Kadlec | Read Comments | Print Jeremy is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com and SQL Server MVP since 2009. Related Tips: More

Problem
In SQL Server 2000's Data Transformation Services (DTS) the tool had the ability to issue each portion of a package one step at a time.  With some custom coding, it was possible to determine variables and the package status by creating T-SQL tasks to write the items to a table.  Unfortunately, this process could become very tedious for large DTS Packages. 

Another alternative was to move portions of the DTS package that were working to another DTS Package.  Then have the problematic package call the first package and then step through the remainder of the tasks in the problematic DTS Package.  This just cut down on the number of clicks, but was also tedious.

With the many changes in SQL Server 2005, does Integration Services (SSIS) have a better way to review the variables and overall SSIS Package status at particular points in time?

Solution
Yes - SQL Server 2005 Integration Services (SSIS) has the ability to apply breakpoints to specific objects in the package.  When the SSIS Package is executed interactively in the SQL Server 2005 Business Intelligence Studio (BIDS), the breakpoint can give the Developer\DBA an opportunity to review the status of the data, variables and the overall status of the SSIS package.

How do I setup the breakpoints in SSIS?

In the BIDS, navigate to the control flow interface.  Right click on the object where you want to set the breakpoint and select the 'Edit Breakpoints...' option.  This will display the screen shot listed next section.

What are the conditions that I can set for a breakpoint in SSIS?

Breakpoints have 10 unique conditions when they can be invoked.  The breakpoints can also be used in combination.  One typical example is using both the OnPreExecute and OnPostExecute events to determine the status of the variables as the process begins and ends.

What other breakpoint parameters can be configured?

For each of the breakpoints, the Hit Count Type and Hit Count can be configured. 

The Hit Count Type values are:

• Always
• Hit count greater than or equal to
• Hit count multiple
• Hit count equals

The Hit Count value is an integer greater than 1.

The image below shows an example of these options, but is only an example not a probable configuration.

Next Steps

• The next time you have to troubleshoot an SSIS Package interactively, consider the SSIS breakpoints as a simple means to determine the status of your variables and your overall package.
• Rather than relying on the error messages that SSIS generates in production, consider setting up SSIS breakpoints in your development or test environment and then step through the package interactively to determine the root cause of the issue.
• Check out these MSSQLTips.com on DTS:
  • Modifying DTS Packages Using Disconnected Edit
  • Transforming Invalid Data Formats in DTS Using ActiveX Script
  • Converting Invalid Date Formats Using SQL Server 2000 Data Transformation Services
  • SQL Server 2000's DTS Authentication Alternative = UDL Files
  • SQL Server DTS command line utility
• Check out these MSSQLTips.com on SSIS:
  • Dynamic Flat File Connections in SQL Server Integration Services
  • SQL Server 2000 to 2005 Crosswalk - DTS Package to SSIS Package Execution
  • SQL Server 2000 to 2005 Crosswalk - The SQL Server Import and Export Wizard
  • SQL Server Crosswalk - Deploying a SQL 2000 DTS vs. a SQL 2005 SSIS package

Share:

Share

Tweet

Free SQL Server Learning Free webcast - Introduction to SQL Azure Demystify TempDB Performance and Manageability

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code