April 15, 2010 at 12:25 am
what are the dis advantages of excel in ssis
April 15, 2010 at 12:37 am
with regards to? using it as a source/destination? please give more detail.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
April 15, 2010 at 3:13 am
i dont know exactly in one interview they asked like using excel some dis advantage are there what that? plz help me
April 15, 2010 at 6:46 am
Its excel limitations of having just 65536 records in one sheet.
The code must be modified if pulling data into excel exceeds 65536 records without storing header info else its linited to 65535 per sheet.
Rex Smith
April 15, 2010 at 6:51 am
seeteshh (4/15/2010)
Its excel limitations of having just 65536 records in one sheet.The code must be modified if pulling data into excel exceeds 65536 records without storing header info else its linited to 65535 per sheet.
No longer true in SQL 2007, but definitely true in prior versions. You'll need to research the current limit in order to be credible in your interview.
Another disadvantage is that you can run into problems with mixed data types in one column. You'll need to research why this can be a problem and what solutions are available, or you'll fail your interview.
Another disadvantage is that you need to have the Jet data engine installed. Again, the specifics are something you'll need to research.
- 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
April 15, 2010 at 7:20 am
Max worksheet size is 1,048,576 rows by 16,384 columns in Excel 2007.
The fact that there is no explicit column data-typing in Excel is the single biggest cause of problems in SSIS, when importing from Excel, where a single column can contain a mix of NULL, numeric and string elements - looks fine in Excel, but when you try importing it, there are almost always issues.
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
April 15, 2010 at 7:29 am
Phil Parkin (4/15/2010)
Max worksheet size is 1,048,576 rows by 16,384 columns in Excel 2007.The fact that there is no explicit column data-typing in Excel is the single biggest cause of problems in SSIS, when importing from Excel, where a single column can contain a mix of NULL, numeric and string elements - looks fine in Excel, but when you try importing it, there are almost always issues.
Aw, come on! GSquared's post was the perfect balance of giving hints while still making you work a little bit to get the answers, and you went and ruined it by giving the answers away!
😛
The Redneck DBA
April 15, 2010 at 7:36 am
<rant>
I just wasted 2 hours on Excel in SSIS, so I can definitely tell you what the disadvantages are 🙂
As said before, the limitation of the rows could be a disadvantage, but when you use Excel as a source, it isn't. It's just a small source then.
The biggest disadvantage is the intermixed data types, also mentioned in this thread. If SSIS thinks the column contains numbers, than the text values are imported as NULL. And the other way around. You can solve this by setting IMEX=1 in the connection string, but, on most computers, this just makes the driver scan the first 8 rows the find the different data types.
Sure, you can set the row scanning property in the registry, but what if your computer is set up OK, but the server isn't? Everything looks fine in BIDS, but when you try to run it on the server, bam, failure all over the place.
Another disadvantage: you have to hard-code your connection string during design mode to configure your package. This sets the door wide open for errors.
And last but not least (and I guess there are more disadvantages, but these are the ones I can think of right now):
regional settings. If the computer that saves the Excel has other regional settings than the server, you're in a world of pain. For example, one user saves a date as 24/10/2008 (dd/mm/yyyy, which is common in Europe), but if the server has the American notation of mm/dd/yyyy, then everything falls to pieces.
Dates are especially cumbersome, as they can also be read as 01-Apr (whoops, SSIS doesn't get the year) or as a number (like 40269), it all depends on how the date is formatted in Excel.
And don't get me started on the automatic conversion of big numbers to scientific notation, which then gets stored in a string, so you can't get rid of the e. :crazy:
</rant>
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 15, 2010 at 8:10 am
da-zero (4/15/2010)
<rant>I just wasted 2 hours on Excel in SSIS, so I can definitely tell you what the disadvantages are 🙂
---
And don't get me started on the automatic conversion of big numbers to scientific notation, which then gets stored in a string, so you can't get rid of the e. :crazy:
</rant>
Just two hours? That's nothing! My wasted time with Excel is measured in days 🙂
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
April 15, 2010 at 8:30 am
Phil Parkin (4/15/2010)
Just two hours? That's nothing! My wasted time with Excel is measured in days 🙂
Two hours today that is 🙂
(I don't count the hours constructing the package, that was time well spent. The 2 hours resemble try finding the errors, trying to fix them, banging my head against the wall, cursing and crying)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 15, 2010 at 8:33 am
da-zero (4/15/2010)
Phil Parkin (4/15/2010)
Just two hours? That's nothing! My wasted time with Excel is measured in days 🙂
Two hours today that is 🙂
(I don't count the hours constructing the package, that was time well spent. The 2 hours resemble try finding the errors, trying to fix them, banging my head against the wall, cursing and crying)
I feel your pain. Sounds like you have earned your BEER for the day! :w00t:
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
April 15, 2010 at 8:42 am
Phil Parkin (4/15/2010)
I feel your pain. Sounds like you have earned your BEER for the day! :w00t:
I sure did. Make it a double and have one yourself 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply