First Time SSIS Long Time DTS

A few pointers would be appreciated.

I am looking at building multiple SSIS packages. There will be some similarities. Flexibility is of highest importance. The main packages will need to connect to SQL Server1 as a source and SQL Server2 as a destination to transfer over dimenion data from multiple databases. (other SSIS packages may need to use SQL Server2 as a source and SQL Server1 as a destination)

For a single dimension table containing column dim_id on the target server (SQLServer2). I need to pass the results of the following SQL and insert into SQLServer2.database.dim_table

select dim.id
from SQLServer1.database08.dim_table
union
select dim.id
from SQLServer1.database07.dim_table
union
select dim.id
from SQLServer1.database06.dim_table


Now next year the names of the databases on SQLServer1 will be database09,database08,database07!

Now so far my best thought is creating views in my destination SQL Server. So I need some way of dropping and recreating the views. Previously in DTS I would expect to see SQL Server connection that I could use as source and destination. Now I can see SQL Server destination but not source? Also How do I just use SSIS to run some SQL. i.e execute a stored procedure, drop and creat views?

Many thanks,
Ells
p.s Flexibility is the key, in the last three months all the ip and server names have changed more than once so need to be as flexible as possible.