Which data type don't accept "Blank Value"

Question

Which data type don't accept "Blank Value"

rocky_498

SSCertifiable

Points: 6532
More actions
December 6, 2010 at 7:42 pm

#143755

I am facing unique problem, My source file 60k rows, and source has mix data, data/blank/Null, Whenever i tried to run this package, package giving me error package transfer 8k rows and then stop running. First i tried to transfer only top 10 rows and all fields has data no null/blank work fine. My question is which data type don't accept "Blank" value. i.e ( Float,varchar,char, int, date, money)? Thanks in advance.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

rocky_498 (12/6/2010)
I am facing unique problem, My source file 60k rows, and source has mix data, data/blank/Null, Whenever i tried to run this package, package giving me error package transfer 8k rows and then stop running. First i tried to transfer only top 10 rows and all fields has data no null/blank work fine. My question is which data type don't accept "Blank" value. i.e ( Float,varchar,char, int, date, money)? Thanks in advance.

Gosh, Rocky... it probably took you longer to sign in and write the question above than to setup and run your own test. 😉 Try it!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rocky_498 SSCertifiable Points: 6532 More actions · Answer 2

i have 134 columns and its very hard to find out + time consuming to check each columns/data, May b i m wrong just wondering is it possible any data type i.e (varchar,char,int,float,date,money) don't accept blank value? I can understand if you choose "do not accept Null value" but never heard any data field don't accept blank value, If you go through this kinda problem and you know some thing, Feel free to share to experience!!!!! Thank your for your time.

Phil Parkin SSC Guru Points: 246961 More actions · Answer 3

You should really test it - wouldn't take long. Don't use your project to test - set up something manageable and quick.

But if by 'blank' you mean an empty string, I would say that you are going to have trouble with numeric fields and dates.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

rocky_498 SSCertifiable Points: 6532 More actions · Answer 4

I create one test table with different data type i.e varchar,nvarchar,int,float,date,money and try to load with blank,0, values they run fine. I am assuming the problem is source table field has data type "varchar" and target table field has data type "int" and source field has data i.e " 119,200, 199-B,Blank, 0 and so on"

i am assuming "199-B" causing problem? If yes SSIS GURUS please guide me how i can face this kinda issue. I can't change source data/target table. Thanks.

Phil Parkin SSC Guru Points: 246961 More actions · Answer 5

Your initial post referred to 'blank' data.

Now you are asking why the text value '199-B' won't post to an integer field. I can't understand why you would ever expect that it would.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

rocky_498 SSCertifiable Points: 6532 More actions · Answer 6

Phil it is more than 60k rows my first thought was blank value giving me problem, but after you guys suggest find out which fields don't accept blank value i tested it's seems like all fields accept blank values after that i checked and find out one source filed that giving me problem has this kinda data "19-D" and target field has "INT".

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

rocky_498 (12/8/2010)
Phil it is more than 60k rows my first thought was blank value giving me problem, but after you guys suggest find out which fields don't accept blank value i tested it's seems like all fields accept blank values after that i checked and find out one source filed that giving me problem has this kinda data "19-D" and target field has "INT".

Agh... sorry Rocky. I didn't mean for you to test all 134 columns. I only meant for you to test a single instance of each datatype to find out.

For your 19-D problem, take a look at the following article which came out just a week ago...

http://www.sqlservercentral.com/articles/IsNumeric/71512/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rocky_498 SSCertifiable Points: 6532 More actions · Answer 8

Thanks for your reply, I know i can use "execute sql task " in ssis to use this procedure, just wondering is there any way i can fix this kinda problem in "Derived column" Transformation?

WayneS SSC Guru Points: 95460 More actions · Answer 9

I have to admit - I had stopped watching this thread because of how obvious the answer was, and after seeing all the responses decided to peek in again. I was pretty surprised at the twist it has gone through! I just might start watching this thread again!

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2