I am very new to SSIS and need to work out how to setup a transfer from table A database A to table B database B.

table A contains many more fields than I need in table B, so the process needs SQL so I can specify fields to take. (I cant just lift the table from database A and drop it into database B).

If I were doing it in SQL I would just select field1, field2, field3 from table a and update table b with the results.

Can anyone offer me any pointers on how to do this in SSIS? I have had a good google around but can't find a definitive answer.

Many thanks

migrated from stackoverflow.com Oct 12 '12 at 16:59

This question came from our site for professional and enthusiast programmers.


Create regular data flow with 2 components - OLE DB Source and OLE DB Destination (I assume you are using MS SQL Server, in general, use whatever components your company uses to connect to the DB).

In case of 2 DBs, create 2 connection managers, each pointing to its DB. Point OLE DB Source to first connection manager configured to point to source of data, and OLE DB Destination to second connection manager configured to point to destination DB.

Now point OLE DB Source to the source table in source DB, leave all the fields intact. Connect source and destination components with green arrow originally going out of source component. Now point OLE DB Destination to the destination table in target DB. Double-click destination, go to mappings and make sure they are correct (SSIS tries to map automatically using strick name matching), otherwise (in case names are different) connect source and destination fields manually. That's it, you just don't provide mappings for the fields which cannot be accommodated by destination table.

Alternatively, you can leave out the columns you don't need at source component - double-click it, go to Columns and uncheck columns you don't need.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy