Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We receive an Excel file weekly currently containing about 250k rows. The file grows by about 300 rows each week.

In SSIS I've hooked up an Excel source to an OLE DB destination to import the file but it takes a long time.

I've looked at using the SQL Command to specify how to select part of the sheet but I can't see how to turn this in to a parallel operation.

Is it possible in SSIS to split the Excel file (by rows) and import it in parallel?

share|improve this question
add comment

2 Answers 2

I would Export the Excel file to a Text file (e.g. tab-delimited) and load it into SSIS in that format. It will be much quicker and less subject to odd errors which sometimes come up when reading Excel files.

I would expect any attempt at parallel Excel imports to be slower than a single import - Excel is not a server.

share|improve this answer
add comment

In SSIS I've hooked up an Excel source to an OLE DB destination to import the file but it takes a long time.

There are three destination adapters for doing this:

  • OLE DB Destination
  • OLE DB Destinaton with FastLoad
  • SQL Server Destination

OLE DB Destination is the slowest option and SQL Server Destination is the quickest. Refer here and here for more details.

Below is what you can do :

  1. Use fast load option on OLE DB destination and experiment with batchsize option and find your optimum number.
  2. Consider dropping the indexes on target table and recreate them after loading the data. For large amount of data, dropping and recreating indexes is faster than inserting with indexes.
  3. If the package is running on destination server itself, use SQL Server destination transform. Its considerably faster than anything else.
  4. Since Integration Services is all about moving large amounts of data, you want to minimize the network overhead. This means that the value 32K (32767) is the fastest option. DO NOT DO IT AT Server Level using sp_configure, instead adjust in the connection manager properties as described in the best practice article (Tuning your network part).

As a side note you can refer to Top 10 SQL Server Integration Services Best Practices by SQL CAT team.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.