We often have to debug Script task which we have used in ourSSIS Package. We use MessageBox.show to see the value of variables and thenhave to hit Ok to see the next value. Instead of introducing Message Boxes fordebugging we can use Break Point and debug step by step so we do not have toworry about removing the Message Boxes or commenting them after debug. In case we forget any of them that can becomethe reason of package failure when we will be running the package from SQLServer Agent.
Let’s jump into an example, how to debug Script Task step bystep by using Break Point.
In this sample Package, I am reading the file names from afolder. I have created a Variable FolderPath in SSIS Package and passed thisvariable to Script Task. Script Task uses this folder path and read the filenames.
The SSIS Script Task gives an option to implement functions that are not available or possible in the SSIS toolbox (both in built-in Tasks and transformations). The SSIS script task utilizes the Microsoft VSTA (Visual Studio Tools for Applications) as the code environment in which you can write the C# or VB Script. Yes also in the data flow task - script component, clicking on the edit script button results in visual studio not responding. This used to work a couple of weeks ago, only thing I can think of is that I upgraded to Windows 8.1 from Windows 8.0 earlier this week. Script Task Plus is an SSIS control flow task and it is an extension of the standard Microsoft Script Task. The standard script task doesn't have the ability to reuse script code. If the SSIS developer wants to reuse the same script in a different task, he has to copy and paste the code.
Fig 1: FolderPath Variable holding the Folder path
First, I want to make sure the Script task is readingthe correct value of FolderPath variable and then want to know what file namesare 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());
foreach (FileInfo file in Files)
Ssis Script Task Variables
string variable = file.Name;
Fig 3: Break Point in Script Task
Ssis Upgrade 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.
Ssis Script Task Examples
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.
Sometimes you need to automate the upload of a file to an FTP or SFTP. Thought I would share the way that I use the SFTP task in SSIS to upload files.
Cheat game pes 2016 ps2. Download game total conquest mod apk offline putra adam. Firstly create the variables to use in the SFTP task. Variables are an easier way to manage things without going into the task, so you can change the password in the variable and not have to open up the SFTP task.
Next you need to complete the SFTP Task Editor.
What you need to take note of above that is very important is that you must remember to place the Remote File name. So this would be the name of the file you are uploading. Once the above is filled in you can click ok and your SFTP task should be ready to go without any red cross on it.
Example:
My ETL consists of generating a file to upload, then uploading the file and then sending a mail to notify users the files have been uploaded.
Hope this helps you with using the SFTP task in SSIS.