January 12, 2015 at 2:33 pm
So I have determined that I need to load my excel file in text mode ( all columns loaded as text), however I have 2 columns that are greater than 255 characters. So I use IMEX=1 and then use the advanced editor to size 2 columns to the data type D_NTEXT and it works. However when I get the next month's file it falls over saying that it is unable to retireve the long data for the 2 columns that are D_NTEXT , this time the first 8 rows( Typeguessrows ) do not contain more thn 255 characters. Each file is 200k long. My solution is to make a dummy template file( file 1 ) which gets the data every month ( file 2) appended to it to create file 3 in excel , and then this file gets loaded into the data and then the dummy rows deleted. However as I need to do this i an automated fashion, so how can I create file 3 every month and is this the best solution? A vb script that opens file 1 and file 2 and creates file 3 or is there a way in SSIS. Has anyone done this and has a script to share or is there a better way to solve this problem? Urgent so any help would be greatly appreciated.
January 13, 2015 at 12:43 am
You can also set TypeGuessRows to 0 in the registry. It then scans the first 60,000 rows or so.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 13, 2015 at 6:39 am
Koen Verbeeck (1/13/2015)
You can also set TypeGuessRows to 0 in the registry. It then scans the first 60,000 rows or so.
WOW. I did not know about that hack. Thanks, Koen.
January 13, 2015 at 1:02 pm
At SSIS at 2008, it will only scan 16483 records max with typeguessrows set to 0 as Microsoft have capped it at this. This is a common misconception even I had that it will do more.
Also remember these rules !
With default settings, The SSIS Excel driver determines the data type for each column based on reading values of the first 8 rows.
If the top 8 records contain equal number of numeric and character types – then the priority is numeric
If the majority of top 8 records are numeric then it assigns the data type as numeric and all character values are read as NULLs
If the majority of top 8 records are of character type then it assigns the data type as string and all numeric values are read as NULLs
Probably why IMEX=1 sounds so appealing.
Convert to csv or get a template excel file in place seem to be the only options. Not allowed to chnage typeguessrows on the server.
January 13, 2015 at 1:30 pm
Jimmy123 (1/13/2015)
At SSIS at 2008, it will only scan 16483 records max with typeguessrows set to 0 as Microsoft have capped it at this. This is a common misconception even I had that it will do more.
You're right. It will scan only 16,483 rows, as mentioned in this KB article. I have no idea how I got that 60,000 number in my head. I blogged about SSIS and Excel a while ago, and there I correctly mentioned 16,483. I must be getting old 😀
If the majority of top 8 records are of character type then it assigns the data type as string and all numeric values are read as NULLs
Are you sure about this? Numerics can be perfectly cast to a string, so they will be imported just as well.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 13, 2015 at 2:21 pm
Fairly certain. Think I has been stung by this recently. I am now definitely thinking convert to csv.
January 13, 2015 at 2:41 pm
Jimmy123 (1/13/2015)
Fairly certain. Think I has been stung by this recently. I am now definitely thinking convert to csv.
I tested it and the numeric value is imported as well, but as a string value of course.
I think NULLs are only retrieved when there are incompatible data types.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 14, 2015 at 6:04 am
Koen Verbeeck (1/13/2015)
Jimmy123 (1/13/2015)
Fairly certain. Think I has been stung by this recently. I am now definitely thinking convert to csv.I tested it and the numeric value is imported as well, but as a string value of course.
I think NULLs are only retrieved when there are incompatible data types.
The NULLS definitely happen when the registry value is NOT set. I know that from experience. Haven't tested the hack yet, so can't say otherwise.
January 14, 2015 at 6:09 am
Brandie Tarvin (1/14/2015)
Koen Verbeeck (1/13/2015)
Jimmy123 (1/13/2015)
Fairly certain. Think I has been stung by this recently. I am now definitely thinking convert to csv.I tested it and the numeric value is imported as well, but as a string value of course.
I think NULLs are only retrieved when there are incompatible data types.
The NULLS definitely happen when the registry value is NOT set. I know that from experience. Haven't tested the hack yet, so can't say otherwise.
It all depends on the data types involved. String can handle everything, so I can't imagine NULLs popping up there.
That's exactly what IMEX=1 does btw: if mixed data types are found, string is chosen over all data types because string can handle all data types.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 14, 2015 at 6:53 am
Koen Verbeeck (1/13/2015)
You're right. It will scan only 16,483 rows, as mentioned in this KB article. I have no idea how I got that 60,000 number in my head. I blogged about SSIS and Excel a while ago, and there I correctly mentioned 16,483. I must be getting old 😀
I think something like that number (65K maybe) is the maximum number of rows that an Excel worksheet could contain prior to Excel 2007. Don't quote me on that though! 😉
January 14, 2015 at 7:00 am
Lempster (1/14/2015)
Koen Verbeeck (1/13/2015)
You're right. It will scan only 16,483 rows, as mentioned in this KB article. I have no idea how I got that 60,000 number in my head. I blogged about SSIS and Excel a while ago, and there I correctly mentioned 16,483. I must be getting old 😀I think something like that number (65K maybe) is the maximum number of rows that an Excel worksheet could contain prior to Excel 2007. Don't quote me on that though! 😉
You may be right 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 15, 2015 at 6:21 am
Koen Verbeeck (1/13/2015)
Jimmy123 (1/13/2015)
At SSIS at 2008, it will only scan 16483 records max with typeguessrows set to 0 as Microsoft have capped it at this. This is a common misconception even I had that it will do more.You're right. It will scan only 16,483 rows, as mentioned in this KB article. I have no idea how I got that 60,000 number in my head. I blogged about SSIS and Excel a while ago, and there I correctly mentioned 16,483. I must be getting old 😀
If the majority of top 8 records are of character type then it assigns the data type as string and all numeric values are read as NULLs
Are you sure about this? Numerics can be perfectly cast to a string, so they will be imported just as well.
The article says 16384 (not 16483) records.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply