locked
How do I perform data type conversions in SSIS? RRS feed

  • General discussion

  • [This is just for sharing information for those common asked questions collected from forums. If you have any better way or feedback, please directly reply in this thread so that community users can benefit from your answers.]

    Question:

    I need to transfer my data from one server to another server, but sometimes the matching destination columns do not have same data types as the source columns. For example, I have a data column with numeric data type with the format YYYYMMDD  in a source table but the data type of the corresponding column in the destination table is datetime.

     

    Answer:

    If you are familiar with T-SQL, you can compose a SQL command text for your data source like OLE DB Source. For example:

    SELECT CONVERT(DATETIME,CAST(MY_DATE_COLUMN as varchar(10)),112) as 'MY_DATE_COLUMN' FROM MyTable

     

    If you do not want to compose T-SQL statement but just use a table as the source, in SSIS you can use a Derived Column to perform the data type conversion, for example:

     

    (DT_DBDATE)((DT_WSTR, 4)(ROUND([MyDateColumn] / 10000, 0)) + "-" + (DT_WSTR, 2)(ROUND([MyDateColumn] / 100, 0) % 100) + "-" + (DT_WSTR, 2)([ MyDateColumn] % 100))


    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Friday, June 18, 2010 9:44 AM

All replies