SSIS Tutorials : Debugging Data Flow

BIDS tool has option to debug the Data Flow control. In normal cases you can see row count while you execute the package in debug mode. And you can see the execution details in progress (Execution Result) tab of the package designer. Apart from this you can see data which is being manipulated in you data task using data viewer. Let's see how to use data viewer in Data Flow task.

Step 1: Define your Data Flow Task. See below image for sample data fow task.

Step 2: Right Click on  Data Flow Path editor as show in below image. And the click on Edit or Data Viewer.

Step 3: Click in on Add  as show in above image. to add data viewer. It will open sceen as shown below to select the type of data viewer you want. For Grid view just click on ok and Ok. It will add one data viewer to your data flow.

Step 4: After adding data viewer you will see small viewer icon along with data flow path.

Step 5: Now run your package to see the data viewer in action. Once you run the package, it will stop at data viewer and will show the records. You will see tree buttons on data viewer. Play, Detach, Copy Data. Click on play to continue exectution with data viewer attached to data flow path. Click on Detach to detach data viewer from execution flow. and Copy Data to copy data in clipboard.

This was short explanation to use data viewer to debug the Data Flow task.


Data viewer can display data in different format. It can show data in grid, histogram, scatter plot and column chart.

Data Grid : It allows column selection, which can later appear as column in tabular data display in debug mode.

Histogram : It only works for numeric column and shows data distribution as histogram.

Scatter plot : This one also only works with numeric data. You can select numberic data column for different axis for the graph.

Chart format: This chart show the occurrence count of discrete values in the selected column.











MS SQL Server Integration Services | SSIS | SSIS Tutorials | Learn SSIS | Beginers Guid to SSIS | Microfot BI Tutorials | Learning SSIS | How to debug package | Debugging Packages | Debugging Data Flow | How to Debug SSIS Package

SSIS Tutorials : How to Debug SSIS Package

Debugging SSIS package is possible using Business Intelligence Development Studio. There are way of debugging  the different component of package. Data Flow, Control Flow and Script Task can be debugged using different technique.


Debugging Control Flow: Control flow can be debugged using breakpoints, debugging windows and progress reporting.


Debugging Data Flow: Data Flow can be debugged using data viewer and progress reporting.


Debugging Script Task: Script Task can be debugged using break point in script code. It is same as debugging any .Net code debug. 




Apart from above debugging methods you can use different event to write log or send message to know the status of the package execution. Above techniques can only be used while you are executing package using Business Intelligence Development Studio. But if you want to do it on production server (means package which are running out side of development studio), then only way is to generate proper log of you package using package event or adding extra task item in your control flow or data flow.




Next we will see how to use above debugging technique in details in coming blogs. 






MS SQL Server Integration Services | SSIS | SSIS Tutorials | Learn SSIS | Beginers Guid to SSIS | Microfot BI Tutorials | Learning SSIS | How to debug package | Debugging Packages

SSIS Tutorials : First Package

Start SQL Server Business Intelligence Development Studio

• Create New Project of type Integration Services Project

• You will see default package designer opened as shown below.

BNow your package is ready to run. You run your package using F5 button or using Start Debugging menu item. As this package does not contain anything but still you can run it. Now let see the main components of Package designer.

Any package designer has below main section.

• Control Flow
• Data Flow
• Event Handlers
• Package Explorer
• Execution Result or Progress : (This will only apper if you run the package at least once.

Now lets see the purpose of each sections.

Control Flow: This section contains the main exection work flow of the package. You can define you package execution steps and sequences here. It contains Tasks and Constraints.

Data Flow: This section contains details of Data Flow task like Data Flow Source, Data Flow Transformationa and Data Flow Destination. In short you can read data from source system, apply some trasformation and write back to destination system.

Event Handlers: This section is used to defined the task to be perrformed in case of error or some event like Task Start, Task End, Variable Chaned etc.

Package Explorer: Package explorer gives you the opportunity to see pakage as tree view. It shows all details about package which you see in all other sections.

Execution Result or Progress : This section only appers if you run the packages at least once. Progress section appears while package is running and it changed to Execution Rerult when you stop exection of the package. This section show the details and status of each Task execution.

Apart from above section Package has some more section which are important.

Connection Managers: This section appears only while you are in Control Flow or Event Handler Section. This section shows all connection available to package.

Vaiables: This section shows variable defined at package level or Task level. You can see veriable by Clicking on Variables menu on context menu of Control Flow designer or any Control Flow Item. You can also see variable in Package Explorer section under Variables node.

Aprat from above you can see Property window and Toolbox as you see with any Visual Studio application.


Next we will see how to use different section and details about different Control Flow Items and Data Flow item.




MS SQL Server Integration Services | SSIS | SSIS Tutorials | Learn SSIS | Beginers Guid to SSIS | Microfot BI Tutorials | Learning SSIS | SQL Server Integration Service (SSIS) Package design

Journey of SQL Server

Today we all know and use Microsoft SQL Server 2008R2, latest release from Microsoft. But some of us don't know about the history of SQL server. So I thought let start from it. 


Here is the major landmark in the life of SQL Server

• 1987: Sybase Corporation released its first relational database system under the name of Sybase SQL Server for UNIX.
• 1988: Sybase, Microsoft and Ashton-Tate co-developed SQL sever for OS/2 for PC.
• 1993: Till this year Microsoft and Sybase has co-developed and release SQL Server 4.21, then co-development ended. Microsoft released SQL server 4.2.1 under the code name SQLNT for Windows NT.
• 1995: SQL Server 6.0, first Microsoft only version released under the code name SQL95.
• 1996: SQL server 6.5 released under the code name Hydra. Enterprise Manager (StarFighter I) and SQL Server Agent (Star Fighter II) born with this version release.
• 1997: SQL Server 7.0 released under the code name Sphinx. This version has included Replication, OLAP services, Full Text Search.
• 2000: SQL Server 2000 released with code of Shiloh. It came with features like Indexed Views, Distributed Partitioned Views, Clustering, XML Support, ser Defined function, Reporting Services, Data Mining Tool etc.
• 2003: SQL Server 2000 64 bit version released. Code name Liberty.
• 2005: SQL Server 2005 released under the code name of Yukon. Which came with lots of new feature
• 2008: SQL Server 2008 released under the code name of Katmai. See this for details.
• 2010: SQL Server 2008 R2 released under the code name of Kilimanjaro. SQL Server Azure released under the code name of Matrix.
• Next coming version is under the code name of denali.

Hello World

Since very long I was thinking to write my blog but I could not start writing probably because of my laziness. Finally today I took a wise decision to write my own blog. I am not sure how long I will continue writing but now I will try to write something on regular basis. If you have come this blog then please do write comment, so that I will motivation to continue writing. You might be thinking that why only today I took this decision. Even I don't know why. So like me don't think and enjoy(!!!) reading my blog.


I think I said enough about reason for starting blog. Now let me tell you what I am gonna write in my blog. From the name (SQL31) you might have guessed that it is something about SQL. You are right, I have some knowledge about SQL which I want to share here. If it is about SQL then what is 31? There is no magic about it. It is my birth day. I was typing some name for my blog and could not find good word so I said let me try this and it was available. That's it. It is SQL31. Good name! Isn't it?


No let me tell you about my self. I am a SQL Server DBA/Developer since 2003. I have worked on SQL server 2000, 2005, 2008, 2008R2, DTS, SSIS, SSAS, SSRS. Apart from SQL server I also have experience of .Net, C#, VB.NET, ASP.Net, ASP MVC, Silverlight, WPF, WCF, XML, HTML, DHTML, Javascript.


That's all for this post. Enjoy the following posts.