When using a Data Flow Task in Integration Services, try to avoid data manipulations such as unions, sorts and joins using data flow tasks. Try to include all join, sort and union operations within your source queries itself. This way, you can do away with run-time issues that crop up when running your package with large amounts of data. Usually various memory restriction errors.
Then, why do they have all these tasks like Sort, Union All and Merge Join you may ask. Well, you could use them when you need to join or combine data coming from disparate sources such as an Excel file and a SQL Server database, or if you would like to sort data coming from a text file etc.
I just fixed a couple of memory errors and brought down the overall execution time of a certain production package, just by following this practice.🙂