Solved

OLE DB provider 'STREAM' for linked server '(null)' returned invalid data for column '[!BulkInsert].COLUMN014'

Posted on 2007-11-27
4
4,292 Views
Last Modified: 2011-10-03
OLE DB provider 'STREAM' for linked server '(null)' returned invalid data for column '[!BulkInsert].

I got this message when I DTS an Excel file to a database table (the datatype for the destination column is float). I tested by removing that column and it was ok. I saved the file as csv and loaded it and query the table using where isnumeric(column014) = 0 and column014 is not null I got nothing so there should be no invalid data in this column. Then why I got this error when loading from Excel. It didn't work even if I loaded from the temp table (which is loaded from the csv file so column datatype is varchar) to my destination table. It didn't work either when I used insert into destination table select * from temp table and also use explicit conversion to convert datatype of the column in temp table to the datatype of the column in destination table.

Has anyone had this problem before? if so how did you solve it? Thanks!
0
Comment
Question by:qinyan
  • 2
  • 2
4 Comments
 
LVL 17

Expert Comment

by:pssandhu
ID: 20363579
I know you checked the data but I still have a hunch that there is some data that not correct.
Try this link: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1003710&SiteID=1

It doesn't tells how to find the problem but it might point in right direction.

Hope this helps.
0
 

Author Comment

by:qinyan
ID: 20365725
Yes I saw this link after searching the error message on google but I don't think this is the problem I'm experiencing. I'm using 2000 DTS not 2005 and I still get this error. and I checked the field in Excel it's just numbers not formula so there is no way for me to tell whether there is divide by zero issue or not. Plus, as I mentioned, I did load it ok after saving it as csv or txt file and ran a query and didn't find any records where isnumeric(columnxx) = 0.

Thanks!
0
 
LVL 17

Accepted Solution

by:
pssandhu earned 500 total points
ID: 20366628
Try this:

1. Open your *.csv file.
2. Select the first column (I am presuming that is where the data is) and go to Data>Text to Columns
3. Rename all the columns to their respective titles (if not already).
4. Now select the first column, right click, select 'Format' and change the format appropriately (So that the format matches the format of the columns in the destination column). For Eg Text, Number, Date etc.
5. Repeat the above step for all the columns.
6. Once finished please seletct all the empty columns and delete them.
7. Repeat the same for all the empty rows. This will ensure there is no extra/corrupted data in the empty cells.
8. Do a 'Save As' so that your original file does not gets overwritten.
9. Use the DTS again to download the data from the excel file that you just saved.

If the above does not work, I will require some sample data to work with.
Let me know how it goes.

Cheers!
0
 

Author Comment

by:qinyan
ID: 20841134
Will try the suggestion when I get a chance; I already worked it out myself using a different way.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now