locked
SSIS Flat file Date Conversion RRS feed

  • Question

  • Environment:
    SQL 2005 DB
    SSIS Package
    Flat file (csv)

    FlatFile -->OLE DB Destination


    Situation:
    FlatFile example row:
    "LocalSales","S","N.L","24 NOV 2008"

    Problem:
    The problem is with the date column, as you can see the date in the file is "24 NOV 2008", the database column is set as (Smalldatetime,Null).

    My other SSIS package which works with a flat file with the date as 24-NOV-08 works file with the same column type.

    Therefore the only differance I can see is that its missing the - between the dates. and uses " " for column separators



    Do I need to use Derived Column to convert the column?

     

     

    Wednesday, November 26, 2008 9:29 AM

Answers

All replies

  • the derived column transformation component should work...provided that the correct ssis expression is employed.  another option is to employ the script transformation component.

     

    hth

    Wednesday, November 26, 2008 9:38 AM
  • Thanks for the suggestion.

    I cannot change the FlatFile in anyway so both options are good.

     

    I am new to SSIS so which would be the easiest to employ?

    And is there any good tutorials on how I can go about doing this?

    Wednesday, November 26, 2008 9:46 AM
  • The error im currently getting is:

    Error: 0xC0202009 at ImportFiles, Insert into Profit [3782]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.

    An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification".

     

    Error: 0xC020901C at ImportFiles, Insert into Profit [3782]: There was an error with input column "Column 3" (3799) on input "OLE DB Destination Input" (3795). The column status returned was: "The value could not be converted because of a potential loss of data.".

     

    Error: 0xC0209029 at ImportFiles, Insert into Profit [3782]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "OLE DB Destination Input" (3795)" failed because error code 0xC0209077 occurred, and the error row disposition on "input "OLE DB Destination Input" (3795)" 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: 0xC0047022 at ImportFiles, DTS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Insert into Profit" (3782) 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: 0xC0047021 at ImportFiles, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread3" has exited with error code 0xC0209029. There may be error messages posted before this with more information on why the thread has exited.

     

    Wednesday, November 26, 2008 9:51 AM
  • Please can someone provide some information on how i can resolve this.

    Some tutorials would be good!

     

    thanks

    Wednesday, November 26, 2008 12:28 PM