All,
I have to use a field that is calculated in a data flow process and call a database function (return a value) to do anther calculation; then return a value back to the data flow.I tried OLD DB Command but I cannot configure to return a value back to the same data flow.
If there any transformations that can call a DB function and get a value from the function in the middle of the data flow process?Need more detailed instruction.
The data flow is Like:
SourceDB à New_filed 1 = field1 + filed2 à New_filed 2= DB_function (New_filed 1) à Destination DB
Thanks in Advance
Jessie
HI, to return a value from a DBfunction, you need to add a derived column into the pipeline and map the OLEDB command return value to this newly added derived column. In the OLEDB command text you insert the following command:
EXEC ? = DB_Function(?)
Then on the mapping tab, you map the return value (first ?) to the derived column and the parameter (second ?) to your New_Field 1 parameter. This way, the new derived column gets the parameter from the DB_function.
HTH,
Ccote
Hi, Ccote,
Thanks so much for the reply. I have followed your instruction but still getting errors. Here is the detail.
before the OLE command, I added a derived column, make a new column(NEW_Col) there with the same data type as the DB_function return value (int), set the default value to 0.
In the OLE command,I put exec ?=[dbo].[F_DBfunction] (?,?,?) in the SQLcommand field, mapping the 3 input column with the parameters, map the first ? with the NEW_Col from the derived column.
The OLE command does not let me to add any new column to the OLE command output.
I got error when I click on REFRESH, “Invalid parameter number ‘
Where I’m missing here?
Thanks
Jessie
|||I got it, after change exec ?= [dbo].[F_DBfunction] (?,?,?) to exec ?= [dbo].[F_DBfunction] ?,?,?
Thanks
No comments:
Post a Comment