2

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

4

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.

3
  • 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 Shah 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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