ActiveX Script Error: Function not found

  • This is my first attempt to create SSIS package. I added ActiveX Script Task just to test

    '****************************************************************

    ' Visual Basic ActiveX Script

    '****************************************************************

    Function Main()

    End Function

    And I get an error when I try to execute task:

    Package Validation Error

    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)

    What function is not found? Why there is an error if there almost nothing in the ActiveX script?

  • I, too, am getting the same error as I try to execute my first SSIS package.

    All I did was copy & paste the ActiveX from the DTS to an ActiveX Script Task. It even parses without errors... Any thoughts on how to correct it ?

  • Active x script tasks are provided for backwards compatibility only. In fact I believe they are being deprecated in future releases.

    Try using the Script task instead. It provides a strongly typed, visual studio coding environment with debugging - so you can set breakpoints and see what is happenning when you run your package.


    Kindest Regards,

    Catherine Eibner
    cybner.com.au

  • Catherine Eibner is correct..

  • well thats good news! I hate to give advice that is incorrect! 😛


    Kindest Regards,

    Catherine Eibner
    cybner.com.au

  • Yes,

    I used script task and it worked. Never use ActiveX anymore...

  • Vika:

    Did you copy/paste from ActiveX or did you need to rewrite?

    How long did it tak eyou to convert it?

    Thanx.

    Beth

  • Hi Beth,

    You cannot convert, I had to rewrite everything. It took me two days with testing to finish it and move from SQL Server 2000 to SQL Server 2005. But then I never wrote SSIS package in my live, I had to do some reading.

    Here's how to write SSIS with Script Task for Server 2005

    1. Programs, MS SQL Server 2005, SQL Server Business Intelligence Developmental Studio

    File-New-Project, Project Type Business Intelligence Project, Installed template – Integration Services Project.

    2. Drag Script Task from Toolbox.

    Right click script task, Edit, click Script, click Design Script.

    Copy paste VB.NET script (see below), edit your DB server name and smtp server name, save, close, OK.

    3. Right click Script Task, click Execute task, see it it works.

    In the solution explorer in the right panel right click project name, properties, Deployment Utility, set Create Deployment Utility to True

    4. Build project. It will create DTSDeploymentManifest and copies the project along with DTSInstall.exe to bin/Deployment

    5. Copy this project to DB server where you want to install it. Go to project/bin/Deployment, click exe , or DTSDeploymentManifest (I don’t remember exactly which one) and wizard will help to install the package, just follow instructions. Install on SQL server and use Windows authentication.

    6. In the SQL Server Management Studio connect to yourDatabase Engine, go to SQL Server Agent, jobs, new job

    7. Give job a name; steps, New, Type SQL Server Integration Services Package. Package Source SQL Server, input server name and use Windows Authentication.

    Click Schedule, New, set your schedule.

    This is a script task VB.NET script that checks database emails and sends email notification several times a day. I had to re-write it from ActiveX in DTS Packages into VB.NET in SSIS:

    ' Microsoft SQL Server Integration Services Script Task

    ' Write scripts using Microsoft Visual Basic

    ' The ScriptMain class is the entry point of the Script Task.

    Imports System

    Imports System.Data

    Imports System.Math

    Imports System.Net

    Imports System.Web

    Imports System.Net.Mail

    Imports Microsoft.SqlServer.Dts.Runtime

    Public Class ScriptMain

    Function getConnection() As Data.SqlClient.SqlConnection

    Dim connection As New Data.SqlClient.SqlConnection("Data Source=myServerName;Initial Catalog=myDBName;Persist Security Info=True;User ID=myUserName;Password=myPassword")

    Return connection

    End Function

    Public Sub Main()

    Dim connection As New Data.SqlClient.SqlConnection

    connection = getConnection()

    Dim queryString As String = ""

    Dim result(15) As String

    Dim count As Integer

    Dim mailFrom As String = "myEmail"

    Dim subject As String = ""

    Dim mailBCC As String = ""

    Dim mailTo As String = ""

    Dim mailCC As String = ""

    Dim body As String = ""

    queryString &= " Select email, first, last, orderedID from.. "

    Try

    Dim query As New Data.SqlClient.SqlCommand(queryString, connection)

    connection.Open()

    Dim queryResult As Data.SqlClient.SqlDataReader = query.ExecuteReader()

    Do While queryResult.Read()

    For count = 0 To 3

    result(count) = CStr(queryResult.GetValue(count))

    Next count

    mailTo = result(0)

    body &= " Something here…"

    SendMailMessage(mailFrom, mailTo, mailBCC, mailCC, subject, body)

    mailTo = ""

    mailCC = ""

    body = ""

    Loop

    Catch ex As Exception

    Finally

    connection.Close()

    End Try

    Dts.TaskResult = Dts.Results.Success

    End Sub

    Public Shared Sub SendMailMessage(ByVal from As String, ByVal recepient As String, ByVal bcc As String, ByVal cc As String, ByVal subject As String, ByVal body As String)

    Dim smtpClient As New SmtpClient("mySMTPServer")

    Dim mailMessage As New MailMessage()

    mailMessage.From = New MailAddress(from)

    mailMessage.To.Add(New MailAddress(recepient))

    If Not String.IsNullOrEmpty(bcc) Then

    mailMessage.Bcc.Add(New MailAddress(bcc))

    End If

    If Not String.IsNullOrEmpty(cc) Then

    mailMessage.CC.Add(New MailAddress(cc))

    End If

    mailMessage.Subject = subject

    mailMessage.Body = body

    mailMessage.IsBodyHtml = True

    mailMessage.Priority = MailPriority.Normal

    smtpClient.Send(mailMessage)

    End Sub

    End Class

  • WOW ! What a detailed response!

    I'm looking forward to giving this a go.

    I'll be back with questions if I run in to problems.

    Thanx much for the reply !

    Beth

  • Hi Vika,

    I'm trying to convert ActiveX Script listed below to Script Task in SQL 2005 but get the same error. Can you please show how would you convert this ActiveX Script to VB.Net?

    Thanks

    '**********************************************************************

    ' Visual Basic ActiveX Script

    '************************************************************************

    Function Main()

    'Change the value of the file name on the connection

    Set oPackage = DTSGlobalVariables.parent

    Dim sSQLString

    Dim sDate

    Dim dDate

    Dim strResult

    dDate = GetRevalDate

    sDate = DateToName(dDate)

    'SQL String

    sSQLString = "exec st_extract_populate_vega_work_table " & Chr(13) & _

    "@RevalDate = '" & sDate & "'"

    DTSGlobalVariables.Parent.Tasks("DTSTask_DTSExecuteSQLTask_2").CustomTask.SQLStatement = sSQLString

    Main = DTSTaskExecResult_Success

    End Function

    Function GetRevalDate()

    Dim dDate

    dDate = date

    If Weekday(dDate) = 1 Then

    GetRevalDate = dDate + 1

    Else If Weekday(dDate) = 7 Then

    GetRevalDate = dDate + 2

    Else

    GetRevalDate = dDate

    End If

    End If

    End Function

    Function DateToName(dDate)

    'Create a name based on a date

    Dim sYear

    Dim sMonth

    Dim sDay

    sYear = Year(dDate)

    If Month(dDate) < 10 Then

    sMonth = "0" & Month(dDate)

    Else

    sMonth = Month(dDate)

    End If

    If Day(dDate) < 10 Then

    sDay = "0" & Day(dDate)

    Else

    sDay = Day(dDate)

    End If

    DateToName = sYear & sMonth & sDay

    End Function

  • I can't speak for how to translate an Active X script to .net. But I ran into this issue when I was first moving out of DTS to SSIS and had to use the Active X script task to work. There is a property called entry method that does not get autopopulated. This is where you specifiy Main() or whatever the function is called. Took me days to figure this out. Hopefully, this is the case for you.

    J.D.

  • You need to rewrite your VBScript code in VB.NET - the syntax and commands are quite different. I'm afraid that cutting and pasting just won't work.

    Phil

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Yes Phil,

    Certainly it hasn't work at all. But I'm wondering if there is an ActiveX task in SSIS for backward compatibility, why it would throw an error. Error like "Function can not be found...."

    Can I perform the same thing what ActiveX Script is doing using some SSIS tasks? What would the Tasks I will need to use if it's possible?

    Thanks

    Munna Bhai

  • I am afraid that I do not know about your error message.

    But I have never found anything in an ActiveX task that cannot be accomplished in a script task and I cannot imagine doing so - though the method may be quite different.

    Phil

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Your ActiveX Script appears to be identical to another post where I provided a solution earlier this week: http://www.sqlservercentral.com/Forums/Topic626389-148-1.aspx.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply