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.

2 comments:

  1. Recently, I have commenced a blog the info you give on this site has encouraged and benefited me hugely. Thanks for all of your time & work. เซียน ส เต็ ป 69

    ReplyDelete
  2. Need to do quality papers, but no idea how to do it? Then you have the opportunity to use top essay writing services that will do everything for you.

    ReplyDelete