December 23, 2008 at 7:11 pm
Hi,
I'm a newbie to ActiveX and trying to convert my DTS packages to SSIS that use ActiveX Script. I thought I can copy and paste ActiveX code from DTS to ActiveX Script Task in SSIS and then create other tasks but seems like I was clearly wrong .
DTS packages only contains 2 Execute Tasks, 1 ActiveX Script Task and one connection.
Here is what the DTS is doing;
It truncates table, Generates SQL using ActiveX script Task, and Runs Extract.
When I copy and paste the ActiveX script in SSIS ActiveX Script Task and run the SSIS package, I get an error "Function not found"....
Can anyone please help me in solving this mystery.... I will greatly appreciate it. I have included the ActiveX Script below.
Thanks in advance for all your help.
'**********************************************************************
' 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_swaption_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 7:33 pm
It's no mystery - you have to rewrite your code using .NET syntax. I'm afraid that you have some learning to do, as I doubt that people here have the time to do it all for you.
The .NET environment is pleasanter to work in and more powerful, but you do have a bit of a learning curve as you start to use it.
Here is a sample of some code, just to give you an idea of the scale of the difference:
Public Class Script1
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
'If the import file is empty, return a fail, otherwise success
Dim strConn As String, objConn As New OleDb.OleDbConnection
Dim strFilepath As String, strFilename As String, File As FileIO.FileSystem
Dim SelectCommand As String, objCmd As OleDbCommand
Dim objSourceReader As OleDbDataReader
Dim FieldsOK As Boolean
Try
'Set a progress message
Dts.Events.FireProgress("Contact Import", 0, 0, 0, "Commencing Import Validation", True)
'Does the input file exist? If not, set error message and exit job successfully.
If Not File.FileExists(Dts.Connections("CSV File").ConnectionString) Then
SetVariable("User::MessageText", "Input file empty or non-existent")
Dts.TaskResult = Dts.Results.Success
Exit Try
End If
If File.GetFileInfo(Dts.Connections("CSV File").ConnectionString).Length = 0 Then
SetVariable("User::MessageText", "Input file empty or non-existent")
Dts.TaskResult = Dts.Results.Success
Exit Try
End If
etc etc
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 8:06 pm
December 24, 2008 at 7:37 am
Thanks everyone for your helpful advices.
Munnabhai
December 26, 2008 at 5:59 am
One of the good things about SSIS is that you do not need to use script tasks for everything that you did in DTS. When I first was rewriting DTS packages as SSIS, I had several that had ActiveX in them. All you need to be able to do with the ActiveX is be able to understand what the code is doing. Often times, you will be able to find a SSIS transformation that will do the same job. A Conditional split is a good example. If you can't find a SSIS task to do the job, you will have to tackle the SSIS script task and learn some .net.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply