Post reply

delete rows from excel file in SSIS

  • May 5, 2010 at 9:19 am

    #1162553

    Try encasing the table name with [] i.e. [Sheet$1]

  • March 23, 2011 at 12:08 pm

    #1302650

    I am new to SSIS so could you please give more detail in how to execute step 1 using the file task flow?

    Thanks

    Chris

  • April 25, 2011 at 2:59 am

    #1315326

    Hi mdawson,

    I dont see when u had posted this answer, but its working fine for me,,thanks a lot.

    Mallik

  • April 26, 2011 at 12:58 pm

    #1316129

    This is an annoying problem. I've simply given up, dealing with the issue this way:

    - For every Excel file I will need to repeatedly generate, I create a template file, complete with formatting and headers.

    - The SSIS then deletes the existing output file, uses a file system task to copy the template file to a new output file, and then write out to the output file.

    It's a kluge, but it works....

    Rich

  • September 19, 2011 at 10:10 am

    #1383386

    Thanks for your proposed solution - it got me moving in the correct direction.

    I changed it up just a little for me.

    Instead of deleting the file, I used the 'copy file' and just overwrote the file with a template.

  • January 20, 2016 at 3:46 pm

    #1853331

    works perfect, thank you.

  • July 11, 2017 at 10:52 am

    #1950405

    eric.donofrio - Thursday, February 19, 2009 10:38 AM

    You can configure a SQL Task against an Excel connection object...so, I'm trying something like "DELETE * FROM SHEET1$" (the "*" is needed b/c the connection manager uses Jet). I'd think that this would work, but I keep getting an error message:[Execute SQL Task] Error: Executing the query "DELETE FROM Sheet1$" failed with the following error: "Syntax error in FROM clause.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. I've tinkered with the query a bit, but still getting the same error msg

    Please help

  • July 14, 2017 at 2:35 pm

    #1951010

    mdawson - Friday, March 21, 2008 6:23 PM

    ok, just figured it out for what i needed, in my previous testing it kept appending - pain in my butt.initially, all i had on my control flow was an export task, the data flow of that was just two datasources, sql exporting to excel (after testing i found it was appending).i had to:1. create a file system task in control flow, deleting the file.2. create a sql task, excel connection type, i used my previous excel connection for this, then used a create table script as my sql statement3. left my previous export object the same, then linked the flow of these in this order described.hope this helps anyone else out there.i've read of other examples out there that would delete rows, and leave white space in the excel files. not what i would have wanted for a solution either.and to me, a simple task such as this should not have been so hard to find information on, or that hard to figure out - but it was for me. what gives msft?

    Let's hope nothing goes wrong between steps 1 and 2 or POOF!... your data is gone. 😉  My recommendation would be to create a new file and, if everything else is successful in populating the new file, then delete the old file and rename the new file.

    And, agree... MS is sometimes like a hardware store.  Lot's of goodies but if you don't know how to use that axe you just bought, you'll find that it doesn't come with instructions.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply