Excel Source in SSIS Column limitations

4
votes

Can we import data from Excel with more than 256 fields in to a SQL database using SSIS. If so is there any trick for this, as I couldn’t see more than 255 columns in the column selection at Excel source in SSIS

4
excelssis
It was not a preview, the excel source has an option to display all of the columns that can be selected for extraction. Went ahead and tried loading the data in to the table, but had null values for all of the columns that wasnt displayed in the Excel source. – reddy r kiran
I assume that since you have more that 256 columns, you are using Office 2007. Did you pick the corresponding connection driver in your excel connection. Previous versions of excel only had a maximum of 256 columns. – William Salzman

4 Answers

1
votes

Yes, I had to save the Excel file as CSV, and then I had access to all 360 columns in my import file. NOTE: Because I wanted to save errors out in the same format, I needed to have my ERRORS file in CSV as well, and the error file had to exist when I set up the connection to it, with only the column headings in the first row, so that all the columns would map automatically in SSIS.

0
votes

If you mean you couldn't see more than 255 rows in the Data Source Component's preview within SSIS then that is expected since I believe that is the most it will show you. Of course all the rows will be imported.

0
votes

As i tried all avenues with no success, i tried converting the excel file in to a tab delimited text and tried the import. It worked superb..

0
votes

you could use two data sources to load the data then join them together as explained in this Post