"jhofmeyr@googlemail.com" wrote:

> On Sep 20, 9:04 am, B@ns <B...@discussions.microsoft.com> wrote:
> > "B@ns" wrote:
> > > 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):
> >
> > > 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.
> >
> > > So, 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
> >
> > I am getting the following error:
> >
> > 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- Hide quoted text -
> >
> > - Show quoted text -
>
> Hi B@ns,
>
> I would suggest using an expression to generate this statement. SSIS
> (like DTS before it) struggles to understand parameters in "complex"
> SQL statements ... not that your statement is particularly complex,
> but I have seen it struggle with even simpler statements :)
>
> Good Luck!
> J
>
>

"jhofmeyr@googlemail.com" wrote:

> On Sep 20, 3:06 pm, B@ns <B...@discussions.microsoft.com> wrote:
> > "jhofm...@googlemail.com" wrote:
> > > On Sep 20, 9:04 am, B@ns <B...@discussions.microsoft.com> wrote:
> > > > "B@ns" wrote:
> > > > > 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):
> >
> > > > > 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.
> >
> > > > > So, 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
> >
> > > > I am getting the following error:
> >
> > > > 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- Hide quoted text -
> >
> > > > - Show quoted text -
> >
> > > Hi B@ns,
> >
> > > I would suggest using an expression to generate this statement. SSIS
> > > (like DTS before it) struggles to understand parameters in "complex"
> > > SQL statements ... not that your statement is particularly complex,
> > > but I have seen it struggle with even simpler statements :)
> >
> > > Good Luck!
> > > J
> >
> > Hi J!
> >
> > I got it!
> > I had to create a variable and give the expression there:
> >
> > "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.
> >
> > Regards,
> > B@ns- Hide quoted text -
> >
> > - Show quoted text -
>
> Hi B@ns,
>
> Sorry for not being more explicit in my first explanation - sounds
> like you found the Expression area alright though. I'm sure you'll
> enjoy the strange semi-vba language that they seem to have created for
> use in expressions as you become more familiar with them :)
>
> One thing I am confused about - you say that you needed to create a
> variable to use in the expression ... If you didn't have a variable
> before this, how were you trying to implement the parameters in the
> task before?
>
> Cheers,
> J
>
>

On Sep 21, 4:38 am, B@ns <B...@discussions.microsoft.com> wrote:
> "jhofm...@googlemail.com" wrote:
> > On Sep 20, 3:06 pm, B@ns <B...@discussions.microsoft.com> wrote:
> > > "jhofm...@googlemail.com" wrote:
> > > > On Sep 20, 9:04 am, B@ns <B...@discussions.microsoft.com> wrote:
> > > > > "B@ns" wrote:
> > > > > > 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):
>
> > > > > > 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.
>
> > > > > > So, 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
>
> > > > > I am getting the following error:
>
> > > > > 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- Hide quoted text -
>
> > > > > - Show quoted text -
>
> > > > Hi B@ns,
>
> > > > I would suggest using an expression to generate this statement. SSIS
> > > > (like DTS before it) struggles to understand parameters in "complex"
> > > > SQL statements ... not that your statement is particularly complex,
> > > > but I have seen it struggle with even simpler statements :)
>
> > > > Good Luck!
> > > > J
>
> > > Hi J!
>
> > > I got it!
> > > I had to create a variable and give the expression there:
>
> > > "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.
>
> > > Regards,
> > > B@ns- Hide quoted text -
>
> > > - Show quoted text -
>
> > Hi B@ns,
>
> > Sorry for not being more explicit in my first explanation - sounds
> > like you found the Expression area alright though. I'm sure you'll
> > enjoy the strange semi-vba language that they seem to have created for
> > use in expressions as you become more familiar with them :)
>
> > One thing I am confused about - you say that you needed to create a
> > variable to use in the expression ... If you didn't have a variable
> > before this, how were you trying to implement the parameters in the
> > task before?
>
> > Cheers,
> > J
>
> Hi J!
>
> I had a variable which had to be used as a parameter to a UDF (in Execute
> SQL Task).
> Now, I created a variable to store an expression and used that variable as a
> source in the Execute SQL Task.
>
> Thanks and Regards,
> B@ns- Hide quoted text -
>
> - Show quoted text -

