November 4, 2011 at 8:37 am
Hi,
I have this following expression:
(DT_WSTR,100)@(System::PackageName)+"Package Failed on"+(DT_WSTR,30)GetDate()
I want to replace the GetDate() Function with a user defined function PUser.ZoneDate. When i evaluate the expression with the user defined function i get the follwing error:
The Function ZoneDate was not recognized. Either the function name is incorrect and or does not exist. The expression cannot be parsed. It might contain invalid elements or it might not be well-formed.
Could anyone suggest where i am going wrong.
November 4, 2011 at 9:03 am
Is the user defined function a SQL function? What parameter(s) does it take, if any? You'll need to connect to the database in order to evaluate it before you can use it in the SSIS expression.
John
November 4, 2011 at 9:20 am
Basically it's a Scalar Valued Function. Here is the code:
CREATE FUNCTION [PUser].[ZoneDate]
(
)
RETURNS DATETIME
AS
BEGIN
DECLARE @getdate-2DateTime
SET @getdate-2 = GETDATE()
RETURN @getdate-2
END
GO
I am using this expression in a mail task i.e. whenever the package fails, a mail will be sent.
To connect to the database, package configuration is being used.
November 4, 2011 at 9:27 am
You don't need to incur the expense of connecting to a database just to get the current date and time, and even if you do, you don't need a user defined function to return GETDATE(). Therefore I'd recommend leaving your expression as it is.
John
November 4, 2011 at 9:42 am
Hi John,
The changes that I am making is to an existing system which is running for quite a time now and which is related to end users from several different time zones.
The above function is just a mock function that we running for testing purpose. The Function will be modified so that a particular time zone is reflected irrespective of where the server is present. Now when we are using Getdate() function only the server time is returned. That's why we need to change the Function in the expression so that when we get a mail, that particular time zone is reflected instead of the server time.
Hope it clarifies the issue 🙂
November 4, 2011 at 9:46 am
Hi John,
The changes that I am making is to an existing system which is running for quite a time now and which is related to end users from several different time zones.
The above function is just a mock function that we running for testing purpose. The Function will be modified so that a particular time zone is reflected irrespective of where the server is present. Now when we are using Getdate() function only the server time is returned. That's why we need to change the Function in the expression so that when we get a mail, that particular time zone is reflected instead of the server time.
Hope it clarifies the issue 🙂
November 4, 2011 at 9:52 am
OK, then if you absolutely need to use a database function, you'll need to use an Execute SQL task to run it and get the result into a variable and use that variable in your expression. However, you might consider using the SSIS GETUTCDATE function instead, and offsetting a certain number of hours depending on the region.
John
November 4, 2011 at 9:57 am
I ll try this and update the result. Thanks for your suggestion 🙂
November 7, 2011 at 6:11 am
It's working. Thanks for your help !!
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply