Post reply

Replacing data in Excel Destination File

  • July 13, 2008 at 1:23 pm

    #184604

    I have a package that takes data out of SQL Server 2005, creates a pivot table and puts it into an Excel worksheet that is part of a large workbook that backfills data into a weekly report. The data is being appended in the target worksheet, even though I've deleted the previous week's data. I've tried using a File System Task ahead of the control flow task, but it deletes the whole workbook, not just the intended worksheet. Any solutions? I couldn't find a property in any of the property pages to replace data instead of appending it, but surely it exists somewhere?

  • July 13, 2008 at 7:38 pm

    #842161

    Do not know if this will be the answer but learned this from Jeff Moden, when I had the same problem using OPENROWSET. Jeff pointed out how to specify the starting location for the data insertion.

    SELECT *

    FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

    'Excel 8.0;Database=C:\Temp\OpenRowSetTest2.xls;HDR=yes',

    'SELECT * FROM [Sheet1$a4:c]']])

    Note the specification of the starting row and colum.

    Try it using the starting row and colum immediately after the sheet name, and let us all know if this solves your problem

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • July 18, 2008 at 6:55 am

    #844983

    Thanks for the suggestion. I noticed there was an OpenRowSet property in the property page for the Excel Destination object. It already had the name of the worksheet in it (Worksheet1$), so I added a reference to the first row of cells immediately after the $. It worked! Thanks again!

Viewing 3 posts - 1 through 3 (of 3 total)

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