Let me preface this by saying I'm relatively new to SSIS, somewhat new to SQL Server, but not new to database work in general -- most of my experience is with PostgreSQL and MySQL. (And the only thing I ever want to do with MySQL again is migrate off it!)
That said, I have inherited an ETL process that I'm updating to do incremental loading. The destination is really a reporting server not a true data warehouse, so the transforms involved are not necessarily what would be used for a star or snowflake schema. Ultimately, it will be fed from multiple data sources, but for now, I'm just working with one.
In the step I'm currently working on, each source data row has (among other columns) a start date, an end date, and columns for week1-week14. The goal is to unroll this so there is one row for each week from start to end (can be 1 week up to 14), with the value from the matching weekN column.
The current, non-incremental, process makes extensive use of staging tables and hand-written SQL in Execute SQL tasks; the SQL Tasks are badly formatted, hard to read, hard to maintain, and just generally complex. I've been switching to using Data Flow components, with Lookups for the incremental load, and up to this step, it's been as fast or faster -- we're not dealing with huge amounts of data here; a couple hundred K rows or less for the most part, so avoiding all the disk IO makes up for a lot.
I've written a Script Task to do the date range expansion, but it's unacceptably slow. Even though it's processing less than 200K rows, it's taking about 7 hours(!!) to complete. I've had a coworker look over the code, and tried a few things to speed it up a bit (like switching from dynamically building the output row based on the columns in the input row to specifying each column individually), but with no luck so far. I've attached the two non-boilerplate functions as they currently exist, but I think the answer is more likely to be "Do [X] instead of the script task" -- I just have no idea what [X] might be, other than switching back to staging tables and Execute SQL tasks, which I'd hoped to avoid.
Any other ideas, or is that my best bet?