CODEWITHSUNDEEP
excelssis
user1486198
user1486198

Reputation: 31

Use heading as column

I have a spreadsheet set out similar to below:

Weekly Report | 25/06/2012

-------------------------------
Name          | Course        |
-------------------------------
Peter         | Maths         |
-------------------------------
John          | English       |
-------------------------------
James         | History       |
-------------------------------

Each week a new report is sent with sometimes different and sometimes the same people on different courses. I want to use SSIS to create an extra column to show the date which is usually in cell b3 of the spreadsheet.

So the final thing would look like

Weekly Report | 25/06/2012

--------------------------------------------
Name          | Course        |   Date     |
--------------------------------------------
Peter         | Maths         | 25/06/2012 |
--------------------------------------------
John          | English       | 25/06/2012 |
-------------------------------------------- 
James         | History       | 25/06/2012 |
-------------------------------------------

Hopefully explained myself there. I am rather new to SSIS so don't know if it's really obvious or something more difficult.

Upvotes: 2

Views: 80

Answers (1)

Pondlife
Pondlife

Reputation: 16240

Thanks for clarifying your question. You have two steps: extracting the date and turning it into a column. I would probably use an Execute SQL task to query the Excel sheet and map the date to a package variable. Then you can use that variable in a Derived Column transformation to add it to the data set being processed.

Finally, you might want to consider not using SSIS at all. If your source is Excel and your destination is MySQL, then using SSIS means you also need SQL Server. So depending on your environment writing your own script or program might be simpler.

Upvotes: 1

Related Questions