February 16, 2006 at 1:48 pm
Working with an archaic mess of Access dbs, and am trying to convert to a single SQL Server 2005 db.
A couple of places in the original monstrosity I have the time and date in separate columns, like the following example:
[Ticket Date] [Ticket Time]
12/1/99 0845
12/2/01 0932
One problem is the two columns can be of different data types, like a date col and a text time. If anyone knows how to bring these into a single column using SSIS, I would love to know since I have been battling with it for a while.
Thanks in Advance
February 17, 2006 at 8:17 am
February 17, 2006 at 11:04 am
I can't believe you've got me working in Access to try this out, but here goes...
I did it by simply converting the data for both columns to a string, then creating a derived column that uses the expression: (DT_DBTIMESTAMP)([Copy of TicketDate] + [Copy of TicketTime])
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 17, 2006 at 12:50 pm
For those who care, that is essentially how I solved this problem.
In SSIS, this means I used a Data Conversion task to convert both, then a Derived Column to combine them, and another Data Conversion to transformt them back into a date.
If anybody knows a better way to do this, please let me know.
February 17, 2006 at 2:57 pm
Chris,
you can do all of the data conversions in the derived column component so you only actually need one component rather than three.
-jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply