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

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics