| Darren Green 2005-11-23, 8:24 pm |
| frostbb wrote:
>
>
> I'm setting up an SSIS package that needs to transform latituded / longitude
> degrees, minutes, seconds values to the decimal equivalent.
>
> I've created a UDF to manage the actual transformation ... but I'm having
> difficulty figuring out how to call the UDF from within an SSIS derived
> column expression. Could REALLY use some help getting the following
> expression into the proper syntax.
>
> I've tried both variations below with no luck ... latitude_degrees,
> latitude_minutes, latitude_seconds are valid input columns.
>
>
>
>
>
>
> Thanks in advance.
>
> Barry
> in Oregon
>
> ==== UDF Definition ====================
==========
>
> CREATE FUNCTION wrd_fn_transform_lat
long_dms_to_dec
> (
> @degrees_in int = 0,
> @minutes_in decimal(13,8) = 0,
> @seconds_in decimal(13,8) = 0
> )
> RETURNS decimal(13,8)
> AS
> BEGIN
>
> DECLARE @Return_DecimalValue
decimal(13,8)
>
> SET @Return_DecimalValue
= 0
>
> IF(@degrees_in > 0) AND (@minutes_in > 0)
> BEGIN
> SET @Return_DecimalValue
= (ABS(@degrees_in)) + (@minutes_in/60) +
> (@seconds_in/3600)
> END
>
> RETURN @Return_DecimalValue
>
> END
> go
>
>
You cannot call externally from within an expression.
Using T-SQL is really not going to be very fast for this type of
calculation compared to other languages. The expression language itself
is very simple, but designed for performance, so I would implement the
calculation entirely in an expression.
Here is a sample -
(@d > 0 && @m > 0) ? (ABS(@d) + (@m/60) +
(@s/3600)) : 0
Note, @d, @s, @m are variable names I used, so change to columns. I find
building and testing expressions is much faster if you prototype in the
Expressions Builder from a property expression, and use variables to
replace columns for this prototype.
--
Darren
http://www.sqldts.com
http://www.sqlis.com
|