How To Debug Ssis Package?

How To Debug SSIS Package There are three ways of debugging. They are: 1) By executing the package partially It multiple tasks are present in a package then we can execute a specific task. Rt click that taskàclick execute task. 2) By break points.
If you are a Developer and only wants to Design / Debug SSIS Packages in Visual Studio (i.e. SSDT) then you need step 1-3. Step1. Install SSDT BI for Visual Studio To design/debug SSIS Packages you will need to install SSDT. Its a set of tools and designers to enable SSIS development and its totally FREE.

How to debug the control flow in SSIs?

Debugging will help us find and resolve an issue in an SQL Server Integration Services (SSIS) package and we can debug the control flow in three ways: In SSIS, breakpoints can be enabled by using the Set Breakpoint dialog box. This is achieved by enabling break conditions based on the need.

Can I develop SSIS packages without using SSIs tools?

Although you can develop SSIS packages without them, it is so much easier once you’re familiar with these tools. SQL Server Business Intelligence Development Studio (BIDS) provides several tools you can use to troubleshoot the data flow of a SQL Server Integration Services (SSIS) package.

How do I debug a package in SSDT?

Debug a Package by Setting Breakpoints on a Task or a Container. This procedure describes how to set breakpoints in a package, a task, a For Loop container, a Foreach Loop container, or a Sequence container. In SQL Server Data Tools (SSDT), open the Integration Services project that contains the package you want.

Can we debug SSIS package?

After you stop package execution, the progress information remains available on the Execution Results tab. To enable or disable the display of messages on the Progress tab, toggle the Debug Progress Reporting option on the SSIS menu.

How can I see errors in SSIS package?

In the Solution Explorer, Right-click on the SSIS package and click on Execute. The Red-Cross icon on the execute SQL Task shows that the package execution failed. Click on the Progress tab for the detailed error message.

How do I debug SSIS package in Visual Studio 2015?

Steps: Go to the Control Flow and right click on the Data Flow Task (or any task where you want create a breakpoint) and select Edit Breakpoints. Now in the new Set Breakpoints window Select Break when the container receives the OnPostExecute event. Click OK.

How do I debug a variable in SSIS?

Solution:

  1. Right Click on File System Task and go to Edit Breakpoints.
  2. Choose the Break Condition.
  3. Execute your SSIS Package.
  4. To view the values of your variable Go to Debug–> Windows–> Locals. Let’s see the values of variables win Locals window.

How do I make SSIS package fail?

How to fail an SSIS Task

  1. Add an error to a sql task like divide by zero.
  2. Set the execution result to failure in a script task.
  3. Change the Forced execution value in the property of the task.

What is Fail component in SSIS?

Fail Component: The Data Flow task fails when an error or a truncation occurs. Failure is the default option for an error and a truncation. Ignore Failure: The error or the truncation is ignored and the data row is directed to the output of the transformation or source.

How do I manually fail an SSIS package?

You need to make property FailPackageOnFailure true try to retrieve the property FailPackageOnFailure of the particular task and assign the value true. so that package will be failed.

How do I manually test SSIS package?

To run a package, use one of the following procedures:

  1. Open the package that you want to run and then click Start Debugging on the menu bar, or press F5. After the package finishes running, press Shift+F5 to return to design mode.
  2. In Solution Explorer, right-click the package, and then click Execute Package.

How do I debug a SQL task in SSIS?

Select the execute sql task, right click on it and add a break point on the OnPreExecuteEvent. This will let you see the value of the variables used in the execute sql task parameters as explained by David.

What is SSIS DelayValidation?

DelayValidation Property:

If any object such as table or destination file etc. is not available then Package validation fails and Package stop execution. By setting this property to True, We enforce our SSIS Package not to validate that Task, Connection Manager or entire Package at start but validate at run time.

How do I enable debug progress reporting in SSIS?

To enable or disable the display of messages on the Progress tab, toggle the Debug Progress Reporting option on the SSIS menu. Disabling progress reporting can help improve performance while running a complex package in SQL Server Data Tools.

How do I execute a deployed SSIS package?

Execute the SSIS package from the catalog with the Execute Package GUI. Right-click on the package and select Execute from the popup menu. Click the OK button on the Execute Package GUI panel. Click the Yes button on the dialog box that asks if you wish to open the overview report.

What does it mean to debug in SSIs?

In simple words debugging means we are simply trying to find the errors or problems in our programs. so in SSIS we try to find problem and error in our package (Task Container). in SSIS it is a process we perform on DFT, and containers. how do we debug our SSIS package?

How do I debug a package in SSDT?

Debug a Package by Setting Breakpoints on a Task or a Container. This procedure describes how to set breakpoints in a package, a task, a For Loop container, a Foreach Loop container, or a Sequence container. In SQL Server Data Tools (SSDT), open the Integration Services project that contains the package you want.

How do I debug a SQL Server project?

So for debugging, go to your project’s Properties | Debug. Set the ‘Start Action’ to c:\\Program Files\\Microsoft SQL Server\\100\\DTS\\Binn\\DTExec.exe. Set the ‘Command line arguments’ to /File (your package name). Set some breakpoints in your code, and F5 to run your project.

How To Debug SSIS package

Learn how to troubleshoot an SSIS package in this tutorial. Debugging is one of the most fascinating aspects of SSIS development, thus as an SSIS developer, you must be familiar with the process of finding defects in packaged solutions.

How To Debug SSIS Package

There are three different methods of debugging. They are as follows:

1) By executing the package partially

If a package contains numerous tasks, we may select and perform a certain task from among them. Right-click on that task and then select ″Execute task.″

2) By break points.

