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. Sorrysql

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

check if field contains numeric character

How can I select all of the rows of my table for which a certain
field's value contains other characters than a-z and A-Z (non alpha
string) ?
Thank youWhere Column like '%[0-9]%'
Look up "Pattern Matching in Search Conditions" in BOL for more information.
<samuelberthelot@.googlemail.com> wrote in message
news:1148478170.488497.325220@.j33g2000cwa.googlegroups.com...
> How can I select all of the rows of my table for which a certain
> field's value contains other characters than a-z and A-Z (non alpha
> string) ?
> Thank you
>|||If it does not help ,please post your actual data + expected result
create table #t (c1 varchar(20))
insert into #t values ('cdjdfj')
insert into #t values ('cd4jdfj')
insert into #t values ('fh')
insert into #t values ('1525')
insert into #t values ('1jj')
insert into #t values ('jkk')
select * from #t where c1 like '%[0-9]%'
<samuelberthelot@.googlemail.com> wrote in message
news:1148478170.488497.325220@.j33g2000cwa.googlegroups.com...
> How can I select all of the rows of my table for which a certain
> field's value contains other characters than a-z and A-Z (non alpha
> string) ?
> Thank you
>|||found out, had to use the PATINDEX function|||Uri Dimant wrote:
> If it does not help ,please post your actual data + expected result
> create table #t (c1 varchar(20))
> insert into #t values ('cdjdfj')
> insert into #t values ('cd4jdfj')
> insert into #t values ('fh')
> insert into #t values ('1525')
> insert into #t values ('1jj')
> insert into #t values ('jkk')
>
> select * from #t where c1 like '%[0-9]%'
>
What about :
insert into test values ('!"=A3$%^&*()_')
Try this instead:
select * from #t where c1 like '%[^A-Z]%'=20
Jamie.

Check for numeric value

Is it possible to check whether a column (define as char
type) value is numeric using something like
Select * From table_name Where Field1 is numeric ?
Thanks,
BenYou can use the ISNUMERIC function for this:
WHERE ISNUMERIC(colname) = 1
Note, however that this returns 1 if the data can be converted to int,
float, money etc. So things like "E" and "," in the string will pass the
test. If you post what you mean precisely by "numeric" we can possibly give
a better suggestion.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Ben" <bluebells88@.yahoo.com> wrote in message
news:2eb201c3a9c4$176cb430$a601280a@.phx.gbl...
> Is it possible to check whether a column (define as char
> type) value is numeric using something like
> Select * From table_name Where Field1 is numeric ?
> Thanks,
> Ben|||Thank your very much for your answer. This one works fine
for my case :)
I've another question: Is it possible to determine if the
column value is NOT alphabet. (using ASCII ? It seems
impossible to me.)
Thanks,
Ben
>--Original Message--
>You can use the ISNUMERIC function for this:
>WHERE ISNUMERIC(colname) = 1
>Note, however that this returns 1 if the data can be
converted to int,
>float, money etc. So things like "E" and "," in the
string will pass the
>test. If you post what you mean precisely by "numeric"
we can possibly give
>a better suggestion.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Ben" <bluebells88@.yahoo.com> wrote in message
>news:2eb201c3a9c4$176cb430$a601280a@.phx.gbl...
>> Is it possible to check whether a column (define as
char
>> type) value is numeric using something like
>> Select * From table_name Where Field1 is numeric ?
>> Thanks,
>> Ben
>
>.
>

Check Constraint help,, Alpha and Numeric

Hey there, In SQL server 2000, I need to create a constraint on one of
the columns that the field enterd must be 2 alpha charactors and the
last 3 must be numeric.
soo, for example
aa123
thanx in advanceUse something like this:
ALTER TABLE TableName ADD CONSTRAINT ConstraintName
CHECK (ColumnName LIKE '[A-Z][A-Z][0-9][0-9][0-9]')
Razvan
Bonzol wrote:
> Hey there, In SQL server 2000, I need to create a constraint on one of
> the columns that the field enterd must be 2 alpha charactors and the
> last 3 must be numeric.
> soo, for example
> aa123
> thanx in advance|||Thanx, but where would I actually put this? atm im trying through right
clicking on the column and creatinng a constraint|||You can execute the above statement in a Management Studio query window
(or in Query Analyzer if you are using SQL Server 2000).
If you want to do this using the graphical interface (in Management
Studio), you should go to the Constraints node (not the Columns node),
right click and choose "New Constraint..."; in the "Expression", type:
ColumnName LIKE '[A-Z][A-Z][0-9][0-9][0-9]'
(of course, replace ColumnName with the name of your column)
Razvan

