May 17, 2011 at 11:45 am
Hi all
Well, I don't know where to start with this one... Here goes anyway...
I am importing an Excel file which has a header row. The header row's columns are in a Custom format (d mmm).
For example (see below for the header row and first line of data):
Resource NameProject 2 May
John Smith#Resource Non Working Time.mpp1.0 d
However, when clicking on the actual '2 May' cell within Excel it shows in the formula bar '02/05/2011'.
Using SSIS, I use a Excel Source task and a OLEDB Destination task to import from Excel and export the data to a database table.
Here is the problem, What is showing in the Destination database table for the headers after exporting are:
Resource Name Project F3
Why it isn't showing '2 May' or better still '02/05/2011' i do not know. I need for the header to show the actual date (02/05/2011) so to be able to use this column in a SQL query further down the line.
Is there something I can do to convert the column, if so how? Can anyone help please as I am clueless on this one???
Thanks in advance.
May 17, 2011 at 1:11 pm
what you could do is write a query to extract the data from excel rather than connecting to the worksheet directly,
if you write a a select * query instead of connecting to the worksheet does the column header come back correctly or does is say F3?
If it says F3 you could re-write the quyery to use an alias for that column like
F3 as '2 May 2011'
May 18, 2011 at 2:11 am
Hi steveb
I am not re-write the query using an alias for the column as each week when the package is run there will be a different date present in that column. If I used alias' I would have to write a new alias each week.
As for using a SELECT to extract the data it has the same result as previously, i.e. the column has F3 in the header row.
I can only see the Custom format is causing problems. I cannot change the Custom format so I need a workaround. Maybe using Conditional Splits and Derived Columns. I'm clueless on how to solve this one.
Help???????
Thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply