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
Comments
Anonymous
January 11, 2009
PingBack from http://blog.a-foton.ru/index.php/2009/01/12/dts-package-that-exports-results-to-existing-excel-workbook-appends-the-data-instead-of-overwriting/Anonymous
March 04, 2009
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.Anonymous
August 14, 2009
Hi, 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, MarkAnonymous
June 06, 2010
It works... Your article really helped me in my project. Thanks you so much, NibirAnonymous
December 18, 2014
When I try to perform step 16, the "On Success" option is not available. The only option available is "Workflow Properties".