SQL SERVER: SSIS - ActiveX Task

Today I am going to explain SSIS - ActiveX Task. Here I am just explain that how to write VB Script in this task, which will execute at run time. This ActiveX Task is mostly used when we  move SQL 2000 DTS package to SSIS. As I found that many developers had used ActiveX with DTS packages in SQL SERVER 2000. The ActiveX Script task provides a way to continue to use custom code that was developed using ActiveX script, until such scripts can be upgraded to use the more advanced features provided by the Script task. Let's take an example to easily understand how to use ActiveX Task with SSIS. 1. Select and Drag, ActiveX Script Task, from Container Flow Items to designer surface.   2. Now we need to write a script which will execute when SSIS package loads. To write a script, Right click on  ActiveX Script Task, which we dragged to Design surface. Click on "Edit.", you will get page as: 3. SSIS ActiveX Script Task - General. Here we need to assign unique name to this task and also we can specify brief description, so we will get idea why we design this task. 4. SSIS ActiveX Script Task - Script Task: This is main section where we need to write script. Language: We need to specify which script language we are going to use for this task, either VB Script, JScript etc..Here we are going to use VB Script language for this demo Script: Here we need to write a script. Click on ".." button located at right side. It will open a dialog to write a script, as shown in figure:   Let's write VB Script code inside to test this task. I write this script: function Main() Dim Stuff, myFSO, WriteStuff, dateStamp dateStamp = Date() Stuff = "SSIS Text ActiveX Task" Set myFSO = CreateObject("Scripting.FileSystemObject") Set WriteStuff=myFSO.OpenTextFile("D:\SQLYoga\SSIS ActiveX Task.txt", 8, True) WriteStuff.WriteLine(Stuff) WriteStuff.Close SET WriteStuff = NOTHING SET myFSO = NOTHING End function Save it and click on Parse, which will parse this script and let us know if there is any problem in this script. If It is on then its allowed to proceed with the next step. Expression: Use the Expressions page to edit property expressions and to access the Property Expressions Editor and Property Expression Builder dialog boxes. That's it. Let's run task, by right click on task and click on Execute Task, as shown in following figure. You can either Execute Package by right click on Package name, from Solution Explorer.   Once you run it and if logged in user has write access to the given path, SSIS package will create file at that place. Now go to that given path and see file is created or appended with content that we want to write. That's simple to configure and use SSIS ActiveX Script Task within BIDS. Let me know if you have any questions in designing SSIS Package