user-defined function in data flow RRS feed

  • Question

  • I want to use a scalar user-defined function (udf) in a data flow with an OLE DB Source and an OLE DB Destination.  The udf parameters are 2 columns from the source and the udf result is mapped to a destination column.

    I've tried using an OLE DB Command component but I'm not getting very far.  This statement works in Management Studio:

    SELECT dbo.objectIDFromSourceKey('person','-128283')

    I was expecting to just substitute ? for each of the function values 'person','-128283' but SSIS doesn't like the syntax (apparently it needs a FROM clause).  Am I on the right track here or...?

    Thursday, May 11, 2006 5:20 AM

All replies

  • It *really* helps if you post errors along with questions like this.

    I get "Parameters cannot be extracted from the SQL command. The provider might not help to parse parameter information from the command. In that case, use the "SQL command from variable" access mode, in which the entire SQL command is stored in a variable.", so assuming this is your problem, then you can workaround.

    Create a variable, that is evaluated as an expression. The expression can be used to build up the SQL with the parameter of your choice. The OLE-DB source can then be set to get the value from a variable instead.

    Since this is a scaler function, why not just use an Execute SQL Task, simpler than a Data Flow and more efficient.

    Thursday, May 11, 2006 7:59 AM
  • I am trying to do the same thing (I think) and I am having problems. I entered the SQL Command as "select dbo.udf_ET1(?,?,?, 'I')". This is in an OLE DB Command component. I'm expecting to pass in values from my data flow and get back a scaler value. I cannot get the OLE DB Command to accept the syntax of the user-defined function. I did find a work around for this.

    I created a stored procedure with an output paramter. (see code below) The stored procedure acts as a wrapper for the user-defined function.

    create procedure dbo.Get_ET1_IBNR_Code
      @Insurance_Company_Code varchar(25),
      @Effective_Year smallint,
      @LOB_Code char(3),
      @IBNR_Code varchar(7) output
    set nocount on
    select @IBNR_Code = dbo.udf_ET1(@Insurance_Company_Code, @Effective_Year, @LOB_Code, 'I')

    Then, I was able to change the SQL Command of the OLE DB Command to "execute dbo.Get_ET1_IBNR_Code ?,?,?,? OUTPUT". In the data flow step before the OLE DB Command, I used a derived column task to create a new empty ("") data element. In the OLE DB Command, I assign the output parameter to the derived column. This is working okay but I would still prefer to do this without the wrapper procedure. I believe simple is better and the wrapper procedure just creates more code to maintain.

    Is it possible to use a user-defined function in an OLE DB Command component?


    Wednesday, December 20, 2006 9:26 PM