none
TRUNCATE Zieltabellen RRS feed

 > 

  • Frage

  • Question
    Anmelden
    0
    Anmelden

    Hallo zusammen,

     

    ich hab 4 Datenquellen diese habe ich mit Hilfe des Integration Services extrahieren können und in der Zieldatenbank ( SQL Server ) speichern können. Funktioniert einwandfrei.

    Ich möchte jedoch zusätzlich die Zieltabellen vorher mit Truncate löschen. Das heisst, wenn sich Daten in den Zieltabellen befinden sollen diese Daten mit einen SQL Statement Truncate gelöscht werden.

    Dazu habe ich im Control Flow folgendes gemacht:

     

     

    Ich habe folgende Fehlermeldung nach dem package deploy erhalten:

     

     

    SSIS package "C:\Users\Administrator\documents\visual studio 2010\projects\PraxisprojectDB\PraxisprojectDB\LoadDimArticle.dtsx" starting.

    Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.

    Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.

    Error: 0xC002F210 at Execute SQL Task 1, Execute SQL Task: Executing the query "TRUNCATE TABLE PraxisprojektDB.Dwh.DimArticle;

    TRU..." failed with the following error: "Cannot truncate table 'PraxisprojektDB.Dwh.DimArticle' because it is being referenced by a FOREIGN KEY constraint.".

    Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

     

    Task failed: Execute SQL Task 1

    Warning: 0x80019002 at LoadDimArticle: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

    Information: 0x40043006 at Data Flow Task, SSIS.Pipeline: Prepare for Execute phase is beginning.

    Information: 0x40043007 at Data Flow Task, SSIS.Pipeline: Pre-Execute phase is beginning.

    Information: 0x402090DC at Data Flow Task, Class [2]: The processing of file "C:\Users\Administrator\Desktop\Files\Class.csv" has started.

    Information: 0x402090DC at Data Flow Task, Commodities [22]: The processing of file "C:\Users\Administrator\Desktop\Files\Commodity.csv" has started.

    Information: 0x402090DC at Data Flow Task, Division [82]: The processing of file "C:\Users\Administrator\Desktop\Files\Division.csv" has started.

    Information: 0x402090DC at Data Flow Task, Style [213]: The processing of file "C:\Users\Administrator\Desktop\Files\Style.csv" has started.

    Information: 0x4004300C at Data Flow Task, SSIS.Pipeline: Execute phase is beginning.

     

     

     

    Information: 0x402090DE at Data Flow Task, Class [2]: The total number of data rows processed for file "C:\Users\Administrator\Desktop\Files\Class.csv" is 87.

    Information: 0x402090DE at Data Flow Task, Commodities [22]: The total number of data rows processed for file "C:\Users\Administrator\Desktop\Files\Commodity.csv" is 17.

    Information: 0x402090DE at Data Flow Task, Division [82]: The total number of data rows processed for file "C:\Users\Administrator\Desktop\Files\Division.csv" is 4.

    Error: 0xC02020A1 at Data Flow Task, Style [213]: Data conversion failed. The data conversion for column "Style_Name" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

    Error: 0xC020902A at Data Flow Task, Style [213]: The "Style.Outputs[Flat File Source Output].Columns[Style_Name]" failed because truncation occurred, and the truncation row disposition on "Style.Outputs[Flat File Source Output].Columns[Style_Name]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

    Error: 0xC0202092 at Data Flow Task, Style [213]: An error occurred while processing file "C:\Users\Administrator\Desktop\Files\Style.csv" on data row 220.

    Error: 0xC0047038 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on Style 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.

    Information: 0x40043008 at Data Flow Task, SSIS.Pipeline: Post Execute phase is beginning.

     

     

     

    Information: 0x402090DD at Data Flow Task, Class [2]: The processing of file "C:\Users\Administrator\Desktop\Files\Class.csv" has ended.

    Information: 0x402090DD at Data Flow Task, Commodities [22]: The processing of file "C:\Users\Administrator\Desktop\Files\Commodity.csv" has ended.

    Information: 0x402090DD at Data Flow Task, Division [82]: The processing of file "C:\Users\Administrator\Desktop\Files\Division.csv" has ended.

    Information: 0x402090DD at Data Flow Task, Style [213]: The processing of file "C:\Users\Administrator\Desktop\Files\Style.csv" has ended.

    Information: 0x4004300B at Data Flow Task, SSIS.Pipeline: "OLE DB Destination T1->DimArticle" wrote 3 rows.

    Information: 0x4004300B at Data Flow Task, SSIS.Pipeline: "OLE DB Destination T2->DimArticle" wrote 16 rows.

    Information: 0x4004300B at Data Flow Task, SSIS.Pipeline: "OLE DB Destination T3->DimArticle" wrote 86 rows.

    Information: 0x4004300B at Data Flow Task, SSIS.Pipeline: "OLE DB Destination T4->DimArticle" wrote 0 rows.

    Information: 0x40043009 at Data Flow Task, SSIS.Pipeline: Cleanup phase is beginning.

    Task failed: Data Flow Task

    SSIS package "C:\Users\Administrator\documents\visual studio 2010\projects\PraxisprojectDB\PraxisprojectDB\LoadDimArticle.dtsx" finished: Failure.

    The program '[4016] DtsDebugHost.exe: DTS' has exited with code 0 (0x0).

     

     

     

     

    Was kann man machen, um das Problem zu entgehen ?

     

    LG

    Kalle

    Sonntag, 22. Januar 2012 17:29
    |

Antworten

  • Question
    Anmelden
    2
    Anmelden

    Error: 0xC002F210 at Execute SQL Task 1, Execute SQL Task: Executing the query "TRUNCATE TABLE PraxisprojektDB.Dwh.DimArticle;

    TRU..." failed with the following error: "Cannot truncate table 'PraxisprojektDB.Dwh.DimArticle' because it is being referenced by a FOREIGN KEY constraint.".


    Hallo Kalle,

    der Befehl Truncate Table unterliegt bestimmten Einschränkung, siehe http://msdn.microsoft.com/de-de/library/ms177570.aspx => Einschränkungen.

    Eine davon ist eben, das keine Foreign Key Constraints in der Tabelle vorhanden sein dürfen, was bei Dir aber der Fall ist.

    Hier kannst Du also nur das Äquivalent "DELETE FROM DeinTabelle" nutzen.

    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing
    Sonntag, 22. Januar 2012 17:39
    |

Alle Antworten