Showing posts with label ActiveX Task. Show all posts
Showing posts with label ActiveX Task. Show all posts

October 31, 2009

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.
 1 SSIS ActiveX Script Task
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:

2 SSIS ActiveX Script Task
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:

3 SSIS ActiveX Script Task 
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.

 4 SSIS ActiveX Script Task

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