Check Constraint help,, Alpha and Numeric

Hey there, In SQL server 2000, I need to create a constraint on one of
the columns that the field enterd must be 2 alpha charactors and the
last 3 must be numeric.
soo, for example
aa123
thanx in advanceUse something like this:
ALTER TABLE TableName ADD CONSTRAINT ConstraintName
CHECK (ColumnName LIKE '[A-Z][A-Z][0-9][0-9][0-9]')
Razvan
Bonzol wrote:
> Hey there, In SQL server 2000, I need to create a constraint on one of
> the columns that the field enterd must be 2 alpha charactors and the
> last 3 must be numeric.
> soo, for example
> aa123
> thanx in advance|||Thanx, but where would I actually put this? atm im trying through right
clicking on the column and creatinng a constraint|||You can execute the above statement in a Management Studio query window
(or in Query Analyzer if you are using SQL Server 2000).
If you want to do this using the graphical interface (in Management
Studio), you should go to the Constraints node (not the Columns node),
right click and choose "New Constraint..."; in the "Expression", type:
ColumnName LIKE '[A-Z][A-Z][0-9][0-9][0-9]'
(of course, replace ColumnName with the name of your column)
Razvan

Chart Problem

I want to display numeric value like 1,2,3,4,5 on X'Axis in Column Graph how to do this?I have GroupName on X'Axis.but i do not want to show id of tht GroupName on X'Axis.I want to show statuc numeric value in SQl server 2005 SSRS.
Can anyone help Me?
Thanks
Care Career
From http://www.developmentnow.com/g/115_2004_12_0_6_0/sql-server-reporting-services.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.comHi
Turn off your axis labels and create a table item to display what you
need. Look at my reply in this post
http://groups.google.com.au/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/e9c478240484b44a/ad757ca86965ed0e?hl=en&lnk=st&q=#ad757ca86965ed0e
Cheers
Shai
On Nov 19, 10:18 pm, Care Career<vertex.vibha.gro...@.hotmail.com>
wrote:
> I want to display numeric value like 1,2,3,4,5 on X'Axis in Column Graph how to do this?I have GroupName on X'Axis.but i do not want to show id of tht GroupName on X'Axis.I want to show statuc numeric value in SQl server 2005 SSRS.
> Can anyone help Me?
> Thanks
> Care Career
> Fromhttp://www.developmentnow.com/g/115_2004_12_0_6_0/sql-server-reportin...
> Posted via DevelopmentNow.com Groupshttp://www.developmentnow.com

Char Format

I have one table that has a column 'amount' with datatype 'numeric(19,5)'
I'm want to insert the data from this column into another column (in a different database) with this format: '00000000.00' as 'char'
example: If in the first column I have 800.75864 I want to insert it in the other column as '00000800.75'

any suggestions on how to make this conversion?This is freaking blowing my mind...

This should work

DECLARE @.x decimal(15,5), @.y char(11)
SELECT @.x = 800.75864
SELECT @.x, RIGHT(REPLICATE('0',11)+CONVERT(char(11),CONVERT(d ecimal(15,2),@.x)),11)

But's it's acting like it's doing an implicint conversion to numeric...

Hold on..

If I do...

DECLARE @.x decimal(15,5), @.y char(11)
SELECT @.x = 800.75864
SELECT @.x, REPLICATE('0',11)+CONVERT(char(11),@.x)|||If you just need the rounding, then check the round (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ra-rz_93z8.asp) function. If you need zero padding too (which is rather silly in most cases, since SQL Server handles all of the "leading zero" issues for you), then you'll probably have to resort to a user defined function.

-PatP|||Methinks thou meant:DECLARE @.x decimal(15,5), @.y char(11)
SELECT @.x = 800.75864
SELECT @.x, RIGHT(REPLICATE('0',11)+CONVERT(varchar(11),CONVER T(decimal(15,2),@.x)),11)That works, as long as the value to convert is positive... Then you get some "interesting" results as the sign floats about!

-PatP|||Yeah...what an idiot...

char(11)...

just found the prob...

Thanks...|||It is the "little" things that make the biggest boom when you trip over them!

-PatP|||declare @.n numeric(19,5)
set @.n = 800.75864
SELECT REPLACE(STR(@.n-.005,11,2),' ','0')

Hans.|||THANKS A LOT! IT WORKED|||Originally posted by diegocro
THANKS A LOT! IT WORKED

What did you expect?

And which one did you use?|||Both, I've been trying to do this since yestarday, that's why I was happy.|||Well that'll learn ya