SQL 2000 Transformation Script to SQL 2005 Data Flow Task?

Posted on 2007-10-11
Last Modified: 2008-09-08
SQL Server 2005 from SQL Server 2000

I have a "transformation script" in SQL Server 2000 that I would like to convert/use in SQL Server 2005 but I'm not sure the best route to take or how to make it work for that matter.  The idea behind the script is to evaluate a column and based upon a value, multiple destination columns are derived from a single source column.  Again, I'm not sure how to do this in the "Data Flow Task" of SQL Server 2005, any ideas?  Here's my old SQL Server 2000 script:

'  Visual Basic Transformation Script

'  Copy each source column to the destination column
Function Main()

Select Case DTSSource("TranCode").Value

        Case "5"        '*****Process Batch Header Row
              DTSGlobalVariables("ServiceClass").Value = ""
                DTSGlobalVariables("CompanyName").Value = ""
                DTSGlobalVariables("CompanyDisc").Value = ""
                DTSGlobalVariables("CompanyId").Value = ""
                DTSGlobalVariables("StandardEntryClass").Value = ""
                DTSGlobalVariables("CompanyEntryDesc").Value = ""
                DTSGlobalVariables("CompanyDescDate").Value = ""
                DTSGlobalVariables("EffectiveEntryDate").Value = ""
                DTSGlobalVariables("SettlementDate").Value = ""
                DTSGlobalVariables("OriginatorStatus").Value = ""
                DTSGlobalVariables("OriginatingDFIId").Value = ""
                DTSGlobalVariables("BatchNumber").Value = ""

         If Mid(DTSSource("TranData"), 50, 3) = "TEL" OR Mid(DTSSource("TranData"), 50, 3) = "WEB" Then

                DTSGlobalVariables("ServiceClass").Value = Mid(DTSSource("TranData"), 1, 3)
                DTSGlobalVariables("CompanyName").Value = Mid(DTSSource("TranData"), 4, 16)
                DTSGlobalVariables("CompanyDisc").Value = Mid(DTSSource("TranData"), 20, 20)
                DTSGlobalVariables("CompanyId").Value = Mid(DTSSource("TranData"), 40, 10)
                DTSGlobalVariables("StandardEntryClass").Value = Mid(DTSSource("TranData"), 50, 3)
                DTSGlobalVariables("CompanyEntryDesc").Value = Mid(DTSSource("TranData"), 53, 10)
                DTSGlobalVariables("CompanyDescDate").Value = Mid(DTSSource("TranData"), 63, 6)
                DTSGlobalVariables("EffectiveEntryDate").Value = DateSerial(Cint(Mid(DTSSource("TranData"), 69, 2)) , Cint(Mid(DTSSource("TranData"), 71, 2)) , Cint(Mid(DTSSource("TranData"), 73, 2)) )
                DTSGlobalVariables("SettlementDate").Value = Mid(DTSSource("TranData"), 75, 3)
                DTSGlobalVariables("OriginatorStatus").Value = Mid(DTSSource("TranData"), 78, 1)
                DTSGlobalVariables("OriginatingDFIId").Value = Mid(DTSSource("TranData"), 79, 8)
                DTSGlobalVariables("BatchNumber").Value = Mid(DTSSource("TranData"), 87, 7)
            DTSGlobalVariables("StandardEntryClass").Value = "nonWebTel"
            'Main = DTSTransformStat_SkipRow
      End If

       Case "6"        '*****Process Entry Detail Record

      If DTSGlobalVariables("StandardEntryClass").Value <> "nonWebTel" Then

              DTSDestination("PostDate") = DTSGlobalVariables("EffectiveEntryDate").Value
                DTSDestination("TranType") = DTSGlobalVariables("StandardEntryClass").Value
                DTSDestination("CompanyName") = DTSGlobalVariables("CompanyName").Value
                DTSDestination("AccountNumber") = Mid(DTSSource("TranData"), 12, 17)
                DTSDestination("Amount") = FormatCurrency(Cdbl(Mid(DTSSource("TranData"), 29, 10))*.01,2)
                DTSDestination("IndividualName") = Mid(DTSSource("TranData"), 54, 22)
            DTSGlobalVariables("StandardEntryClass").Value = "nonWebTel"
            'Main = DTSTransformStat_SkipRow
      End If

Case Else
      'Main = DTSTransformStat_SkipRow
End Select

Main = DTSTransformStat_OK

End Function
Question by:irishm20
    LVL 18

    Expert Comment

    You can create a SSIS package using the Visual studio 2005.
    There is a task called 'Execute DTS 2000 package task' and that will run your current DTS.
    Or you can use a 'ActiveX script task' ... you can enter your ActiveX code in there right away.

    Hope this helps ...

    Author Comment

    I've tried to execute the DTS 2000 package and because of the layout of the package I'm having to re-create it in 2005 (Microsoft says I would probably have to if I had certain errors).  I'm not able to use the ActiveX Script task in the data flow piece otherwise that would work great.  Thanks for the attempt though.
    LVL 1

    Accepted Solution

    PAQed with no points refunded (of 250)

    EE Admin

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now