Calling function in SSIS -Data flow task

• khushbu

  SSCarpal Tunnel

  Points: 4591

  More actions

  October 9, 2009 at 1:29 am

  #134292

  Hi

  I have a package which uses data flow task. In the source of data flow task it is calling tha function (the function accepts a parameter).

  When i execute the package it passes me error:

  OLE DB source failed the pre execute phase and returned error code 0xC02092B4

  The command used to call function is:

  dbo.<functionname> (parameter)

  Can somebody help me.

  This error usually comes with SP but in my case it is Function!!

• Elliott Whitlow

  SSC Guru

  Points: 102402

  More actions

  October 9, 2009 at 1:35 am

  #1063899

  I don't think you can do this.. But you might try

  SELECT FieldName = dbo.<functionname> (?)

  OR

  SELECT * FROM dbo.<functionname> (?)

  I'm not sure either would work but that is how I would try to get them working..

  CEWII

• khushbu

  SSCarpal Tunnel

  Points: 4591

  More actions

  October 9, 2009 at 2:00 am

  #1063903

  well.. this is not working 🙁

  Moreever the parameter that i'm passing is coming from the select statement that sits in same task

  like

  Select @var1= col1 from my_table

  select @PStatus = status,@message = err_message from dbo.<function_name> (@var1)

  is this ok..?

  I dont want to make variables and pass them to package using paramaters since this would require me to make 100 variables then.. which is not good design at all!!

• Elliott Whitlow

  SSC Guru

  Points: 102402

  More actions

  October 9, 2009 at 2:22 am

  #1063915

  I've never got something like this to work. It is like SQL can't decide exactly what the structure of the output will be..

  Now if you get that variable in a Exec SQL Task beforehand and then use it in the DataFlow that should work..

  CEWII