none
truncate excel file destination

Respuestas

  • hi wifer87 try this

                CREATE TABLE `No_Tupel` (
        `Kundenname` LongText,
        `Agenturname` LongText,
        `AgenturNr`  Long ,
        `KundenNr`  Long ,
        `Inhaltskomponente`  LongText
    )

    lunes, 19 de marzo de 2012 17:54
  • Hi wifer,

    To truncate data of the excel file, you could drop the sheet and then create the table in one execute sql tast like this -

    DROP TABLE `No_Tupel`
    GO
    CREATE TABLE `No_Tupel` (
        `Kundenname` LongText,
        `Agenturname` LongText,
        `AgenturNr`  Long ,
        `KundenNr`  Long ,
        `Inhaltskomponente`  LongText
    )

    Regards,
    Jerry

    • Propuesto como respuesta Kiran.Y jueves, 22 de marzo de 2012 6:15
    • Marcado como respuesta Jerry NeeModerator jueves, 05 de abril de 2012 8:52
    miércoles, 21 de marzo de 2012 7:20
    Moderador

Todas las respuestas

  • you could use the file system task to delete the file and recreate it first

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    lunes, 19 de marzo de 2012 9:54
  • if i delete the file the mappings in the destination will be no available, its note like flat files.

    lunes, 19 de marzo de 2012 9:59
  • That is not true.
    If you delete an excel file the mappings in the Excel destination won't dissapear.
    However, you need to have a valid Excel file present during the configuration.
    But at runtime, you can delete files and recreate them as often as you like.


    MCTS, MCITP - Please mark posts as answered where appropriate.

    lunes, 19 de marzo de 2012 10:09
  • what command u r using for truncating

    lunes, 19 de marzo de 2012 10:50
  • could you show me the control flow and konfiguration, i tried it, but it no run.

    my Truncate command is:

    TRUNCATE TABLE NOMTACH

    this is my control and data flow

     

    • Editado wifer87 lunes, 19 de marzo de 2012 11:17
    lunes, 19 de marzo de 2012 11:08
  • use excute sql task use  drop table sheetname$


    • Editado v60 lunes, 19 de marzo de 2012 11:43
    lunes, 19 de marzo de 2012 11:33
  • Where did you put the TRUNCATE command? I don't see it in the screenshots.
    Furthermore, TRUNCATE is TSQL syntax and is not supported by Excel. You need to find another way to delete the data.

    (deleting the entire file and recreating it is the easiest option)


    MCTS, MCITP - Please mark posts as answered where appropriate.

    lunes, 19 de marzo de 2012 11:36
  • Look, dirst i delete the destination file and after i delete the destination i recreate a file, is it so right???

    Error: 0xC0202009 at Gabi, Connection manager "Excel Connection Manager 1": SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
    An OLE DB record is available.  Source: "Microsoft Access Database Engine"  Hresult: 0x80004005  Description: "Cannot update. Database or object is read-only.".
    Error: 0xC00291EC at Recret Excel File, Execute SQL Task: Failed to acquire connection "Excel Connection Manager 1". Connection may not be configured correctly or you may not have the right permissions on this connection.
    Task failed: Recret Excel File

    • Editado wifer87 lunes, 19 de marzo de 2012 11:49
    • Propuesto como respuesta AmirAslan martes, 08 de abril de 2014 6:47
    lunes, 19 de marzo de 2012 11:47
  • you should write create table sheetname

    example

    CREATE TABLE `Excel Destination` (
        `EMPNO` Long,
        `ENAME` LongText,
        `JOB` LongText,
        `MGR` Long,
        `HIREDATE` DateTime,
        `SAL` Long,
        `COMM` Long,
        `DEPTNO` Long)

    -----------

    here sheetname is Excel Destination in single quotation



    and file name is when u r creating excel connection manager u should write name and where u want to save it
    • Editado v60 lunes, 19 de marzo de 2012 12:02
    lunes, 19 de marzo de 2012 11:59
  • use this link

    http://social.technet.microsoft.com/Forums/en-US/sqlintegrationservices/thread/00527aef-3ae2-4f41-b196-9ae649ccfe04

    lunes, 19 de marzo de 2012 12:09
  • thank you, so no the data are delete, but i receive an error:

    SSIS package "Gabi.dtsx" starting.
    Warning: 0x0 at Drop Excel Sheet: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
    Warning: 0x0 at Drop Excel Sheet: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
    Information: 0x4004300A at Compare Tupel, SSIS.Pipeline: Validation phase is beginning.
    Warning: 0x800470C8 at Compare Tupel, No Match Tupel [386]: The external columns for component "No Match Tupel" (386) are out of synchronization with the data source columns. The column "F1" needs to be added to the external columns.
    The column "F2" needs to be added to the external columns.
    The column "F3" needs to be added to the external columns.
    The column "F4" needs to be added to the external columns.
    The column "F5" needs to be added to the external columns.
    The external column "Column5" (464) needs to be removed from the external columns.
    The external column "Column4" (463) needs to be removed from the external columns.
    The external column "Column3" (462) needs to be removed from the external columns.
    The external column "Column2" (461) needs to be removed from the external columns.
    The external column "Column1" (460) needs to be removed from the external columns.
    Error: 0xC004706B at Compare Tupel, SSIS.Pipeline: "component "No Match Tupel" (386)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".
    Error: 0xC004700C at Compare Tupel, SSIS.Pipeline: One or more component failed validation.
    Error: 0xC0024107 at Compare Tupel: There were errors during task validation.
    Warning: 0x80019002 at Gabi: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (4) 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.
    SSIS package "Gabi.dtsx" finished: Failure.

    lunes, 19 de marzo de 2012 13:41
  • Your metadata is out of data. You need to refresh it by opening the component and clicking OK.

    MCTS, MCITP - Please mark posts as answered where appropriate.

    lunes, 19 de marzo de 2012 13:50
  • what do you mean, if i drop the table, the columns for the mapping not available because of that i receive this error.

    i think i can solve this problem so, i i first drop the table and than create a table.

    but i reveice error by create a table.

    CREATE TABLE `No Match Tupel` (
        `Kundenname` NVARCHAR(200),
        `Agenturname` NVARCHAR(200),
        `AgenturNr` BIGINT,
        `KundenNr` BIGINT,
        `Inhaltskomponente` NVARCHAR(200)
    )

    lunes, 19 de marzo de 2012 14:20
  • hi wifer87 try this

                CREATE TABLE `No_Tupel` (
        `Kundenname` LongText,
        `Agenturname` LongText,
        `AgenturNr`  Long ,
        `KundenNr`  Long ,
        `Inhaltskomponente`  LongText
    )

    lunes, 19 de marzo de 2012 17:54
  • Hi wifer,

    To truncate data of the excel file, you could drop the sheet and then create the table in one execute sql tast like this -

    DROP TABLE `No_Tupel`
    GO
    CREATE TABLE `No_Tupel` (
        `Kundenname` LongText,
        `Agenturname` LongText,
        `AgenturNr`  Long ,
        `KundenNr`  Long ,
        `Inhaltskomponente`  LongText
    )

    Regards,
    Jerry

    • Propuesto como respuesta Kiran.Y jueves, 22 de marzo de 2012 6:15
    • Marcado como respuesta Jerry NeeModerator jueves, 05 de abril de 2012 8:52
    miércoles, 21 de marzo de 2012 7:20
    Moderador