Using break points, you may halt the execution of a package at a certain ″event″ and then choose whether to continue or stop the event.Break points are exclusively used in control flow scenarios.Consider the example in (fig: a), where a break point is set following a successful df-update.Learning MSBI, from the fundamentals to advanced methods, is easy with online video lessons offered by masters in the field of computer science.Request a Free MSBI Training Demo by enrolling here.If you want to advance your professional development, consider visiting Mindmajix, a global online training platform: SQL Server Training over the Internet This course will assist you in achieving greatness in your chosen field.

Navigation:

DF-update rt click edit break point mark break while the container gets the on post execute event option ok DF-update rt click ok Execute the job in such a way that a break occurs after DF-Update.Based on the outcome, keep an eye on the statistics or pay attention to the projected result.In the event that you want to continue, go to the debug menu and select continue.If you want to stop debugging, go to the debug menu and select stop debugging.Hit count: We will take it into consideration, together with the break point, in order to take a break in accordance with the circumstance.This is something that is applicable and usable.

  1. Generally speaking, when we have containers such as ″for loop″ and so on (except the type always).
  2. Types 1.
  3. Always: When the break point is reached, the execution is automatically halted.
  4. 2.

Hit count equal to: When the number of times the break threshold has been reached equals the number of times the execution is paused.3.Hit count more than or equal to: When the number of times the break point has been reached is equal to or greater than the hit count, execution is paused.4.Multiple of the hit count: When a multiple of the hit count occurs, execution is paused.

  1. For example, if we set this option to five, it will take a break every fifth time it is used.

Data viewer:

In the data flow job between the source, destination, and transformation, we only utilize this data view choice in the linkages between the components; otherwise, we don’t use it at all. rt click on any of the links in the data flow task data viewers rt click add rt click pick grid rt click ok

 Precedence Constraints 

These are important for controlling the flow of information between distinct jobs in a control flow scenario. Constraints and alternatives: It is possible to do two different evolution operations.

a) Constraint:

Success (green link): If the preceding component is successful, the other component is executed. Failure (Red link): If the preceding component fails, the subsequent component is executed. Complete (blue link): If the preceding component is either a success or a failure, the subsequent component is executed.

b) Expression 

The phrase is supported, and when it is satisfied or reached, the other task is carried out. For example: @counter=6 Counter is a variable in programming (may be for loop or any).

c) Expression and constraint 

If both requirements are met, only the other duty is carried out.

d) Expression or constraint 

If any of these tasks is completed successfully, the other job is carried out.

Implementation of expression:

Create a variable with the name ″counter.″ Consider using a For loop and modifying it to include the following settings: Right-click the mouse to choose properties: @counter=6 is the initial [email protected] is the evaluation Exp.Assign the following expression: @[email protected] Right-click on the link and select ″Edit.″ Expression and constraint evaluation are two types of evaluation operations.Success has a monetary [email protected]==6 is an expression.

Multiple Constraints

Several constraints are used to interoperate with one another and to regulate the execution of the restricted job. There are two options:

  1. Several constraints are used to interoperate with one another and to regulate the execution of a restricted job. The following are two options:

Priority constraints can be used to manage work flow in a number of situations. Take a look at some MSBI Sample Resumes! Download and customize your resume to stand out to top employers! Now is the time to download!

Debugging Control Flow in SSIS – Part 1

By: | Updated: 2016-11-24 | Comments (1) | Related: Integration Services Development (More Information)

Problem

When debugging control flow logic in SQL Server Integration Services, what should I do?

Solution
  • An SQL Server Integration Services (SSIS) package may be debugged in three ways to assist us identify and address a problem. We can debug the control flow in three different methods. Setting up breakpoints
  • utilizing the information on the Progress tab
  • utilizing Debug windows

Setting up breakpoints in SQL Server Integration Services

Breakpoints can be enabled in SSIS by selecting the Enable Breakpoint option from the Set Breakpoint dialog box.This is accomplished by permitting break conditions based on the situation’s requirements.Break points can be configured in two different ways in the SQL Server Data Tools (SSDT).This type allows you to specify a break point for a container, task, or package using the break point configuration.The second kind will allow you to establish a breakpoint for the script job that you are working on.

Breakpoint for an SSIS package

When the break condition is met, the package will be suspended for the course of the execution.As a result, we will be better able to investigate the problem at that time.SSIS has 10 different break conditions that may be applied to tasks and containers respectively.The breakpoints dialog box will assist us in configuring this.The following steps can be taken to create a breakpoint for a package.Select ″Edit Breakpoints″ from the context menu of the package by right-clicking on it.

  1. Select the necessary events to be monitored from the breakpoint selection box.
  2. As seen in the image below, breakpoints for the package’s OnPreExecute and OnError events have been set up for the package.
  3. To save your work, click OK.
  4. Take note of the red dot in the control flow diagram (this is an indication of package level breakpoints).

It will be highlighted with a red backdrop and a yellow arrow when the control flow tab is selected during the execution process.This is an indicator that SSDT is awaiting the user’s response to the request.The breakpoint window will provide a list of the events that are now accessible for debugging.When the package is running, breakpoints can also be adjusted (added, deleted, and amended) while it is still operating.

Breakpoint for an SSIS container

In the example below, we have established a ″For Loop″ container to house the code.A SQL job has been added to the ″For Loop″ for your convenience.Detailed configuration information for the ″For Loop″ is shown in the image below.A total of 5 iterations of the ″For Loop″ will be performed (value of the LoopCount variable – 0,1,2,3 & 4).The breakpoint for the for the loop may be set by selecting ″For Loop″ from the context menu and selecting ″Edit Breakpoints.″ If you are using the ″For Loop,″ you may use a particular breakpoint condition called ″Break at the beginning of every iteration of loop.″ For each iteration of the ″For Loop,″ I have chosen this particular breakpoint condition to cause the loop to terminate.The hit counter type has been set to ″Always″ in the settings (the default value).

  1. During execution, the package will be halted at the start of each iteration to allow for debugging.
  2. Each repetition will result in an update to the output window.
  3. Due to the fact that this is the first iteration, the SQL job has not yet been executed.
  4. The messages displayed in the output window for the third iteration are highlighted in the image below.

