Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Dates and Times via SSIS Expand / Collapse
Author
Message
Posted Tuesday, July 23, 2013 8:09 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:52 AM
Points: 228, Visits: 464
I do apolagise for all these questions.

I have a staging database. This database has a table - "table1". I have a few columns but two that I want to transfer.
ID = int
planned_start_date = datetime.

Sample of the data is -
id - 1875
planned_start_date - 2012-06-13 12:00:00.000

What I want to do is transfer it to the datawarehouse database as the following -
"table2"
id - 1875
planned_date -2012-06-13
planned_time - 12:00:00.000

So keep the date and time seperate?

Any ideas please are welcome.

Thanks
Post #1476599
Posted Tuesday, July 23, 2013 8:29 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:52 AM
Points: 228, Visits: 464
I think I have worked it out.

Created a table in my datawarehouse called 'table2', with columns -
id - int
date - datetime
time - time(7)

The in my SSIS Data Flow Task I have a "Data Conversion" task inbetween my source and destination.

I've then added the input colum of "planned_start_date" twice, one with an output alias of "date" and one of "time" with the datatype of database date and database timestamp respectively and it appears to do what I want it to.

Thanks
Post #1476612
Posted Tuesday, July 23, 2013 1:54 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 13,562, Visits: 11,372
The only change I still would made is change the data type of the first column from datetime to date.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1476781
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse