December 28, 2006 at 3:04 pm
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
Check http://www.sqlis.com ?
MohammedU
Microsoft SQL Server MVP
December 29, 2006 at 2:09 am
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
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
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
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
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