Now that the SQL Task has been run twice (for Iteration 1 and 2), the green signal on the SQL Task has been illuminated on the task.

Progress tab in SQL Server Integration Services

The progress tab of the SSDT designer displays information about the jobs and containers that have been created.This comprises the start and completion timings, as well as cautions and error messages, which are displayed in the sequence of execution.Immediately upon the completion of the package’s execution, the progress information is still visible on the Execution Results tab.The progress tab will also display information about the current state of debugging events.According to the image below, a Pre-execute event has been received by the package.It is possible to continue working on the package when debugging is complete (by pressing F5 or using the Continue button).

See also:  What Do You Do If Amazon Package Is Stolen?

Summary

Breakpoints are a powerful debugging tool for SSIS and are used extensively in the industry. You will be able to master it if you put in the time to practice with different instances. There are situations when they can assist you in identifying problems with a few basic measures.

Next Steps
  • More information on ″How to debug a package″ may be found here.
  • You may learn more about ″Debugging Control Flow″ here.
About the author

Nat Sundar is an independent SQL BI consultant based in the United Kingdom. He holds a Bachelors Degree in Engineering from the University of Cambridge. See all of my recommendations The article was last updated on November 24, 2016.

Debugging Data Flow in SQL Server Integration Services

By utilizing the SSIS Troubleshooting tools, you may save a significant amount of time.These allow you to deal with smaller data samples, check row counts, use data viewers, define error handling, and keep track of the progress of the package’s execution.Although you can create SSIS packages without using these tools, they make the process considerably simpler once you get familiar with them.A number of tools are available in the SQL Server Business Intelligence Development Studio (BIDS) that may be used to troubleshoot the data flow of a SQL Server Integration Services (SSIS) package.It is possible to sample a subset of data, gather data flow row counts, observe data as it goes via data pathways, reroute data that causes issues, and monitor package execution using the tools available.Any package that contains a data flow can benefit from these capabilities, independent of the data’s source or destination, as well as the transformations that are being applied.

  1. The more your understanding of debugging tools, the more effectively you will be able to diagnose your data flow problems.
  2. In this post, I will demonstrate the operation of each debugging tool.
  3. A test environment was created in order to do this, and it consisted of three components: A comma-separated text file, a SQL Server database, and an SSIS program that reads data from the text file and puts it into the table.
  4. It contains data from the Person.Person table in the AdventureWorks2008R2 database, which is included in the text file.

To populate the file, I used the bcp command, which is as follows:

bcp ″SELECT TOP 10000 BusinessEntityID, FirstName, LastName FROM AdventureWorks2008R2.Person.Person ORDER BY BusinessEntityID″ queryout C:\DataFiles\PersonData.txt -c -t, -S localhost\SqlSrv2008R2 -T

Later on, after I had generated the file, I modified the first row of data included inside it by increasing the LastName value contained within the first row to a string of more than 50 characters in length.According to what you’ll see later in the post, I did this in order to inject an error into the data flow, which would allow me to illustrate how to deal with problems like this.The following Transact-SQL script was used to construct the PersonName table in the AdentureWorks2008R2 database, which you can see below:

USE AdventureWorks2008R2GOIF OBJECT_ID(‘dbo.PersonName’) IS NOT NULLDROP TABLE dbo.PersonNameGOCREATE TABLE dbo.PersonName(NameID INT PRIMARY KEY,FullName NVARCHAR(110) NOT NULL)
  • Following the configuration of the source and target, I developed an SSIS package. The following components were initially included in the package, which I configured as follows: The AdventureWorks2008R2 database is managed by a connection manager.
  • A connection manager for connecting to the text file containing the source data.
  • A SQL task that executes and truncates the PersonName table
  • In this case, a Data Flow task is used to receive data from the text file, build a derived column, and then put the information into the PersonName database.

Among the data flow components I added to the package were those that were linked to debugging the data flow, as seen in Figure 1.PLEASE NOTE: At the bottom of this post, you will find a link to download the SSIS package.Figure 1 shows a diagram of a compass.The data flow in the sample SSIS package is configured in this section.The OLE DB Source, Derived Column, and OLE DB Destination components are the data flow components that are specifically designed for processing the Person data.This transformation concatenates the first and last names into a single column named FullName using the Derived Column transformation.

  1. There are several additional components in the data flow that are special to debugging, and they will be addressed in greater depth later in this article.

Working with a Data Sample

Working with only a subset of data when designing an SSIS package that gets vast volumes of data might be beneficial until you’ve handled any difficulties with the data flow that may have arisen.SSIS has two data flow components that allow you to work with a subset of data that has been randomly picked.If you want to include a certain number of rows in your random data sample, you may use the Row Sampling Transformation component, and if you want to include a percentage of rows in your random data sample, you can use the Percentage Sampling Transformation component.There are two data outputs supported by both components: one for the sampled data and another for the unsampled data.Each component also allows you to set a seed value, which ensures that the samples are same each time the package is executed.A link exists between the seed value and the operating system’s tick count.

  1. It is possible to execute the data flow several times without specifying a seed value, but each time the data sample is different.
  2. You’ll see that I included a Row Sampling Transformation component directly after the Flat File Source component in Figure 1.
  3. Please refer to Figure 1 for further information.
  4. The Row Sampling Transformation Editor is seen in Figure 2.

Keep in mind that I configured the component to retrieve 1000 rows of sample data, but I did not give a seed value for the component.Figure 2: Choosing a sample of data from the data flow diagram.You have the option of naming the outputs for the sample and non-sample data if you so like.In this example, I’ve kept the default names alone and connected to the next component in the data flow using the Sampling Selected Output data path that was created before.Now, the data flow will only comprise the 1000 rows that were chosen at random.

