October 22, 2007 at 3:12 pm
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?
October 25, 2007 at 2:10 pm
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 ?
October 25, 2007 at 11:52 pm
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.
Catherine Eibner
cybner.com.au
October 26, 2007 at 12:31 am
Catherine Eibner is correct..
October 26, 2007 at 2:49 am
well thats good news! I hate to give advice that is incorrect! 😛
Catherine Eibner
cybner.com.au
October 26, 2007 at 11:59 am
Yes,
I used script task and it worked. Never use ActiveX anymore...
October 26, 2007 at 12:07 pm
Vika:
Did you copy/paste from ActiveX or did you need to rewrite?
How long did it tak eyou to convert it?
Thanx.
Beth
October 26, 2007 at 2:16 pm
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
October 26, 2007 at 2:29 pm
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
December 23, 2008 at 11:51 am
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
December 23, 2008 at 2:13 pm
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.
December 23, 2008 at 7:23 pm
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
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 23, 2008 at 7:36 pm
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
December 23, 2008 at 7:46 pm
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
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 2, 2009 at 7:09 am
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