Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm new to SSIS trying to covert data types. I'm pulling data from Google Analytics and loading data into a SQL 2008 database. The date column from Google analytics appears as "DT_WSTR" and the values are in the format "20130402" (year:month:day). I want to change the format to a date format so i can load it into a date field in my database. How do I do this?

Thanks

share|improve this question
add comment

1 Answer 1

You have to use convert DT_WSTR to DT_DBTIMESTAMP. You can find BOL reference here.

for e.g.instead of "Getdate()" use your Date_Column

(DB_TIMESTAMP)((DT_WSTR,4)YEAR(GETDATE()) + RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()),2) + RIGHT("0" + (DT_WSTR,2)DAY(GETDATE()),2) + (DT_WSTR,10) Time_Column)

Also, you can use the Data Conversion transformation, which is probably easier if you are only changing the data type.

EDIT: You can also look for an option of SSIS GoogleAnalyticsSource from codeplex.

share|improve this answer
    
After trial and error i used the derived column transformation with the following expression: "(DT_DATE)(SUBSTRING([ga:date],1,4) + "-" + SUBSTRING([ga:date],5,2) + "-" + SUBSTRING([ga:date],7,2))" Im not sure if its the cleanest way but it works :-) –  resolver101 May 9 '13 at 16:06
    
@resolver101 fair enough. But the options I suggested will also work. –  Kin May 9 '13 at 16:12
    
Thanks for the responce, its apprecitaed. I couldnt see how to following your instructions and needed a quick result. Im fairly new to SSIS. –  resolver101 May 9 '13 at 16:15
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.