Verifying Row Counts

Working with only a subset of data when designing an SSIS package that gets vast volumes of data might be beneficial until you’ve handled any difficulties with the data flow that you’re experiencing.You can deal with a randomly selected sample of data using two data flow components provided by the SSIS system.If you want to include a certain number of rows in your random data sample, you may use the Row Sampling Transformation component, and if you want to include a percentage of rows, you can use the Percentage Sampling Transformation component.There are two data outputs supported by both components: one for sampled data and another for unsampled data.As an additional feature, you have the option to give a seed value so that the samples remain consistent no matter how many times you execute the package.A link exists between the seed value and the operating system’s tick count.) It is possible to execute the data flow several times without specifying a seed value, but the data sample is different each time.

  1. If you look at Figure 1, you’ll notice that I placed a Row Sampling Transformation component directly after the Flat File Source component.
  2. This was done to simplify the code.
  3. The Row Sampling Transformation Editor is illustrated in Figure 2.
  4. I made a conscious decision not to give a seed value when configuring the component to obtain 1000 rows of sample data.

The data flow is shown in Figure 2 and a sample is chosen from it You can give the outputs for the sample data and the non-sample data different names if you so choose.In this example, I’ve kept the default names alone and connected to the next component in the data flow using the Sampling Selected Output data path that I created before.With these changes, only a random 1,000 records will be included in the data flow.

Adding Data Viewers to the Data Path

Viewing the data itself as it flows via a data route can be beneficial for diagnosing data flow issues in a network.This may be accomplished by including one or more data readers into your data flow.SSIS supports a number of different types of data readers.The grid data viewer, which presents the data in a tabular style, is the most often used of these.You may, on the other hand, develop data viewers that display histograms, scatter plot charts, and column charts, among other things.These forms of data viewers are typically more beneficial for more analytical types of data analysis, but for simple troubleshooting, the grid data viewer is frequently the best place to start looking for answers.

  1. As illustrated in Figure 4, to construct a grid data viewer, you must first open the editor for the data path on which you want the data to be shown, and then navigate to the Data Viewers page.
  2. The characteristics of the data flow channel can be changed in Figure 4.
  3. The Data Flow Path editor is where you will add all of your data readers, regardless of what type they will be.
  4. The Add button will activate the Configure Data Viewer dialog box, as shown in Figure 5.

To add a data viewer, choose it from the drop-down menu and click OK.The sort of viewer you want to create and the name you want to give to that viewer are both specified here.Figure 5: Adding a grid data viewer to a data stream is illustrated.Go to the Grid tab, as shown in Figure 6, after you have selected the Grid option from the Type list and provided a name for it.This is the step in which you decide which columns to include in the grid and which ones to exclude.

  1. As of right now, we’re just concerned with the BusinessEntityID and FullName columns, because those are the only columns in our target database that we’re interested in.
  2. Figure 6: Creating a grid data viewer from scratch You may then click OK once you’ve specified which columns should be included in the grid.
  3. You will be returned to the Data Flow Path Editor after completing this step.
  4. The new grid data viewer should now be visible in the Data Viewers list, as seen in the screenshot below.
  5. In addition, a little symbol has been put next to the data route for convenience (shown in Figure 1).

The program will stop operating at the point in the data route where the data viewer has been configured, and a window will open, displaying the data in that section of the data flow.This is useful for debugging packages that have data viewers.Figure 7 is the grid data viewer that I configured on my data flow for viewing grid data.Figure 7: A grid data viewer is used to see sample data.Take note of the fact that the BusinessEntityID and FullName values for each row are displayed in the data viewer.It is possible to navigate through the list, disconnect the viewer from the data flow, reconnect the viewer to the data flow, or copy the data to the clipboard.

The data in and of itself, as well as the final product of the package, are not changed.

Configuring Error-Handling on the Components

Many data flow components allow you to specify how to deal with data that may cause an error to be generated.By default, if data produces an error, the component will fail; however, you may set some components to divert issue rows in order to avoid failure.Take, for example, Figure 1, where you can see that the Flat File Source has an extra data route output, which is indicated by the color red.It is possible to utilize the red data path to collect any incorrect rows that are output by the component if the component has been correctly setup.It was necessary to link the red data path to a Flat File Destination component in order to save rows that create errors in a text file.As seen in Figure 8, when you link an error output to another component, the Configure Error Output dialog box displays.

  1. Take note that you may specify what action should be taken in the event of an error or a truncation for each column.
  2. The cause of a mistake might be anything as simple as inaccurate data or an improper data type.
  3. A truncation happens when a value is too lengthy for the type that has been specified.
  4. When the column is designed to fail the component by default, it does so regardless of whether there is an error or truncation.

Figure 8: Using a data flow component to redirect rows of information.By asking that the row be redirected, you can override the default behavior of the row.In this particular instance, I elected to reroute all columns regardless of whether there was an error or truncation in the data.As a result, I modified the Error and Truncation choices for each row and column to the option Redirect row to achieve this.I then configured the Flat File Destination component with a new data source that points to a text file that will be used to capture the outputted rows if there are any errors or truncations, as described previously.

  1. As you may recall from earlier in the post, I changed the last name in the first row of the source file by making the last name excessively long, which resulted in the last name being edited.
  2. Therefore, I would anticipate that the first row would fail and that the user would be forwarded to the new error file.
  3. During the configuration of the destination component and connection manager, you’ll note that a row for the outsourced row is produced, as well as a column for the numeric error code and a column for the identity of the source column that creates the issue.
  4. When a row is sent to the error output, it is stored to the error file together with the error number and column identification, which are both recorded in the error file.
  5. The values in the redirected row are separated by commas, but they are processed as if they were a single value in the original row.

Monitoring Package Execution

  • In order to debug the data flow, the last tools to use are those that are connected to the package execution and the SSIS design surface. During the execution of a package, you may monitor the data flow to determine what is occurring with each component. The row counts are presented next to the data pathways, and the colors of the components change as they are being used in the execution. By looking at the colors, you may tell what stage of execution is being followed: White. The component has not been run as of yet.
  • Yellow. Currently, the component is extracting, altering, or loading information.
  • Green. Component has concluded its operation
  • Red. The component created errors, resulting in the package’s execution being halted.
See also:  How Much Is A Post Office Box Per Month?

If a component begins to glow red, you know that you have a problem.However, every now and again, a component will become yellow and hang there.And in such scenario, you’re still facing an issue.If, on the other hand, everything is working properly, the components will first turn yellow and then green, as seen in Figure 9.Figure 9: Visualizing the progress of the data flow on the design surface Take note of the fact that the number of rows that travelled through the data pathways during the previous buffer is displayed on the design surface.The error file has been forwarded to one of the rows, as you can see in the screenshot.

  1. There are also 9,999 rows in the data path that leads to the Row Sampling transformation, but only 1,000 rows in the data path that follows the transformation.
  2. For information on the package execution, you should turn to the Progress tab if an execution does not complete successfully (red or hanging yellow).
  3. In this section, you will discover information about each component, as well as information on the data that is passing through those components.
  4. Figure 10 depicts the Progress tab after I had completed the execution of my program.

Figure 10: Using the Progress tab to monitor the progress of a package’s execution It is important to note that the Progress tab contains information about the Data Flow job and its data pipeline.The information provided here is merely a portion of the total amount of information available.To see the rest of the content, you must scroll down.However, as you can see, in addition to all of the informative displays, there are a number of warning messages as well.In this particular instance, the warning signals suggest that the unsampled data is not being used, something we already knew about from previous experience.

  1. Some cautions, on the other hand, might be important pieces of information to have.
  2. In addition, the Progress tab indicates any errors that occur throughout the execution of the program, as well as all of the other events that occur.

The Data Flow Debugging Tools

You may not need to use all of the tools that SSIS provides for debugging your data flow, but any tools you do apply will be extremely beneficial when attempting to diagnose a problem with your data flow.Working with data samples, checking row counts, employing data viewers, establishing error-handling, and monitoring package execution, you should be able to identify any potential difficulties in your data flow and isolate the source of the problem.You can next take the measures necessary to remedy the issues that have been identified.Finding the root of an issue might take an unusual amount of time if you do not have access to the SSIS troubleshooting tools.It is possible that the time and effort you put in today to become familiar with these tools and take advantage of their capability may pay off handsomely the next time you run an SSIS package.

Debugging Control Flow – SQL Server Integration Services (SSIS)

  • Continue to the main content This browser is no longer supported by the manufacturer. You may benefit from the newest features, security updates, and technical support by switching to Microsoft Edge from Internet Explorer. Article published on October 8, 2021.
  • It will take you 7 minutes to read this.

The information you provide will be forwarded to Microsoft: By clicking the submit button, your input will be used to improve Microsoft products and services in the future. Policy on personal information. Thank you very much.

In this article

  • This applies to the SQL Server database (all supported versions) In Azure Data Factory, the SSIS Integration Runtime is available. It is possible to troubleshoot the control flow in an Integration Services package using the SQL Server Data Tools (SSDT) and Microsoft Integration Services, which are both included in the SQL Server Data Tools (SSDT) and Microsoft Integration Services. Breakpoints on containers and jobs are supported by the Integration Services.
  • At runtime, SSIS Designer gives progress reports
  • SQL Server Data Tools (SSDT) provides debug windows
  • and other features are available.

Breakpoints

It is possible to establish breakpoints using the Set Breakpoints dialog box in SSIS Designer.You may do so by activating break conditions and setting the number of times a breakpoint can occur before the package’s execution is halted.Individual component breakpoints can be enabled at the package level or at the component level for each individual component.Should a task or container have break conditions enabled at the task or container level, a breakpoint icon will be displayed next to that task or container on the control flow design surface of the Control Flow tab.It is possible that the package’s break conditions will be activated, in which case a breakpoint icon will show on the tab label for the Control Flow tab.Breakpoints are indicated by a change in the breakpoint indicator, which may be used to identify the cause of the breakpoint.

  1. When the package is running, you have the ability to add, delete, and modify breakpoints.
  2. There are eleven break conditions available in Integration Services, and you may enable them on any job or container.
  3. In the Set Breakpoints dialog box, you may specify whether or not breakpoints should be enabled based on the following conditions:
Break condition Description
When the task or container receives the OnPreExecute event. Called when a task is about to execute. This event is raised by a task or a container immediately before it runs.
When the task or container receives the OnPostExecute event. Called immediately after the execution logic of the task finishes. This event is raised by a task or container immediately after it runs.
When the task or container receives the OnError event. Called by a task or container when an error occurs.
When the task or container receives the OnWarning event. Called when the task is in a state that does not justify an error, but does warrant a warning.
When the task or container receives the OnInformation event. Called when the task is required to provide information.
When the task or container receives the OnTaskFailed event. Called by the task host when it fails.
When the task or container receives the OnProgress event. Called to update progress about task execution.
When the task or container receives the OnQueryCancel event. Called at any time in task processing when you can cancel execution.
When the task or container receives the OnVariableValueChanged event. Called by the Integration Services runtime when the value of a variable changes. The RaiseChangeEvent of the variable must be set to true to raise this event. ** Warning ** The variable associated with this breakpoint must be defined at the container scope. If the variable is defined at the package scope, the breakpoint does not get hit.
When the task or container receives the OnCustomEvent event. Called by tasks to raise custom task-defined events.
  • Several tasks and containers feature specific break conditions that may be used to define breakpoints in addition to the standard break conditions that are accessible to all jobs and containers. For example, you may activate a break condition on the For Loop container, which will establish a breakpoint at the beginning of each iteration of the loop and pause execution until the breakpoint is reached. It is possible to adjust the behavior of a breakpoint in order to increase its flexibility and power. The following parameters can be used to do so: It is the hit count, which is the maximum number of times that a break condition can occur before the execution is stopped.
  • The hit count type, or the rule that determines when the break condition triggers the breakpoint, is defined as follows:

