DTS to SSIS Migration

  • 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

  • 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

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Here is an inexpensive book to get you on your way with scripting using SSIS.

  • Thanks everyone for your helpful advices.

    Munnabhai

  • 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