We are seeing an SSIS process taking too long overnight, and running late into the morning.
The process queries a database table to return a list of files that have been changed since the last time the process ran (it runs daily), and this can be anything from 3 (over a weekend) to 40 during the working week. There are possibly 258 Excel files (all based on the same template) that could be imported.
As I stated, the process seems to be taking too long some days (it's not on a dedicated server) so we decided to look at performance improvement suggestions i.e. increase the DefaultBufferMaxRows and DefaultBufferSize to 50,000 and 50MB respectively for each Data Flow Task in the project. The other main suggestion was to always use a SQL command over a Table or View - each of my Data Flow Tasks (I have nine) all rely on a range name from the spreadsheet - if it might help with performance, what I want to know is - is it possible to select from an Excel worksheet with a WHERE condition?
The nine import ranges vary from a single cell to a range of 10,000 rows by 230 columns. Each of these is imported into a staging table, and then merged into the appropriate main table, but we have had issues with the import not correctly understanding data types (even with IMEX=1), so it seems that I might get a better import if I could select the data differently, and restrict it to only the rows I'm interested in (rather than all 10,000 and then filter them as part of the task), i.e. all rows where a specific column is not blank.
This is just initially an exercise to look into performance improvement, but also it's going to help me going forward with maintaining and improving the package as it's an important process for the business.