Post reply

SSIS equivalent of DTS Transform Data Task w/ ActiveX Script for the Transformation

  • December 28, 2006 at 3:04 pm

    #114988

    I'm sure someone has asked this question but I'm not having much luck finding it.  I converted a DTS package that had a Transform Data Task with a ActiveX Script for the Transformation.  It is a really simple script changing some string values from the input text file to decimal values for the output table.

    Part of the orginal code...

    Function Main()

     DTSDestination("colFiller1") = DTSSource("Col001")

     DTSDestination("colReceipt") = DTSSource("Col002") / 10

     DTSDestination("colDistrict") = DTSSource("Col003")

     DTSDestination("colTotalValue") = DTSSource("Col019")

     DTSDestination("colFirstHalfPaid") = DTSSource("Col021") / 100

     DTSDestination("colFiller3") = DTSSource("Col048")

     Main = DTSTransformStat_OK

    End Function

    The best I could come up with in the Data Flow of SSIS is...

    Text file -> Data Convertion -> Derived Column -> table

    The data convertion was needed to change the data types from string to decimal and the derived column was needed to do a calc on the final value (divide by 100).

    There has to be a better way

    Thanks 

  • December 28, 2006 at 3:46 pm

    #680391

    Check http://www.sqlis.com ?

    MohammedU
    Microsoft SQL Server MVP

  • December 29, 2006 at 2:09 am

    #680459

    Why does there have to be a better way? What is wrong with what you have done? Nothing at all is the answer, and its hugely more performant than what you had in a DTS ActiveX script.

    If you wanted you could do the data conversions within the same Derived Column component rather than the Data Conversion component but there's really no need. Some people may argue that its better to have that split into two seperate components anyway, for clarity.

    -Jamie

     

    Jamie Thomson
    http://sqlblog.com/blogs/jamie_thomson

  • December 29, 2006 at 6:52 am

    #680472

    Maybe I should have said that a little different.  As for permormance I have no idea if what I have is good or bad (you indicate that it is good).  When I said "better way" maybe I should have said "faster way" to set it up.  I could create the dts package for a similar process in less than half the time it would take me to setup the SSIS equivilent.

    Thanks for the feedback

  • January 1, 2007 at 7:47 am

    #680648

    Interesting comment. Do you think that its because its easier in DTS or because you're more familiar with DTS? Do you not think it will be alot quicker in SSIS once you have as much experience with it as you have with DTS?

    Personally I think the ability to basically build this effectively with drag and drop rather than writing an ActiveX Script would be quicker - but then, I haven't written a DTS package in about three years now.

     

    -Jamie

     

    Jamie Thomson
    http://sqlblog.com/blogs/jamie_thomson

  • January 2, 2007 at 7:10 am

    #680736

    I think it is some of both as I'm sure I'll get faster with time.  I'm sure that across the board the time do build packages will be as fast or faster with SSIS than it was with DTS.  It just happened that one of my first few wasn't.  It isn't that I didn't want the change from DTS to SSIS.  I think the move is a good one.  SSIS has much better conditional and looping abilities.

    As for writing the script it didn't take long in DTS because most of it is created automatically.  I did find that there was a much better of doing this after looking around in SSIS.  When I setup the flat file I didn't give the columns data types.  So in the end I did find a better and more correct way of doing this.

    Thanks

  • January 21, 2007 at 1:22 pm

    #684580

    Another approach would be the "Script Component". If you are more comfortable with coding you can use the following code for the string to decimal conversion.  You must set up an Output with the Decimal format and then use the code below. The script "Script Component" provide a blank slate if your coding to visual developemnt.  

    Imports System

    Imports

    System.Data

    Imports

    System.Math

    Imports

    Microsoft.SqlServer.Dts.Pipeline.Wrapper

    Imports

    Microsoft.SqlServer.Dts.Runtime.Wrapper

    Public

    Class ScriptMain

    Inherits UserComponent

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

    '

    ' You must change the Scriptcomponent-Properties-Build checkbox for "Option Sctrict on by default" unchecked.

    '

    Row.StringDec = Row.StringDecimal

    End Sub

    End

    Class

    [font="Comic Sans MS"]Ira Warren Whiteside[/font]

Viewing 7 posts - 1 through 6 (of 6 total)

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