Hi all!
I am importing data from a CSV to a DB with a SSIS package.
Among some things things it already does, it has to decide if the relation between one row and the following is acceptable.
If it is not, the 2nd row is discarded, the next one is taken and the relation value is calculated again to decide whether to keep this one or not, and so on.
To calculate this value, I need to apply a formula that includes sin(), cos() and acos() functions.
I have already written this formula as a scalar-valued function in my SQL Server.
So, my question is:
- Is there a way to call a function (a UDF) within the Expression in a Derived Column dataflow item
and if not,
- Hoy can I use trigonometric functions within the Expression in a Derived Column dataflow item
I hope someona can tell me something about this... I'm falling into despair! :-s
Thanks a lot!!
Cau
UDF or trigonometric functions in an expression for a Derived Column?
Milos Cimfl
The functions obiously do not exist today in the xpression syntax, and you cannot extend it. You could perhaps use the OLE-DB Command or Lookup Transform to call back to SQL with values, but the real issue I see is the abilit influence the following row.
I suggest you look at a Script Compoent, which being VB.Net has full access to the .Net framwork, in particular the System.Math ( I think) namespace so you have the functions. You can also write an asynchronous transform to "cross" rows with your logic. If you can evaluate the current row on the basis of the previous row, you can use a synchronous transform which is much easier and the default behaviour. You may want to search/read Books Online for topics about extending the data flow with the script transform and sync vs asynch transforms.
vbSathya
Thanks for your soon and useful response, Darren!
My idea was trying to use variables to keep the last "accepted" row (that is, variables global to the SSIS package) but maybe that was not the easiest way to do this...
I think I'll be checking the Script Component thing.
Thanks again!
Cau
Zoop1984
In particular, once you are famiiar with the basics of the script component, have a look at http://msdn2.microsoft.com/en-us/ms136133(SQL.90).aspx which describes how to create an asynchronous script component.
Donald
When using BOL, please remember that you can add comments (and vote) using the controls at the foot of the BOL page - this will help us to improve the documentation for future use.