- C# Scripts
- DWH INTERVIEW QUESTIONS
- MS Dynamics AX 2012 R2 Video Tutorial
- Project / Work Support
- SQL SERVER DBA INTERVIEW QUESTIONS
- SQL SERVER DBA Video Tutorial
- SQL Server / TSQL Tutorial
- SQL Server 2016
- SQL Server Scripts
- SSIS INTERVIEW QUESTIONS
- SSIS Video Tutorial
- SSRS INTERVIEW QUESTIONS
- SSRS Video Tutorial
- TSQL INTERVIEW QUESTIONS
- Team Foundation Server 2013 Video Tutorial
- Team Foundation Server 2015 Video Tutorial
- Windows 10
- Windows Server 2012 R2 Installation Videos
SSIS - How to Debug Script Task Step by Step In SSIS
We often have to debug Script task which we have used in our SSIS Package. We use MessageBox.show to see the value of variables and then have to hit Ok to see the next value. Instead of introducing Message Boxes for debugging we can use Break Point and debug step by step so we do not have to worry about removing the Message Boxes or commenting them after debug. In case we forget any of them that can become the reason of package failure when we will be running the package from SQL Server Agent.
Let’s jump into an example, how to debug Script Task step by step by using Break Point.
In this sample Package, I am reading the file names from a folder. I have created a Variable FolderPath in SSIS Package and passed this variable to Script Task. Script Task uses this folder path and read the file names.
Fig 1: FolderPath Variable holding the Folder path
First, I want to make sure the Script task is reading the correct value of FolderPath variable and then want to know what file names are read from Folder?
Drag Script Task to Control Flow Surface, Open it by double clicking and then provide the FolderPath variable to it and Click on Edit Script. I am using C# for scripting, you can use VB.Net if you like.
Fig 2: Provide the Variable FolderPath to Script Task
Add using System.IO under namespaces and below code in Main. The code is going to read the .txt files from the Folder which is provided by FolderPath variable.
DirectoryInfo dir = new DirectoryInfo(Dts.Variables["User::FolderPath"].Value.ToString());
FileInfo Files = dir.GetFiles("*.txt");
foreach (FileInfo file in Files)
string variable = file.Name;
Fig 3: Break Point in Script Task
Hit the Save button and close the Script Task Edit window. You will see that the Script Task has Red Circle that means Break Point is added to the Script Task.
Fig 4: Script Task After Break Point added
Let's execute our SSIS Package and see how we can debug the Script Task and see the values of variables etc. As we can see the Script Task is stopped at Break Point.
Hit F10 or F11 on your Keyboard to proceed
Fig 5: Execution stopped at Break Point
Once you hit F10 or F11 , the next step will be highlighted and executed as shown below.
Fig 6: Debug step by step by using F10 or F11
As you can see in the fig 6, we can click on the line and it will show us the value. you can unpin or pin to see or hide the values while debugging.
Once the Script Task execution is completed, the package will return to run next task in Control Flow Surface. If all looks fine to you. Open the Script Task and Click on the Break Point to remove it.