January 7, 2011 at 5:59 am
Hi All,
Just to provide a bit of background to this... I have two production database servers, one in a European location and the other in America. They are both identical databases (SQL 2008 Standard) that host the same ERP Solution. There is a need to transfer BOM (Bill of Material) Data from the European system to the American system - to facilitate this I have created an export routine on the European Server and an Import Routine on the American server which works really well, the servers connect over the Domain via an SSIS Package and the BOM data is seamlessly passed across.
As a contingency measure, I have been asked to provide a backup method of transferring the same data - my obvious thought was to use Excel files as the medium, exporting to them from the European system via SSIS and e-mailing them to the American site where they would then be imported (again by SSIS) into the American System.
While I have managed to get this process to work, once imported, the data on the American Server contains NULL values that were not present on the European server.
Having done a bit a bit of research into this, it seems to be a known issue and one of the suggested workarounds was to use .csv files as the medium instead of Excel. I then proceeded to change my SSIS package over to a .csv file but on attempting to import from them into the American database, the package is failing due to truncation errors at the point of insertion into the SQL tables.
If anyone has any hints or tips regarding the NULLs / truncation issue or even has an ‘out of the box’ solution for an alternative way to transfer the data then I would really appreciate any words of wisdom.
Kind Regards,
John
January 7, 2011 at 6:48 am
Do you have mixed data types in the columns that are coming up with incorrect nulls? Like the first few rows are numbers, and below that it has character data, or vice-versa?
If so, this is a common problem, and you'll need to look up "IMEX" as a connection option. Depending on the data in the Excel file, you may also have to do a registry edit to allow the import engine to scan more than the first 8 rows while figuring out what data type a column contains.
You can also avoid this by using a tab or pipe delimited text file, and defining the column types as per OpenRowset format file methodology.
So, take a look at OpenRowset (my prefered solution), and at IMEX, in Bing/Google/whatever, and see which one looks most applicable to your situation.
I wouldn't use CSV. It's called "comedy separate values" for a reason. Often causes more problems than it solves.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 7, 2011 at 11:14 am
Hi GSquared,
I tried your suggestion relating to the IMEX Connection flag and the registry change but no luck I'm afraid... the data is a bit of a mix, some of the columns are empty (but don't contain NULLS) and some columns contain numeric and alpha-numeric values so I can see why you suggested that avenue of enquiry.
I then tried your suggestion of tab delimited text files... bingo!! The crucial thing here being to correctly set the output column character length (of the text file) to match the columns of the table I was inserting into, in the SSIS Package.
Thanks very much for taking the time to post a few suggestions for me - it really has made a big difference to my day and I'll hopefully enjoy my weekend all the more knowing I have found a solution for this.
All the very best,
John
January 7, 2011 at 11:18 am
You're welcome.
It's always been interesting to me that SSIS works so poorly with Excel. You'd think MS could get that one ironed out.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 7, 2011 at 1:00 pm
Without delving into all the details you provided, I think the solution has to be very simple.
I came across the same situation where I had to import data from Excels to SQL Server and surprisingly enough my Excels had null rows appended at the end (without any data what so ever).
My solution was simple in that I just added a Conditional Split right after my excel source. If Any of the rows/columns were null, they were simply redirected to some other place for Auditing purposes. That for me has worked flawlessly by far. So try it, should work.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply