Hi,
The requirement is, we have to transfer from Flat file to OledBDestination. Flat File example is: 001|001|abc|
In the Database Table, Column A and Column B are Numeric(4,3). Column C is varchar(50).We have to write custom component (programmatically) which puts in the Database as
0.001|0.001|abc
I followed the Programmatic Sample, that comes with installation, ChangeCase which changes the case of the column as it moves to Database. I modified the program to meet my requirement. Program Sample is below. In below, I get the decimal Value (as columnValue), and then Divide by Math.Pow(10.0,3) to get 0.001. And I set it to thebuffer.SetDecimal method. For some reason, the decimal values does not show up in the Database. It truncates and gives 0.000. What Am I doing Wrong Please help and suggest....
Decimal columnValue = buffer.GetDecimal(colInfo.bufferColumnIndex);
columnValue = (Decimal)(Decimal.ToDouble(columnValue) / Math.Pow(10.0, (3)));
buffer.SetDecimal(colInfo.bufferColumnIndex, columnValue);
All Code here:
public
override void ProcessInput(int inputID, PipelineBuffer buffer) { if (!buffer.EndOfRowset){ IDTSInput90 input = ComponentMetaData.InputCollection.GetObjectByID(inputID); int errorOutputID = -1; int errorOutputIndex = -1; int defaultOutputId = -1;GetErrorOutputInfo(
ref errorOutputID, ref errorOutputIndex); if (errorOutputIndex == 0)defaultOutputId = ComponentMetaData.OutputCollection[1].ID;
else defaultOutputId = ComponentMetaData.OutputCollection[0].ID; while (buffer.NextRow()){ if (columnInfos.Length == 0)buffer.DirectRow(defaultOutputId);
bool isError = false; /// Iterate the columns in the columnInfos array. for (int x = 0; x < columnInfos.Length; x++){ ColumnInfo colInfo = columnInfos[x]; /// Is the column null if (!buffer.IsNull(colInfo.bufferColumnIndex)){ if (colInfo.dataType == DataType.DT_NUMERIC){ Decimal columnValue = buffer.GetDecimal(colInfo.bufferColumnIndex);columnValue = (Decimal)(Decimal.ToDouble(columnValue) / Math.Pow(10.0, (3)));
buffer.SetDecimal(colInfo.bufferColumnIndex, columnValue);
if (!isError)buffer.DirectRow(defaultOutputId);
}
Need Help with Data Conversion??
bitskull
What is the value of columnValue and is the scale set correctly on your column You say it does not look good in the DB, so have you got a truncation issue when inserting into the DB
Try debuging the component and verify the value of columnValue after calculation, before setting the buffer value again.
Try adding a data viewer after the component and see what it looks like in there.
FilipeTB
DarrenSQLIS,
Thanks for your reply. I tried adding DataViewer (Grid). The Data moves from Flat File Sour to Custom Component as 1| 1| abc.
On Custom component , I get the value,(which is columnValue) as decimal value. I try to divide by 1000. Above code shows that.
From Custom component to Oledb Destination, it goest as 0|0|abc. For some reason, it will not go as .001|.001|abc.
I do not know what am I doing wrong. Can custom Component do the Data Conversion If then how,..... I have done best from my side.
Please Reply.