NULLS allowed, but still not accepting no value??

I am trying to import some data from a text file into SQL Server 2008R2, but get an error message stating that it cannot do a type conversion. The error arises in columns where there are no values, even though that column is setup to accept NULL values.

The import works if I have value in all cells.
mitdanielsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jogosCommented:
How do you import? Pretty crusial to get focused help.

For example with bulk import you can have a format file that gives the import the knowledge of what it can expect on that place.
http://msdn.microsoft.com/en-us/library/ms178129.aspx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
brad2575Commented:
If you have a blank field, check to verify it is not a " " (space) because if you are trying to import this to an int or a date field it will not like that "can not do a type conversion".
mitdanielsAuthor Commented:
Thanks for your comments.

I retried doing a "Find and Replace" of NULL text with no value or space, but still get this error message when doing the import:

"Error 0xc02020c5: Data Flow Task 1: Data conversion failed while converting column "Column 3" (18) to column "Column 3" (109).  The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
 (SQL Server Import and Export Wizard)"
SolarWinds® IP Control Bundle (IPCB)

Combines SolarWinds IP Address Manager and User Device Tracker to help detect IP conflicts, quickly identify affected systems, and help your team take near instantaneous action. Help improve visibility and enhance reliability with SolarWinds IP Control Bundle.

Anthony PerkinsCommented:
Were making progress, now if we could just have the source and destination data types as well as some sample data, we may actually be able to give you an answer that is better than a guess.
mitdanielsAuthor Commented:
I am guessing that it has something to do with how a .txt files data is handled by SQL Server, since I could import the same data using an Excel file.

I tried tab and comma separated versions.

I have attached the file I am trying to import. The only difference is with the first row, which I've added so you can see the data type for each column.
DataTest.txt
Anthony PerkinsCommented:
Once I corrected your file by adding the missing CrLf on the last line, it imported fine by accepting all the defaults.
Anthony PerkinsCommented:
This is what the destination table looks like:
CREATE TABLE [dbo].[DataTest](
	[ID1] [varchar](50) NULL,
	[ID2] [varchar](50) NULL,
	[ID3] [varchar](50) NULL,
	[ID4] [varchar](50) NULL,
	[ID5] [varchar](50) NULL,
	[ID6] [varchar](50) NULL,
	[ID7] [varchar](50) NULL,
	[Bit1] [varchar](50) NULL,
	[Bit2] [varchar](50) NULL,
	[Bit3] [varchar](50) NULL,
	[ID8] [varchar](50) NULL,
	[ID9] [varchar](50) NULL,
	[ID10] [varchar](50) NULL
)

Open in new window

And here are the contents of the table:
ID1	ID2	ID3	ID4	ID5	ID6	ID7	Bit1	Bit2	Bit3	ID8	ID9	ID10
1	9	18	1	2	5	15		TRUE		1		TRUE
1	17			3								
1	20											
2	15	18	8	6	17	25		TRUE		1		TRUE
2	14		140									
3	21	11	100	12	25	50		TRUE		11		TRUE
3	22			4								
3	23											
3	24											
4	8	9	12	4	3	5			TRUE	2		
4	10	22	114									
5	12	12	12	5	4	7	TRUE			3		TRUE
5	6	6	8	8								
5		5	140									
5			52									
5			10									
6	5	15	140	11	6	30	TRUE			4		TRUE
6	14	18	8									
6		11	100									
6			114									
6			52									
6			10									
6			138									
7	5	15	140	11	5	25	TRUE			4		TRUE
7	14	18	8									
7	3	21										
8	15	13	8	1	5	10			TRUE	5		TRUE
8	16	17		7								
9	2	3	140	11	24	50	TRUE			6		TRUE
9	19	7	8									
10	7	12	8	11	10	30		TRUE	TRUE	6		TRUE
10	20	17	12									
11	1	15	8	11	25	30			TRUE	1		TRUE
11		19	12									
12	4	10	12	11	38	50				8		TRUE
12	13	23	140									
13	2	1	52	10	8	16		TRUE		9		TRUE
13	5	13	12									
14	5	16	8	9	4	12			TRUE	10		TRUE
14	11	24	114									

Open in new window

mitdanielsAuthor Commented:
Thanks acperkins, I do however need the data types to be ints not varchar.
Anthony PerkinsCommented:
That is just it, they can't be.  You need to quit wasting time and import this into a staging table using varchar data type and then converting it to your final production table like this:
INSERT YourProductionTable (ID1, ID2, ID3, ID4, ID5, ID6, ID7, Bit1, Bit2, Bit3, ID8, ID9, ID10)
SELECT	NULLIF(ID1, ''), 
		NULLIF(ID2, ''), 
		NULLIF(ID3, ''),
		NULLIF(ID4, ''), 
		NULLIF(ID5, ''),
		NULLIF(ID6, ''), 
		NULLIF(ID7, ''), 
		NULLIF(Bit1, ''), 
		NULLIF(Bit2, ''), 
		NULLIF(Bit3, ''), 
		NULLIF(ID8, ''), 
		NULLIF(ID9, ''), 
		NULLIF(ID10, '')
FROM YourStagingTable

Open in new window


Alternatively, hire a consultant to create an SSIS package that will use a Derived Column to do the conversion on the fly.  Of course, this assumes you are using something other than the Express Edition.
mitdanielsAuthor Commented:
Despite not solving my problem directly, this advice is sound, thanks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.