Furthermore, some tasks and containers have specific break circumstances for creating breakpoints in addition to the break conditions that are available to all jobs and containers.When a break condition is enabled on the For Loop container, it sets a breakpoint at the start of each iteration, preventing the loop from being executed until the break condition is enabled again.You may customize the behavior of a breakpoint by supplying the following parameters, which will give you more freedom and control.The hit count, which is the maximum number of times a break condition can occur before the execution is halted.The hit count type, or the rule that determines when the break condition causes the breakpoint to be triggered.

Hit count type Description
Always Execution is always suspended when the breakpoint is hit.
Hit count equals Execution is suspended when the number of times the breakpoint has occurred is equal to the hit count.
Hit count greater than or equal to Execution is suspended when the number of times the breakpoint has occurred is equal to or greater than the hit count.
Hit count multiple Execution is suspended when a multiple of the hit count occurs. For example, if you set this option to 5, execution is suspended every fifth time.

To set breakpoints

Breakpoints may be used to debug a package by placing them on a task or a container.

Progress Reporting

Two methods of progress reporting are available in SSIS Designer: color-coding on the design surface of the Control Flow tab and progress messages on the Progress tab.When you execute a package, SSIS Designer illustrates the progress of the execution by presenting each task or container with a color that represents the state of the current execution.By looking at the element’s color, you may identify whether it is waiting to run, is already running, has completed successfully, or has terminated in an unsuccessful manner.The color-coding is no longer visible once you have terminated package execution.The colors that are used to show the status of an execution are described in the following table.

Color Execution status
Gray Waiting to run
Yellow Running
Green Ran successfully
highlighted Ran with errors

The Progress tab displays a list of tasks and containers in the order in which they were executed, as well as start and end times, warnings, and error messages.Following the completion of package execution, the progress information is still visible on the Execution Results tab.a.Note Toggle the Debug Progress Reporting option on the SSIS menu to allow or prevent the display of messages on the Progress tab while working with SSIS.The Progress tab is depicted in the following diagram.

Debug Windows

When working with breakpoints and debugging packages that contain breakpoints, the SQL Server Data Tools (SSDT) provides a number of windows that you may utilize to assist you.In order to learn more about each window, first open the window and then press F1 to bring up Help for that particular window.Click on the Debug menu in SQL Server Data Tools (SSDT), then point to Windows and then click on Breakpoints, Output, or Immediate to bring up the appropriate windows.Each window is described in detail in the following table.

Window Description
Breakpoints Lists the breakpoints in a package and provides options to enable and delete breakpoints.
Output Displays status messages for features in SQL Server Data Tools (SSDT).
Immediate Used to debug and evaluate expressions and print variable values.

Debug a Package by Setting Breakpoints on a Task or a Container

Working with breakpoints and debugging packages that contain breakpoints is made easier using SQL Server Data Tools (SSDT), which provides a number of windows to help you accomplish just that.Using the F1 key to display Help for a specific window will provide further information about that window.Click on the Debug menu in SQL Server Data Tools (SSDT), then point to Windows and then click on Breakpoints, Output, or Immediate to bring up the respective windows.Detailed descriptions of each window are provided below.

To set breakpoints in a package, a task, or a container

  1. Start by opening the Integration Services project that contains the package you wish to use in SQL Server Data Tools (SSDT).
  2. Double-click the package in which you want to establish breakpoints to see the options.
  3. Perform the following steps in SSIS Designer: Create breakpoints in the package object by selecting the Control Flow tab, placing the mouse anywhere on the backdrop of the design surface, selecting Edit Breakpoints from the context menu, and clicking OK.
  4. To create breakpoints in a package control flow, choose the Control Flow tab, right-click a task, a For Loop container, a Foreach Loop container, or a Sequence container, and then select Edit Breakpoints from the drop-down menu.
  5. To establish breakpoints in an event handler, choose the Event Handler tab, right-click a job, a For Loop container, a Foreach Loop container, or a Sequence container, and then select Edit Breakpoints from the drop-down menu.
  1. Select the breakpoints that you want to activate in the Set Breakpoints dialog box.
  2. It is possible to change the hit count type and the hit count number for each breakpoint if desired.
  3. The package may be saved by selecting Save Selected Items from the File menu.

Set Breakpoints

The Set Breakpoints dialog box allows you to specify the events on which breakpoints should be enabled, as well as the behavior of the breakpoints themselves.

Options

Enabled To activate a breakpoint on an event, choose it from the drop-down menu. Condition of Disruption View a list of the events that are available for you to use as breakpoints. Type of Hit Count Specify the time period during which the breakpoint will take effect.

Value Description
Always Execution is always suspended when the breakpoint is hit.
Hit count equals Execution is suspended when the number of times the breakpoint has occurred is equal to the hit count.
Hit greater or equal Execution is suspended when the number of times the breakpoint has occurred is equal to or greater than the hit count.
Hit count multiple Execution is suspended when a multiple of the hit count occurs. For example, if you set this option to 5, execution is suspended every fifth time.

Count the number of hits You can choose the amount of hits that will cause a break to be triggered. If the breakpoint is constantly in effect, this option will not be offered to you.

See Also

