SQL Server Performance

Error output Redirect Row - Not working

Discussion in 'SQL Server 2005 Integration Services' started by shivi, Sep 26, 2007.

  1. shivi New Member

    Hello,
    I am trying to import a text file to sql server.
    Flat file has date, text and int columns.
    There is an erroneous row where the text qualifier is messed up

    600017242,"Commerce Group, Inc. (The)","N00294","Americas","CGI","INSURANCE","INSURANCE: PROPERTY & CASUALTY"
    815072158,"Commercial Bank ""Renaissance Capital"", LLC","","EMEA","","BANKING","NON-U.S. BANK"
    here the first row is correct but in the second row as the quotes are messed up ssis keeps failing with the message that LLC is missing the text qualifier.
    So I used the error output - redirect row option first (this did not work)
    I also tried ignore failure option. But both are not working and the package keeps failing.
    Please let me know what is wrong with this.

    Regards.
    Shivi

    SSIS package "MOODYS_DTSTask.dtsx" starting.
    Information: 0x4004300A at issr_idmap, DTS.Pipeline: Validation phase is beginning.
    Information: 0x40043006 at issr_idmap, DTS.Pipeline: Prepare for Execute phase is beginning.
    Information: 0x40043007 at issr_idmap, DTS.Pipeline: Pre-Execute phase is beginning.
    Information: 0x402090DC at issr_idmap, Flat File Source [1]: The processing of file "\ln1vserverd$InetpubwwwrootMISQAMISBatchServicesMoodysTXTmir_issr_idmap.txt" has started.
    Information: 0x402090DC at issr_idmap, Flat File Destination [932]: The processing of file "error_issr.txt" has started.
    Information: 0x4004300C at issr_idmap, DTS.Pipeline: Execute phase is beginning.
    Error: 0xC0202055 at issr_idmap, Flat File Source [1]: The column delimiter for column "mdy_spec_ind" was not found.
    Error: 0xC0202092 at issr_idmap, Flat File Source [1]: An error occurred while processing file "\ln1vserverd$InetpubwwwrootMISQAMISBatchServicesMoodysTXTmir_issr_idmap.txt" on data row 1496.
    Error: 0xC0047038 at issr_idmap, DTS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Flat File Source" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
    Error: 0xC0047021 at issr_idmap, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
    Error: 0xC0047039 at issr_idmap, DTS.Pipeline: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.
    Error: 0xC0047039 at issr_idmap, DTS.Pipeline: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.
    Error: 0xC0047021 at issr_idmap, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
    Error: 0xC0047021 at issr_idmap, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread1" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
    Information: 0x40043008 at issr_idmap, DTS.Pipeline: Post Execute phase is beginning.
    Information: 0x402090DD at issr_idmap, Flat File Source [1]: The processing of file "\ln1vserverd$InetpubwwwrootMISQAMISBatchServicesMoodysTXTmir_issr_idmap.txt" has ended.
    Information: 0x402090DF at issr_idmap, OLE DB Destination [46]: The final commit for the data insertion has started.
    Information: 0x402090E0 at issr_idmap, OLE DB Destination [46]: The final commit for the data insertion has ended.
    Information: 0x402090DD at issr_idmap, Flat File Destination [932]: The processing of file "error_issr.txt" has ended.
    Information: 0x40043009 at issr_idmap, DTS.Pipeline: Cleanup phase is beginning.
    Information: 0x4004300B at issr_idmap, DTS.Pipeline: "component "OLE DB Destination" (46)" wrote 0 rows.
    Information: 0x4004300B at issr_idmap, DTS.Pipeline: "component "Flat File Destination" (932)" wrote 0 rows.
    Task failed: issr_idmap
    SSIS package "MOODYS_DTSTask.dtsx" finished: Success.
  2. dineshasanka Moderator

    Are you using fast load. You can not redirect error record if it is fast load. Change to some other mode

Share This Page