Import big excel file using excel connection manager in SSIS

0
votes

Situation:

Right now I have several excel files that need to be populated to databases. Like usual, first I tried to import files using import task in SSMS, but it failed when I tried to edit the mapping and proceed to the next step. (Here I am guessing it is because of the size of excel that cannot process in the cache?), then I switch to using SSIS, but still, the excel connection manager does not allow me to preview or just finish connecting to the file. (after few minutes, it give me error says source does not exist?)

I tried to break the file into small size, it worked.

Here are my question :

  1. what is the max number of rows that SSIS or SSMS could pre-load the file from excel?

  2. Instead of breaking the big file into small piece (not sure what is the exact size I need to distribute each time), is there any other way to import big size excel file? Because it is kind of not doable when having lots of files.

Thanks

1
excelssisbusiness-intelligencessis-2012

1 Answers

1
votes

SSIS does not limit the number of rows it can import from any particular source (Unless you are using some sort of constraint). By default, the preview usually gives about 200 rows only and I dont know of a way to change it.

Although you can change the number of rows you can use to query the metadata to probably 10000 but for preview, it is pretty standard.

What is the version of excel that you are importing data from and what is your SSDT version that you are using? What is the maximum number of rows you have encountered on your biggest excel files?