Instruments for Troubleshooting in Package Development Breakpoints may be used to debug a script by placing them in the Script Task and Script Component.

How to Debug SSIS Package

What exactly does the term ″Debug″ mean?In layman’s terms, debugging is the process of attempting to identify and correct flaws or issues in our programs.As a result, we use SSIS to look for problems and errors in our package (Task Container).Specifically, it is a procedure that we run on DFT and containers in SSIS.Consequently, the question is: how can we debug our SSIS package?The solution is that we can create a breakdown point in a package, a job, a For Each Loop Container, a Sequence Container, or a For Each Loop Container.

  1. Setting breakpoints in a package, a job, or a container is a common practice.
  2. 1.
  3. Take the package out of the box.
  4. So, for the sake of this tutorial, I’ll use a package called TestPkg.

2.There are two methods for establishing a break point.1.Pressing the F9 key will place the cursor in the debugging mode.Alternatively, you might choose the second option.

  1. 2.
  2. To establish breakpoints in the package object, choose the Control Flow tab, position the pointer anywhere on the design surface’s backdrop, right-click, and then select Edit Breakpoints from the context menu.
  3. If necessary, you can enable as many break conditions as are required based on the requirements you have.
  4. The hit count type and the hit count number for each breakpoint can be specified optionally; however, they are not required.
  5. The Control Flow tab is where you will define breakpoints in your package control flow.

Right click a task, a For Loop Container, a Foreach Loop Container, or a Sequence Container and then select Edit Breakpoints from the context menu.Once the break point has been established, the pointer will appear as a red spot on the DFT screen.The hit count type and the hit count number for each breakpoint can be specified optionally; however, they are not required.Breakpoints may be added to an event handler by selecting it from the Event Handler tab, right-clicking on a task or Loop Containers, and then selecting Edit Breakpoints.

Debugging Data Flow – SQL Server Integration Services (SSIS)

  • Continue to the main content This browser is no longer supported by the manufacturer. You may benefit from the newest features, security updates, and technical support by switching to Microsoft Edge from Internet Explorer. Article published on October 19, 2020.
  • It takes 12 minutes to read

The information you provide will be forwarded to Microsoft: By clicking the submit button, your input will be used to improve Microsoft products and services in the future. Policy on personal information. Thank you very much.

In this article

  • This applies to the SQL Server database (all supported versions) In Azure Data Factory, the SSIS Integration Runtime is available. Data flow troubleshooting capabilities and tools are included in both Microsoft Integration Services and the SSIS Designer, which you may use to debug the data flows in an Integration Services package. Data viewers are provided by SSIS Designer
  • row counts are provided by SSIS Designer and Integration Services transformations
  • progress reporting is provided by SSIS Designer at run time.

Data Viewers

  • Information between two components in a data flow is shown by data viewers.
  • In addition to displaying data when it is extracted from a data source and initially enters a data flow, data viewers can also display data before and after a transformation updates the data, as well as before the data is loaded into its destination.
  • Attaching data viewers to the path that connects two data flow components allows you to see the information shown in the data.
  • This capability makes it easy to recognize unusual data values, see how a transformation alters column values, and determine why a transformation fails.
  • Suppose you discover that a lookup in a reference table fails, and you decide that the solution is to include a transformation that supplies default values for empty columns.

A data viewer is a program that displays data on a grid.You may choose which columns to display by utilizing a grid.The values for the columns that have been selected are shown in a tabular style.On a route, you may also include numerous data viewers, which is really useful.Different data viewers for different columns of data may be created to display the same data in different forms.

For example, you can build a chart view and a grid view of the data, or you can construct multiple data viewers for the same data.Adding a data viewer to a path triggers the addition of a data viewer icon on the design surface of the Data Flow tab, immediately adjacent to the path.When doing transformations that might produce many outputs, such as the Conditional Split transformation, it is possible to add a data viewer on each of the paths.When the program is launched, a Data Viewer window appears, which shows the information provided by the data viewer format in question.

Example: A data viewer that employs the grid format displays data for the selected columns, the number of output rows sent to the data flow component, and the number of rows that are now being shown.The information is displayed buffer by buffer, and depending on the breadth of the rows in the data flow, a buffer may include more or less rows than other buffers in the information display.You may copy data to the clipboard, clear all data from the table, reconfigure the data viewer, continue the flow of data, and detach or attach the data viewer from the table using the Data Viewer dialog box.

To add a data viewer

  • Add a Data Viewer to a Data Flow

Row Counts

  • SSIS Designer displays the number of rows that have traveled through a path on the design surface of the Data Flow tab adjacent to the path, which is a useful indicator.
  • While the data is moving through the pipeline, the number is updated on a periodic basis.
  • In addition, you can include a Row Count transformation in the data flow to save the final row count in a variable when it is completed.
  • For further information, check the Row Count Transformation section of this manual.

Progress Reporting

  • Each data flow component on the design surface of the Data Flow tab is shown in a different color depending on the state of the package when it is being run by SSIS Designer when it is being run.
  • When a component begins to execute its function, it changes color from its default state of no color to yellow, and when it completes its function properly, it changes color to green.
  • The failure of a component is indicated by the color red.
  • The color-coding scheme is described in the following table.
Color Description
No color Waiting to be called by the data flow engine.
Yellow Performing a transformation, extracting data, or loading data.
Green Ran successfully.
red Ran with errors.

Analysis of Data Flow

  • A database view in the catalog.execution data statistics SSISDB database may be used to examine the data flow of packaged data. A row is displayed in this view for each instance in which a data flow component transfers data to a downstream component. With this information, it is possible to develop a more in-depth knowledge of the rows that are delivered to each of the components. Note A logging level of Verbose must be configured in order for the catalog.execution data statistics view to be able to capture the information it needs. The next example shows the number of rows that are transferred between the components of a package in the following format. SSISDB should be used. catalog.execution data statistics: select package name, task name, source component name, destination component name, rows sent from catalog.execution data statistics where execution id = 132 and order by source component name and destination component name are both true With the help of the following example, you can figure out how many rows per millisecond are transmitted by each component during a certain run. total rows – the sum of all the rows sent by the component
  • wall clock time ms – the total elapsed execution time, measured in milliseconds for each component
  • num rows per millisecond – the number of rows sent by each component per millisecond
  • and num rows per millisecond – the number of rows sent by each component per millisecond.
  • The HAVING clause is intended to avoid a divide-by-zero mistake from occurring during the calculating process.
  • Use SSISDB to pick source component name, destination component name, sum(rows sent) as total rows, DATEDIFF(ms,min(created time),max(created time)) as wall clock time ms, and ((0.0+sum(rows sent)) / (datediff(ms,min(created time),max(created time))) as from and from.
  • Use SSISDB the case where execution id = 132 and the destination component name has (datediff(ms,min(),max())) > 0 and the order by source component name is desc, the case where execution id = 132 and the desc is the case

Configure an Error Output in a Data Flow Component

  • The error outputs of many data flow components are supported, and depending on the component, SSIS Designer gives a variety of configuration options for the error output.
  • The columns of an error output can also be configured in addition to customizing the error output itself, as seen below.
  • This involves customizing the ErrorCode and ErrorColumn columns, which were introduced by the component, among other things.

Configuring an Error Output

  • You have two options when it comes to configuring an error output: Use the Configure Error Output dialog box to configure the error output. It is possible to configure an error output on any data flow component that supports an error output by using this dialog box.
  • Make use of the component’s editor dialog box to make changes. Some components allow you to specify error outputs directly from their editor dialog box, while others need you to use a third-party tool. You cannot, however, configure error outputs from the editor dialog box for the ADO NET source, the Import Column transformation, the OLE DB Command transformation, or the SQL Server Compact destination.

The processes that follow demonstrate how to configure error outputs by using these dialog boxes to the right.

To configure an error output using the Configure Error Output dialog box

  1. Start by opening the Integration Services project that contains the package you wish to use in SQL Server Data Tools (SSDT).
  2. Double-click the package in Solution Explorer to bring it up for inspection.
  3. Select the Data Flow tab from the SSIS Designer toolbar.
  4. Draw a red arrow to indicate that an error output has been generated and should be sent to another component in the data flow.
  5. Configure error output by selecting an action in the Error and Truncation columns for each column in the component input in the Configure Error Output dialog box.
  6. In order to save the newly revised package, select Save Selected Items from the File menu.

To add an error output using the editor dialog box for the component

  1. Start by opening the Integration Services project that contains the package you wish to use in SQL Server Data Tools (SSDT).
  2. Double-click the package in Solution Explorer to bring it up for inspection.
  3. Select the Data Flow tab from the SSIS Designer toolbar.
  4. Make a double-click on the data flow components in which you wish to configure an error output and then perform one of the following actions, depending on the component: Then, select Configure Error Output
  5. then, select Error Output.
  1. Make sure that the Error option is selected for each column. Make sure that the Truncation option is selected for each column.
  2. In order to save the newly revised package, select Save Selected Items from the File menu.

Configuring Error Output Columns

The Input and Output Properties tab of the Advanced Editor dialog box must be used in order to set the error output columns for the Advanced Editor.

To configure error output columns

  1. Start by opening the Integration Services project that contains the package you wish to use in SQL Server Data Tools (SSDT).
  2. Double-click the package in Solution Explorer to bring it up for inspection.
  3. Select the Data Flow tab from the SSIS Designer toolbar.
  4. Show the Advanced Editor by selecting Show Advanced Editor from the context menu of the component whose error output columns you wish to modify.
  5. Click the Input and Output Properties tab, then click Error Output and then expand Output Columns to see the options.
  6. Select a column and make changes to its characteristics by clicking on it. Note The columns in the component input, the ErrorCode and ErrorColumn columns introduced by earlier error outputs, and the ErrorCode and ErrorColumn columns added by this component are all included in the list of columns.
  7. To proceed, click OK.
  8. In order to save the newly revised package, select Save Selected Items from the File menu.

Add a Data Viewer to a Data Flow

  • It is described in this article how to include and setup a data viewer in a data flow.
  • A data viewer is a program that displays data that is being sent between two data flow components.
  • Example: A data viewer can display the information that has been taken from a data source before the information is modified by a transformation in the data flow.
  • Using a route, components in a data flow can be connected by linking the output of one component to the input of another component.
  • It is necessary for a package to have a Data Flow job as well as at least two data flow components that are linked before you may put data viewers inside it.

To display the explanation of the mistake as well as the name of the column in which the error occurred, include a data viewer in the error output.In the default configuration, the error output contains simply numeric IDs for the error and the column.

To add a data viewer to a data flow

  1. Start by opening the Integration Services project that contains the package you wish to use in SQL Server Data Tools (SSDT).
  2. Double-click the package in Solution Explorer to bring it up for inspection.
  3. If the Control Flow tab is not already selected, choose it by clicking it.
  4. Select the Data Flow job to which you wish to connect a data viewer and then select the Data Flow tab from the drop-down menu.
  5. Right-click a path between two data flow components and select Edit from the context menu.
  6. On the General page, you may examine and update the path attributes that have been specified. Using the PathAnnotation drop-down list, for example, you may choose the annotation that shows next to the path.
  7. On the Metadata page, you can see the metadata for each column and copy the metadata to the Clipboard.
  8. On the Data Viewer page, select Enable data viewer from the drop-down menu.
  9. Select the columns that you wish to see in the data viewer from the Columns to show area of the data viewer. By default, all of the available columns are selected and present

Leave a Reply

Your email address will not be published.