Post reply

Use of UDF in Data Transformation Task

  • June 24, 2005 at 10:55 am

    #60849

    I wanted to use a UDF with two parameters in the SQL Quesry of a Data Transformation task. The UDF returns a table of results which I want to write to a destination (in this case an Excel worksheet). The two parameters to the UDF allow me to limit the result table and hence the amount of work done. These two parameters need to be substituted in the Query

    select mytab.a,mytab.b,mytab.c from dbo.Functionname(?,?) mytab

    This gave me a syntax error.

    I rewrote the UDF to process all the possible data (not limited by the parameters) and changed the SQL Query to select only those I wanted then it ran OK.

    select mytab.a,mytab.b,mytab.c from dbo.Functionname() mytab

    where mytab.x = ? and mytab.z = ?

    This can take a lot longer to run (depending on the total size of the underlying tables).  Any ideas will be welcome.

  • June 26, 2005 at 8:46 am

    #568469

    You can not use a UDF as specified in your first example in the ExecuteSQL task. This is because the meta data about the parameters is not available.

    Call your UDF in a stored procedure and put the stored procedure in  the ExecuteSQL task.

     

    --------------------
    Colt 45 - the original point and click interface

  • June 27, 2005 at 4:51 am

    #568524

    Thanks very much. That works fine!  The UDF is now just what I want and the stored procedure is a simple stripped down statement that just passes the parameters through in a select statement.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply