Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Error with Derived Column component in SSIS...

Unanswered Error with Derived Column component in SSIS...

  • Thursday, January 31, 2013 12:25 AM
     
     
    Sign In to Vote
    0

    Hi everyone,

    I created an ssis package and in that package I read a CSV file and transform it in a table.

    CSV file includes 4 columns that one of them has value same as 21/01/2013 (name of coulmn is Call_Date)

    My table needs varchar(30) with this format 2013-01-21 as value of that column.

    for this reason I used a derived column between flat file source and OLE DB destination and I added new column with below specifications:

    Derived Column Name : Call_Date  

    Derived Column : <add as new column>

    Expression:  ((DT_WSTR,4)YEAR(((DT_DATE)Call_Date))) + "-" + RIGHT("0" + ((DT_WSTR,2)MONTH(((DT_DATE)Call_Date))),2) + "-" + RIGHT("0" + ((DT_WSTR,2)DAY(((DT_DATE)Call_Date))),2)

    Data type: string [DT_STR] 

    lenght: 30   

    Code Page:  1252  (ANSI - Latin I)

    This package works fine most of times but for some CSV file I get below error in derived column component , and when I create a new CSV file and change format cell to text and copy and past that file datas to new one , it works again.

    The error is :

    [Derived Column [56446]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "component "Derived Column" (56446)" failed because error code 0xC0049064 occurred, and the error row disposition on "output column "Call_Date" (57282)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.

    Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Derived Column" (56446) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.

    Error: SSIS Error Code DTS_E_THREADFAILED.  Thread "WorkThread0" has exited with error code 0xC0209029.  There may be error messages posted before this with more information on why the thread has exited.

    I do not understand what is eactly the issue.

    I will appreciated for any help.

    Cheers,

    Emma

     

All Replies

  • Thursday, January 31, 2013 12:37 AM
     
     
    Sign In to Vote
    0
    Redirect your errors from the derived column block to a flat file so that you can see the data that is failing.  Chances are that you have some data that won't convert to a valid DT_DATE

    Chuck Pedretti | Magenic – North Region | magenic.com

     
  • Thursday, January 31, 2013 1:01 AM
     
     
    Sign In to Vote
    0

    Thanks Chunk,

    Could you please explain more how can I redirect the error to flat file . I am new in this case :)

    Cheers,

    Emma

     
  • Thursday, January 31, 2013 1:09 AM
     
     
    Sign In to Vote
    1

    Here is a decent tutorial.  I'd recommend you buy a decent book as well so  that you can get the basics down.

    http://sqlserver360.blogspot.com/2011/03/error-handling-in-ssis.html

    Chuck Pedretti | Magenic – North Region | magenic.com

     
  • Thursday, January 31, 2013 1:39 AM
     
     
    Sign In to Vote
    0

    Thanks a lot Chuck,

    The link is very useful. I redirected the Call_Date column and I see 992 rows in new file that it is equal to original file. it means SSIS thinks all rows have bad data.

    the value for all rows in Call_Data column are the same and it is : 9/01/2013 

    It looks like can convert to DT_Date but it can not.

    Any suggestions?

    Cheers,

    Emma

     
  • Thursday, January 31, 2013 2:14 AM
     
     
    Sign In to Vote
    0

    Just did a little test and your expression looks fine for that data.  I get a result of '2013-09-01'

    Although now that I'm looking at your initial post - you defined your new column as DT_STR and cast all your intermediate strings to DT_WSTR (Unicode)

    Try changing your expression to :

    (DT_STR, 20, 1252) (((DT_WSTR,4)YEAR(((DT_DATE) Call_Date))) + "-" + RIGHT("0" + ((DT_WSTR,2)MONTH(((DT_DATE)Call_Date))),2) + "-" + RIGHT("0" + ((DT_WSTR,2)DAY(((DT_DATE)Call_Date))),2))

    Chuck Pedretti | Magenic – North Region | magenic.com

     
  • Thursday, January 31, 2013 2:35 AM
     
     
    Sign In to Vote
    0

     

    I tried below expression instead of myself but still I get the same error.

    (DT_STR, 20, 1252) (((DT_WSTR,4)YEAR(((DT_DATE) Call_Date))) + "-" + RIGHT("0" + ((DT_WSTR,2)MONTH(((DT_DATE)Call_Date))),2) + "-" + RIGHT("0" + ((DT_WSTR,2)DAY(((DT_DATE)Call_Date))),2))

     
  • Thursday, January 31, 2013 4:51 AM
     
     
    Sign In to Vote
    0

    Hi Emma,

    I have doubt on expression as well. I created FF with format dd/mm/yyyy. As per screen shot I do not get correct yyyy-mm-dd format result for 2nd row.

    Thanks, hsbal


    • Edited by Harry Bal Thursday, January 31, 2013 4:52 AM
    •  
     
  • Thursday, January 31, 2013 9:42 PM
    Moderator
     
      Has Code
    Sign In to Vote
    0

    21/01/2013 => 2013-01-21

    What's the datatype of Call_Date in your Flat File Connection Manager? If it's a string and the destination is also a string then you could also try something like:

    SUBSTRING([Call_Date],7,4) + "-" + SUBSTRING([Call_Date],4,2)  + "-" + SUBSTRING([Call_Date],1,2)

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

     
  • Friday, February 01, 2013 8:49 AM
     
     
    Sign In to Vote
    0

    There are multiple things you need to check.

    1: Open the file in notepad and not in excel to check the date column. Are the dates valid dates?

    2: Check if the date you are getting is in dd/mm/yyyy format or mm/dd/yyyy format. The first is the wrong format, in case its in that format you will have to change your expression. You need to split the date column based on "/" and then manipulate the data.

    My Blog    |      Ask Me     |      SSIS Basics     

     
  • Friday, February 08, 2013 1:36 AM
     
     
    Sign In to Vote
    0

    Thanks all for your reply and so sorry for my delay,

    Sudeep as you mentioned , I opened the flat file with notepad , all the date column value seems fine.

    But for second step, I saw th edate is with dd/mm/yyyy format, could you please let me know what is wrong in my expression with this format?

    Cheers,

    Emma