SSIS - ETL - SQL Query to SQL Table
The following video demonstrates a workflow for creating an SSIS package loading data from an SQL Query into a target table.This technique can be used to do dimensional modeling. My preference in using this technique is to push as much of the transformation work as possible into the SQL query. Additionally, using this technique saves a great deal of manual work in mapping data types between the source and target systems.
This is done using SSIS 2008, but the concepts apply and can be used with the 2005 and 2012 versions of SSIS as well.