We have observed a common issue which is faced by many of our  customers who export the result set from DTS package to excel worksheet. DTS package that exports results to an existing excel workbook appends the data  each time you run it.  Manually deleting the contents of the sheet and running the DTS job still appends


The OLEDB drivers does not allow you to delete worksheets or rows. Hence you do not get the options in the Transform Dialog box of Import Export wizard. If you manually blank out the exported data in the destination worksheet, export the data again to have the new data appended below the blank rows because the driver is looking at the saved definition of the named range and it is expanding it for the new rows. If you delete all the rows of old data in the worksheet, this behavior does not occur because deleting the rows changes the saved definition of the named range


We will discuss how to delete the rows using VB script and make sure the rows are not appended

Below is a step-by-step procedure to create the DTS package that deleted the existing rows and adds new data every time you execute it

1. Assuming you have your text file ready. Start the Import\Export Wizard.

2. Select the Data Source as Text File. Select your delimited file clicking on the elipse button. In my case I am selecting the  C:\DtsExcel.txt. This file has 1 row with 5 columns, comma as delimiter

3. Click on the Next button and make sure you select the Delimited radio button. Click Next

4. Select the Column Delimiter from the next window. Make sure that the Preview is as expected. Click Next

5. Select the destination as "Microsoft Excel 97-2000". Click on the elipse button, browse to the location where you want the Excel File to be placed Or select and exiting Excel File. In my case I am selecting the path and file as C:\DtsExcel.xls. Click Next

6. Click Next to the "Select source Tables and Views" Dialog

7. Select "Run Immediately" and "Save the DTS Package - SQL Server" options and click Next

8. Give a Package Name and select the SQL Server  where you wish to save the package. Select a valid authentication method and Click Next

9. Click Finish to Run the Package.

Now the File DtsExcel.xls is created and also the DTS package is created in the SQL Server

10. Open the SQL Server in Enterprise Manager. Scroll to "Data Transformation Services"- "Local Packages". You should see the  package here.

11. Open the package. You will see 3 Tasks. TextFileConnection, ExcelFileConnection and "Create Table DtsExcel Task"

12. Delete the Create Table DtsExcel Task

13. From the Tasks menu select "ActiveX Script Task". This should open the VB Editor.

14. Delete the existing script in the window. Copy the below script and paste it in the editor


'  Visual Basic ActiveX Script


Function Main()

                Dim Excel_Application

                Dim Excel_WorkBook

                Dim Excel_WorkSheet

                Dim iSheetCounter

                Dim bFound

                Set Excel_Application = CreateObject("Excel.Application")

                Set Excel_WorkBook = Excel_Application.Workbooks.Open("C:\DtsExcel.xls")

                iSheetCounter = Excel_WorkBook.WorkSheets.Count

                If iSheetCounter >= 1 then

                                For Each Excel_WorkSheet in Excel_WorkBook.WorkSheets

                                                ' Find the WorkSheet specified

                                                If Excel_WorkSheet.Name = CStr("DtsExcel") Then



                                                                Exit For

                                                End if


                End if

                Set Excel_WorkSheet = Nothing


                Set Excel_WorkBook = Nothing


                Set Excel_Application = Nothing

                Main = DTSTaskExecResult_Success

End Function

15. Make the changes to the script as per your environment.

           a. Set Excel_WorkBook = Excel_Application.Workbooks.Open("C:\DtsExcel.xls")  ------>Change the Path and file name

               b. If Excel_WorkSheet.Name = CStr("DtsExcel") Then - Change the SheetName. ------>You can open the Excel file and check the sheet name

15. Close the ActiveX Windows.

16. In the DTS Package Editor select the TextFile Connection and the Activex Script Task holding the Control button. Click on the Workflow menu and select "On Success". You should see the Green arrow joining the two tasks

17. Save the package and you are good to go.

The above script will check for the Worksheet name specified. Once found it would delete the rows from 2 to 65536. It would not delete the first row since it would be the column headers. You can change this range as per your requirement.


We have tried and tested the above script in our environment and it worked for us. So we would strongly suggest to try the above script and package in your test environment and try it on production only if the results are positive on test environment.

This post should not be treated as the Microsoft’s Recommendation or Resolution to the problem, it is only a workaround which worked in our environment and so we would like to share it.

Levi Justus
TL, Microsoft SQL Server


Comments (5)

  1. Erinn says:

    Thanks for the workaround. I had formatting issues with excel datetimes from DTS; so to get DTS to format the column correctly I manually added a datetime in the second row of the template.

    Unfortunately this threw off the clients import process since row 2 now only contained a date and no valid information – although the formatting from DTS for datetimes was now correct.

    By opening the excel file and deleting the second row after the transformation ran, everyone’s happy. Thanks again.

  2. Mark says:


    I need to be able to do this, however I need to delete a range of cells that have data and exclude the first row and first column from the range.

    It would be like creating a Macro that would "GOTO – Current Region" then de-select the first row and column.

    Any Idea how to do that here in place of the range?

    Thanks much,


  3. Nibir says:

    It works…

    Your article really helped me in my project.

    Thanks you so much,


  4. Kyle says:

    When I try to perform step 16, the "On Success" option is not available.  The only option available is "Workflow Properties".  

Skip to main content