none
SSIS Derived Column and Data Conversion Task

    Question

  • I am passing down a variable, filename, as  string and then using Derived Column to pass it down to the destination. The filename is numeric (date) like such: 20101010. So, I need to convert the filename into a date formmat before I pass it to the destination table. Therefor, I am using a data conversion and converting it to a Date format. I tried using both database date or the date format and it keeps failing.

    Here is what the failure report:

    [Data Conversion [42297]] Error: Data conversion failed while converting column "DateAdded" (29685) to column "Copy of DateAdded" (42310).  The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".  

    Any ideas to resolve this issue?

    Thanks


    Kajo
    Monday, November 01, 2010 4:53 PM

Answers

  • Hi,

     

    Would the format of the filename (the date) remain the same always? If so, then in the Derived Column task, you could use the following expression.

    SUBSTRING((DT_STR,10,1252)@[User::MyDate],1,4) + "-" + SUBSTRING((DT_STR,10,1252)@[User::MyDate],5,2) + "-" + SUBSTRING((DT_STR,10,1252)@[User::MyDate],7,2)

    In the above expression, I have used @[User::MyDate] variable that has the date (20101010). You need to replace it with the corresponding variable in your solution.

    What I am doing here is just splitting the Year, the Month and the Date from the entire number 20101010 and concatenating them separating them by a hyphen (-).

    You wouldn't necessarily need to have a Data Conversion task after this Derived Column task; you can directly map this column to the column in your destination table.

     

    Please let us know in case it doesn't work for you.

     

    Cheers,

    Shalin


    Shalin P. Kapadia
    Monday, November 01, 2010 5:24 PM
  • You cannot directly use the data conversion component to convert into date. Before that you have to supply the proper value to the data conversion component as: 2010-10-10. Write an expression in derived column using substring function to create a yyyy-mm-dd format string and use DT_DBDATE to convert it to date as: (DT_DBDATE)(Substring(date,1,4)+"-" + Substring(date,5,2)+"-" + Substring(date,7,2))
    Nitesh Rai- Please mark the post as answered if it answers your question
    Monday, November 01, 2010 5:25 PM

All replies

  • Monday, November 01, 2010 5:21 PM
    Moderator
  • Hi,

     

    Would the format of the filename (the date) remain the same always? If so, then in the Derived Column task, you could use the following expression.

    SUBSTRING((DT_STR,10,1252)@[User::MyDate],1,4) + "-" + SUBSTRING((DT_STR,10,1252)@[User::MyDate],5,2) + "-" + SUBSTRING((DT_STR,10,1252)@[User::MyDate],7,2)

    In the above expression, I have used @[User::MyDate] variable that has the date (20101010). You need to replace it with the corresponding variable in your solution.

    What I am doing here is just splitting the Year, the Month and the Date from the entire number 20101010 and concatenating them separating them by a hyphen (-).

    You wouldn't necessarily need to have a Data Conversion task after this Derived Column task; you can directly map this column to the column in your destination table.

     

    Please let us know in case it doesn't work for you.

     

    Cheers,

    Shalin


    Shalin P. Kapadia
    Monday, November 01, 2010 5:24 PM
  • You cannot directly use the data conversion component to convert into date. Before that you have to supply the proper value to the data conversion component as: 2010-10-10. Write an expression in derived column using substring function to create a yyyy-mm-dd format string and use DT_DBDATE to convert it to date as: (DT_DBDATE)(Substring(date,1,4)+"-" + Substring(date,5,2)+"-" + Substring(date,7,2))
    Nitesh Rai- Please mark the post as answered if it answers your question
    Monday, November 01, 2010 5:25 PM