SSIS Adding column to excel sheet template destination when importing from SQL Table Source

Question

I have an excel sheet template that has 3 columns. I also have a SQL server table that has the same 3 columns plus an additional 4th column. I need to import all 4 columns from the SQL table into the excel spreed sheet template. My problem is that when I try to MAP the columns in SSIS I can only map the 3 columns that the Table and excel sheet have in common. How can I have SSIS import all 4 columns and not just the 3 columns that the source and destination have in common? Is there a way I can have SSIS add the additional field column to my excel spread sheet destination?

Answer 1

When you do Export data from the database (using the wizard - Import\Export)

select -- write a query to specify data transfer.

enter image description here

If you are creating package from scratch using BIDS, then in the execute sql task editor, in the sql statement

enter image description here

then in the data flow task in the source query specify T-SQL query as below :

enter image description here