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_latlong_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

frostbb wrote:
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

Darren,

Thanks for the quick response. It's very much appreciated!

I'll 'gin' up an embedded expression as you've suggested.

Bummer though. In general I really like to encapsulate these type of things
in a UDF so that if something changes in the future I need only go to one
place to make the correction. Guess I'll be building a lot of custom SSIS
tools (controls) in the future

Best wishes and happy computing!

Barry
in Oregon

"Darren Green" <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote

Hello,

Couldn't you also write a custom procedure using VB.net or C#, compile it to
a DLL, then reference the custom procedure?

Jim

"frostbb" wrote:

Jim,

Thanks for the reply. I expect that's possible. We have VS 2005 as one of
our primary development tools. I've yet to experiment with C# <=> SSIS
interactivity. We create lot's of custom user controls that we incorporate
into the visual studio tools bars. If possible I'd like to simply create
custom tools that could be made available via the SSIS tool bar ... although
at this point I haven't the slightest clue as to what it will take to, say,
extend (i.e. inherit from) and customize the derived column tool.

Best wishes and happy computing.

Barry
in Oregon

"Jim" <Jim (AT) discussions (DOT) microsoft.com> wrote