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

 

18 comments:

  1. Dear Sir,
    What is future of a SSIS package developer.

    I am a software developer working with Micro Soft Technologies Like ASP.Net, C# and other related technologies and I am also work with SSIS and SSRS. I want to build my career in SSIS, and SQL Server database so I want to know that this is a good field or I need to rethink about it. I am in very confusion can u help me because you have a very good experience in Sql server and SSIS, I have read your notes on the site related to SSIA. Please, Can u give me some Guidance?

    ReplyDelete
  2. Hi,

    SSIS future is very bright.

    Now a days, Every Business need to use their data for decision making.
    SSIS is platform where we can integrate data from different sources and convert data to Information.

    SSIS can present data in various format, so user can understand it easily.

    SSIS is ETL Tool, ETL means "Extract, Transform, and Load", where you can convert data to information.

    That's why I plan to explain each SSIS task by this blog.

    You can try each task and you come to know how is future of this.

    Tejas
    SQLYoga.com

    ReplyDelete
    Replies
    1. Hi I got this error when i execute SSIS package in IST server

      An error occurred while adding the managed SSIS type library to the script host.Verify that the DTS 2000 runtime is installed.

      Can you help me.

      Delete
  3. I have a query in SSIS can you help me.
    i have a T-SQL query
    With

    DefectUnit AS(SELECT SerialNo,MAX(GUTCDateTime)as LastGUTCTime from GPSTrack group by SerialNO having convert(datetime,CURRENT_TIMESTAMP,101)
    -

    MAX(convert(datetime,GUTCDateTime,101))>1)select SerialNo,LastGUTCTime from DefectUnit order by SerialNo ASC


    i need to send a email with the result dataset of this query in SSIS.

    Can you help me how i will do that?

    how we attach the result of this query in email?

    Please help me?

    ReplyDelete
  4. I get an error saying function cannot be found?

    ReplyDelete
  5. hey rake u just need to use send email task to send the mail ..... if you need more information about that let me know.

    Thanks
    Ram

    ReplyDelete
  6. Hi Tejas

    I have followed the given steps but if i execute it it says function not found


    TITLE: Package Validation Error
    ------------------------------

    Package Validation Error

    ------------------------------
    ADDITIONAL INFORMATION:

    Error at ActiveX Script Task [ActiveX Script Task]: Function not found.

    Error at ActiveX Script Task: There were errors during task validation.

    (Microsoft.DataTransformationServices.VsIntegration)

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    ReplyDelete
  7. Hi vijay,

    If you have error "Function not found", means that you haven't write name of function in the field Entry method, for instance if you put code below in script field:

    Function Main()

    {some code inside}

    End function

    you have to put in field Entry method name "Main".


    Regards,
    Pawel

    ReplyDelete
  8. Hi Tejas,

    Can you show an example on script component transformation?

    ReplyDelete
  9. Hii
    I have got an error
    "An error occurred while adding the managed ssis type library to the script host verify that the DTS 2000 run time is installed"
    what might be the possible reason
    can any body help

    ReplyDelete
  10. Thank you guys I found this very helpful!!! Pawel thats a good tip.

    ReplyDelete
  11. Thanks, It is very easy to understand and helpful

    ReplyDelete
  12. Getting error when i ty to parse the code...

    "An error occurred while adding the managed SSIS type library to the script host. Verify that the DTS 2000 runtime is installed"

    Please advice

    Thanks,
    Raghu

    ReplyDelete
  13. Getting error when i ty to parse the code...

    "An error occurred while adding the managed SSIS type library to the script host. Verify that the DTS 2000 runtime is installed"

    Please advice

    Thanks,
    Raghu

    ReplyDelete
  14. This comment has been removed by a blog administrator.

    ReplyDelete
  15. Hello Tejas,

    I see that this code was copied from a free ebook of Karthikeyan, www.f5debug.com. Neither you or karthikeyan explained any thing about error handling, like what is the work around for a specific error..

    Good Try...
    Bairam

    ReplyDelete
  16. Hi I got this error when I am execute SSIS package in Production
    "An error occurred while adding the managed SSIS type library to the script host. Verify that the DTS 2000 runtime is installed"

    can any one help me...

    ReplyDelete
  17. I got this error when I execute SSIS package in IST server
    "An error occurred while adding the managed SSIS type library to the script host. Verify that the DTS 2000 runtime is installed"

    can any one help.
    Santosh

    ReplyDelete