The new Merge T-SQL command has great potential in a data warehouse scenario and Integration Services is the tool in SQL Server to load the data warehouse so how do they fit together in SQL Server 2008?
The answer is not really and I am not sure they should, so let me explain why.
Integration Services works by extracting data from a source and then the transformations are done in memory before being written out (or loaded) to a target. The in memory transformations have nothing to do with the relational database on either the source or target. Of course you can drop out of integration services and run SQL scripts, but then you are not really using Integration Services.
Merge is a SQL statement, so yes you could put it in a task to use this command, and it might even be faster in certain circumstances, but this is part of the workflow not a transformation. If do this you may want to pick up some logging output so you know that this part of the overall process is working as expected (e.g. are the row counts what you expect)
This is totally different from the ways the predecessor to integration services worked, data transformation services (DTS). In this tool you typically (well I did anyway) used the workflow in DTS to link together SQL views and stored procedures. This is one of many reasons why the move to integration services is hard, you have to completely change the way you use the tool.
For an introduction on when and how to use Merge with Integration Services check this part of SQL Server Books On Line, but to conclude it’s the usual Microsoft situation with lots of options and whenever you ask which one to use the answer is “it depends…”,