DTS PACKAGE THAT EXPORTS RESULTS TO EXISTING EXCEL WORKBOOK APPENDS THE DATA INSTEAD OF OVERWRITING

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

Cause
=====

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

Workaround
=========

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

                                                                Excel_Workbook.Worksheets("DtsExcel").Rows("2:65536").Delete

                                                                Excel_WorkBook.Save

                                                                Exit For

                                                End if

                                Next

                End if

                Set Excel_WorkSheet = Nothing

                Excel_WorkBook.Close

                Set Excel_WorkBook = Nothing

                Excel_Application.Quit

                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.

Note:

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