SSIS 14 - staging area - merging two sources takes a long time

If both source tables are on the same server, do not use this method. You should just write your query on the SQL Server side.

Something like that:

SELECT * 
FROM [Table A]
INNER JOIN [Table B] ON [Table A].ID  = [Table B].ID 
ORDER BY ... 

      

        
        
        
      

    

As James Serra said: When to Use T-SQL or SSIS for ETL

• Performance. With T-SQL, everything is handled in the SQL engine. With SSIS, you move all data into the SSIS memory space and do the manipulation there. So if speed is an issue, T-SQL is usually the way to go, especially when dealing with a lot of records. Something like JOIN in T-SQL will be much faster than using SSIS search tasks. Another example is the MERGE statement in T-SQL has much better performance than the SCD task in SSIS for large tasks.
• Features / Capabilities. Some functions can only be performed in T-SQL or SSIS. You can trim text in SSIS, but not in T-SQL. For example, text files with an inconsistent number of fields per line can only be executed in SSIS. Therefore, certain tasks can force you to use one or another
• Current Skillset - Are your IT staff familiar with SSIS or T-SQL?
• Ease of development / maintenance. Of course, whatever you are most familiar with will be the easiest, but if your skills on both are fairly even then SSIS is usually easier to use because it is graphical, but sometimes you can develop faster in T-SQL. For example, joining a bunch of tables would require a lot of tasks in SSIS, where in T-SQL it is one statement. So it may be easier to create tasks to join tables in SSIS, but it will take longer to build than writing a T-SQL statement
• Complexity - SSIS can be more complex because you may need to create many tasks to achieve your goal, where in T-SQL it can only be one statement like in the example above for joining tables.
• Extensibility. SSIS has better extensibility as you can create a script task that uses C # that can do anything, especially for non-database tasks. T-SQL is limited because it is only for database tasks. SSIS also has logging which T-SQL does not
• The likelihood of degradation / breaking changes is a minor issue, but T-SQL always removes features in each version that need to be rewritten.
• Source and Destination Types / Architecture - SSIS is best if you have multiple source types. For example, it works great with Oracle, XML, flat files, etc. SSIS was designed from the beginning to work well with other sources where T-SQL is for SQL Server and requires additional steps to access other sources and there are additional restrictions
• Local Regulations - Are there any company standards that you must adhere to that limit which tool you can use?