locked
SSIS convert function? RRS feed

  • Question

  • Hi Experts,
    I am fetching the data from the database. Where I have a date column named "JoinDate" and format is like
    2010-01-27 12:3:34.000.

    I need the output as 20100127 in output.

    Currently I am doing this in SQL query (convert function) in OLE DB Source.

    My question is, How can I achieve the same in SSIS, is there any short cut or direct function like convert in SQL?
    Tuesday, February 2, 2010 10:30 AM

Answers

  • Hi,
    You can do it in the Derived Column transform however there is no method in there that is as simple to use as T-SQL's CONVERT()  (which is not to say that its particularly difficult in SSIS - it isn't).

    Why do you want to do this in the data flow? If you're able to do it in the source adapter then that is perfectly adequate.

    -Jamie
    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    • Marked as answer by Rocky Iyan Tuesday, February 2, 2010 11:17 AM
    Tuesday, February 2, 2010 10:39 AM
  • Both Sudeep and Jamie are correct, there are 3 ways to do it in SSIS
    1> Use a Derived column with the following expression
    ((DT_WSTR, 4)YEAR([JoinDate]))  + (RIGHT("0" + (DT_WSTR, 2)MONTH([JoinDate]), 2))  + (RIGHT("0" + (DT_WSTR, 2)DAY([JoinDate]), 2))

    2> use a Script component

    3> this is what you are doing right now. write a SQL command.

    Thanks-
    Let us TRY this | Don’t forget to mark the post(s) that answered your question

    http://quest4gen.blogspot.com/
    • Marked as answer by Rocky Iyan Tuesday, February 2, 2010 11:17 AM
    Tuesday, February 2, 2010 11:06 AM

All replies

  • Hi,
    You can do it in the Derived Column transform however there is no method in there that is as simple to use as T-SQL's CONVERT()  (which is not to say that its particularly difficult in SSIS - it isn't).

    Why do you want to do this in the data flow? If you're able to do it in the source adapter then that is perfectly adequate.

    -Jamie
    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    • Marked as answer by Rocky Iyan Tuesday, February 2, 2010 11:17 AM
    Tuesday, February 2, 2010 10:39 AM
  • Jamie is correct, if you can get the data from the OLEDB Source(modify ur query to get the appropriate date column)
    Else use derived column with SUBSTRING function to get the date in ur desired format.
    Hope this helps !!
    Sudeep   |    My Blog
    Tuesday, February 2, 2010 10:56 AM
  • Both Sudeep and Jamie are correct, there are 3 ways to do it in SSIS
    1> Use a Derived column with the following expression
    ((DT_WSTR, 4)YEAR([JoinDate]))  + (RIGHT("0" + (DT_WSTR, 2)MONTH([JoinDate]), 2))  + (RIGHT("0" + (DT_WSTR, 2)DAY([JoinDate]), 2))

    2> use a Script component

    3> this is what you are doing right now. write a SQL command.

    Thanks-
    Let us TRY this | Don’t forget to mark the post(s) that answered your question

    http://quest4gen.blogspot.com/
    • Marked as answer by Rocky Iyan Tuesday, February 2, 2010 11:17 AM
    Tuesday, February 2, 2010 11:06 AM
  • Why do you want to do this in the data flow? If you're able to do it in the source adapter then that is perfectly adequate.
    -Jamie
    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

    Hi Jamie,
    I am new to SSIS and eager to know different ways to doing a single task (for the sake of learning SSIS). :)


    Hi ETL,
    your expression for the derived column is working perfectly.


    Thanks everyone for sharing your experience.

    Tuesday, February 2, 2010 11:17 AM