Execute SQL Task: UDF not taking parameters

Hi,

I have an Execute SQL Task in my SSIS Package.
Now, this Execute SQL Task has the following query (Connection Type is OLE DB):

Code Snippet

SELECT dbo.udf_CommonDateTime_Get (GetDate(), ?) As User_Datetime

I want 2 things from this Task:
1) It should take the 2nd argument to the UDF from a variable.
2) It should store the value returned by this SELECT statement into another variable.

So, I go ahead and modify the Parameter Mapping for the Task. Here I add the Input variable name, Data type and I give the Parameter Name as 0.

I also modify the Result Set for the Task. Here, I specify the Result Name as User_Datetime and give the appropriate Variable Name.

I am getting an error here and I believe it is due to the input parameter. The UDF is not getting the 2nd argument correctly.

My questions:
1) Has the Execute SQL Task been designed to handle UDFs like this. If not, then where am I going wrong?
2) What is the work-around for this? I need to pass a parameter (variable) to the UDF.

Thanks in advance.

Regards,
B@.ns

The error message is:

Code Snippet

Execute SQL Task: Executing the query "SELECT dbo.udf_Common_DateTime_Get (GetDate(), ?)

As User_Datetime" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Set UserDateTime

As a workaround, you could you create a new variable to store your sql statement. Set the 'EvaluateAsExpression' property of the variable to 'true'. Then set the expression like this...

"SELECT dbo.udf_CommonDateTime_Get (GetDate()," + user::VariableNameHere + ") As User_Datetime"

Then in 'SourceVariable' property of the execute sql statement to 'Variable' and then choose the variable name.

|||

Hi Martin,

Thank you for the reply. At least it gives me some hope

Unfortunately, I am getting this error:

Code Snippet

The expression for variable "varQuery" failed evaluation. There was an error in the expression.

If I remove the user::VariableNameHere part, it works fine...

Any ideas?

Thanks again.

Regards,

B@.ns

|||

You replaced user::VariableNameHere with the actual name of your variable correct?

|||

Martin,

It worked!!

Thank you so much!!

I had to do this:

Code Snippet

"SELECT dbo.udf_CommonDateTime_Get(GETDATE(), " + (DT_WSTR, 1) @.[User::VariableName] + ") As UserDateTime"

The only thing worries me is that @.[User::VariableName] can be NULL.

I will have to handle that.

Thanks again.

Regard,

B@.ns