On Sep 20, 3:06 pm, B@ns <B...@discussions.microsoft.com> wrote:
> "jhofm...@googlemail.com" wrote:
> > On Sep 20, 9:04 am, B@ns <B...@discussions.microsoft.com> wrote:
> > > "B@ns" wrote:
> > > > 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):
>
> > > > 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.
>
> > > > So, 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
>
> > > I am getting the following error:
>
> > > 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- Hide quoted text -
>
> > > - Show quoted text -
>
> > Hi B@ns,
>
> > I would suggest using an expression to generate this statement. SSIS
> > (like DTS before it) struggles to understand parameters in "complex"
> > SQL statements ... not that your statement is particularly complex,
> > but I have seen it struggle with even simpler statements :)
>
> > Good Luck!
> > J
>
> Hi J!
>
> I got it!
> I had to create a variable and give the expression there:
>
> "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.
>
> Regards,
> B@ns- Hide quoted text -
>
> - Show quoted text -

"jhofmeyr@googlemail.com" wrote:

> On Sep 21, 4:38 am, B@ns <B...@discussions.microsoft.com> wrote:
> > "jhofm...@googlemail.com" wrote:
> > > On Sep 20, 3:06 pm, B@ns <B...@discussions.microsoft.com> wrote:
> > > > "jhofm...@googlemail.com" wrote:
> > > > > On Sep 20, 9:04 am, B@ns <B...@discussions.microsoft.com> wrote:
> > > > > > "B@ns" wrote:
> > > > > > > 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):
> >
> > > > > > > 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.
> >
> > > > > > > So, 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
> >
> > > > > > I am getting the following error:
> >
> > > > > > 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- Hide quoted text -
> >
> > > > > > - Show quoted text -
> >
> > > > > Hi B@ns,
> >
> > > > > I would suggest using an expression to generate this statement. SSIS
> > > > > (like DTS before it) struggles to understand parameters in "complex"
> > > > > SQL statements ... not that your statement is particularly complex,
> > > > > but I have seen it struggle with even simpler statements :)
> >
> > > > > Good Luck!
> > > > > J
> >
> > > > Hi J!
> >
> > > > I got it!
> > > > I had to create a variable and give the expression there:
> >
> > > > "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.
> >
> > > > Regards,
> > > > B@ns- Hide quoted text -
> >
> > > > - Show quoted text -
> >
> > > Hi B@ns,
> >
> > > Sorry for not being more explicit in my first explanation - sounds
> > > like you found the Expression area alright though. I'm sure you'll
> > > enjoy the strange semi-vba language that they seem to have created for
> > > use in expressions as you become more familiar with them :)
> >
> > > One thing I am confused about - you say that you needed to create a
> > > variable to use in the expression ... If you didn't have a variable
> > > before this, how were you trying to implement the parameters in the
> > > task before?
> >
> > > Cheers,
> > > J
> >
> > Hi J!
> >
> > I had a variable which had to be used as a parameter to a UDF (in Execute
> > SQL Task).
> > Now, I created a variable to store an expression and used that variable as a
> > source in the Execute SQL Task.
> >
> > Thanks and Regards,
> > B@ns- Hide quoted text -
> >
> > - Show quoted text -
>
> Hi B@ns,
>
> I'm still not clear on why you needed to create another variable for
> the expression instead of just using the one that you had created for
> the parameter in the Execute SQL task?
>
> Hmm - just to double check: Did you create the Expression inside the
> Execute SQL task (i.e: In the Execute SQL task you clicked on the
> "Expressions" in the left menu and added the expression there against
> the SQLStatementSource property)?
>
> The only reason I could see for you needing 2 variables is if you set
> the Execute SQL task SQLSourceType to Variable and generated that
> variable somewhere else (maybe a Script task?)
>
> Either way - it sounds like your solution is working, glad I could
> help :)
> J
>