Bertil Forsberg: I've learnt something new today! every day is a school day. 🙂

fszendzielarz: Partitioning for me was not an option as we are only using SQL Server Standard Edition - Partitioning is an Enterprise edition feature. But also, the transaction types that I refer to in the article represent different information that ideally should have been designed as different tables within a database anyway (source is a 3rd party system).

106600.3363: Potentially, yes it could be done via T-SQL. I think you touch on a interesting point. Personally, I've used SSIS for a number of reasons:

1 - visually you can sort of see what is going on (albeit there is some slightly more hidden details, such as defining the connection string

2 - secondly, this is part a much larger project and the production package I use, contains several other package run related variables and a few additional task.

3 - SSIS offers better event handling functionality. (again some of this could be done in SQL) ...... horses for courses