check numeric data type in SSIS

Dear All,

the situation is that i have a column data comes from flat file and all i want to do is to check that the incoming column is numeric(12,3) and if the incoming data exceed that size "12,3" exception or redirect the row is happened.

the problem that i try to apply that with the data conversion or Derived column component but it in case of the scale of the incoming data exceed 3 the component trim until 3 scale.

i also try to perform it with the flat file data source component but i face a problem that if the data in the column is empty then flat file data source component read the numeric column as Zero

i hope someone help me coz i need to handle it soon.

best wishes

Maylo

Here is a thought for your Scale 3 situation.

Could you try importing the data into column X as a larger datatype, say (20,5).

Then use two derived value steps to create a new column Y that is the result of conversion from (20,5) to (12,3) and then back to (20,5).

Now compare the value in column X with the value in column Y.

If X is a valid (12,3) value, then it must now have the same value as Y. Otherwise it will be different.

Simulation:

Flat file value: 123456.789

imported to X (20,5): 123456.789

Converted to Y (12,3): 123456.789

Converted back to Y (20,5): 123456.789

(X == Y) = true

Flat file value: 1234.56789

imported to X (20,5): 1234.56789

Converted to Y (12,3): 1234.567

Converted back to Y (20,5): 1234.567

(X == Y) = false

(in my VB days, we would have achieved something like this by going:

y = int(x*1000) / 1000

if x=y then msgbox "All is sweet." else msgbox "Your value has too many decimal places."

|||

thanx SOoooooo much it helps me alot

best wishes

Maylo

|||

The way I normally get round this is to use a script component.

Feed all available output columns from your flat file into the script component.

In the script component add an extra outpt column as a boolean called, for example, blnOK

In the script component's ProcessInputRow Sub add code similar to the following

If IsNumeric(Row.RowToCheck) Then

Row.blnOK = True

Else

Row.blnOK = False

End If

where RowToCheck is the particular row from the flat file you wish to check.

Then use a conditional split transformation to check the value of your new column blnOK. You can then direct your rows accordingly, ie, where blnOK is TRUE rows would go to your default table and where blnOK is FALSE rows could go to and error table.

This is a simple example but you could extend the code by creating a function to check for any data type, string format etc or even create a DLL, which you can re-use for similar situations though this may be a bit over the top.

Hope this helps

|||That RowToCheck in above should refer to the column/field to check NOT a row. Sorry