DTS Excel transform reading beyond last row

Discussion in 'microsoft.public.sqlserver.dts' started by stainless, Aug 18, 2010.

  1. stainless

    stainless Guest

    SQL Server 2000

    I have a DTS Package that is reading an Excel file and, using a
    Transform, populating a SQL table with simple Copy transformations. It
    is failing as it reads beyond the last populated row in the Excel
    worksheet and thus tries to populate one of the table fields with the
    null value it finds there.

    The Excel file itself is a more recent format than the latest type
    available in DTS (Microsoft Excel 97-2000 in DTS) and I have defined
    the default value of 0 in both the First Row and Last Row value of the
    Transform Data Task Properties.

    I know the actual data is ok as, if I alter these to 1 and 22401 (the
    actual number), the package works and the data is loaded.

    The issue is that the file will change from week to week and so I
    cannot give exact values for the number of rows. The process has to
    read all rows available.

    What can I do to ensure the process stops at the last row populated?

    Cheers

    Mark
     
    stainless, Aug 18, 2010
    #1
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.