Max number of columns during data read in
Hi,
A quick question for max column issue in SSIS: I am trying to read in csv file with 1,605 columns but failed, is it because SSIS have the same limitation of max columns as SQL Server , which is 1,024 ?
PS: i am using SQL Server 2008 Enterprise Edition
Regards
yongyang
August 31st, 2012 12:38pm
SQL server has wide tables which support 30000 columns and the non wide ones supporting 1024 columns. So i guess the same restriction must apply to SSIS which would not allow you more than 1024.
On a separate note though, it sounds like a lot of columns. Have you considered splitting your files?http://btsbee.wordpress.com/
Free Windows Admin Tool Kit Click here and download it now
August 31st, 2012 1:00pm
Hi btsbee,
Thanks fro the reply, I am quite a newbie on SSIS, would you mind provide a bit more info for how to splitting the files? cheers
August 31st, 2012 1:10pm
Well what i meant by splitting files was instead of doing something complicated to in SSIS can you get the publsiher to publish instead of one source csv file with 1605 columns to multiple csv files with lesser columns. If some columns do not contain data
to be imported then these columns can be skipped completely. This just simplifies the ETL process. Is your destination going to be a sql wide table?http://btsbee.wordpress.com/
Free Windows Admin Tool Kit Click here and download it now
August 31st, 2012 6:08pm
Hi btsbee,
Many thanks for the info, I never try publisher before, will check it in details. you are right about the destination -- I will need to write it into a sql wide table. In all these 1605 columns, quite a certain number of Null value exist. I am not
sure wide table will be the best choice for store the data, but it worth a try. The skipped column probably will be difficult to work as we will need to keep the column even if it only has one value.
September 1st, 2012 8:13pm
Hi Stephen,
The idea what we would like to present is that can your source system come up with multiple CSV files instead of one CSV file with 1605 columns. later there are multiple approach(es) available to manipulate the data as desired.
Again 1605 columns is indeed lot and there are challenges to maintain a large sparse file/data.Please vote as helpful or mark as answer, if it helps
Cheers, Raunak | t: @raunakjhawar |
My Blog
Free Windows Admin Tool Kit Click here and download it now
September 3rd, 2012 12:54am
Hi Raunak,
Unfortunately it's difficult to cut the csv file into multiple one to decrease the number of columns, unless write a seperate program to do that. Every csv file is encrypt and zip as a individual read only file, which arrive in our FTP daily. However
quite a null value inside the csv file, I wonder would the wide table in SQL Server help to sovle this issue? cheers
Regards
September 3rd, 2012 5:21am
Am confused with your question. So you have a very large csv file which has 1600+ columns and you want to import it to SQL as is i.e. you want all the data from the source csv file to be imported to SQL server.
If yes then the destination sql table has to be a wide table which can accomodate all your data and you need to explore a solution outside SSIS. BCP will be a good starting point to import data. Check
this for reference. However if you still want to use SSIS to import data then check
this where the author has imported data to a wide table in SSIS using scripts.http://btsbee.wordpress.com/
Free Windows Admin Tool Kit Click here and download it now
September 3rd, 2012 8:29am
Another option to consider... Load directly into SQL table as a single column and split them based on Column delimiter using SQL code.
Meganathan Kanagaraj (MCTS - SQL 2005)
September 3rd, 2012 11:59am
Hi,
Many thanks for the information, will review the link and give a try!
Regards
Free Windows Admin Tool Kit Click here and download it now
September 4th, 2012 7:13am