Clever Geek Handbook

SSIS: Truncate Excel Destination

I am creating an SSIS package that imports data from SQL Server Source

to Excel Destination

.

How can you truncate a table before running? I have tried as follows (with Execute SQL Task

no success.

enter image description here

+3


source to share


2 answers


The Jet provider does not support either the truncate or delete command . You have 3 workarounds:

  • Have a blank Excel template that you clone before starting the dataflow, or
  • Use the Run sql task to create a new workbook / tab before starting the data flow.
  • Drop Table TableCall_Log

    sheet with Drop Table TableCall_Log

    and create a new one. You can refer to this link for more details.


useful links

+3


source


Truncation is not supported. You can recreate the entire Excel file using two tasks:

  1. The first task is the File task, which deletes the target xls file.
  2. The second task will be "Execute SQL Task" which creates a "table" (Excel table). Use the EXCEL connection type with the Excel connection manager and the CREATE TABLE statement.

If you don't know the exact form of the CREATE TABLE statement, try to prepare the Excel destination in the dataflow task first and create a new Excel table (by clicking the New button on the Connection Manager tab in the Excel destination editor), the SSIS designer will show you CREATE TABLE the expression you need.



Connect the first task to the second using the Completion constraint if you are not sure the excel file exists every time you run the package.

You may also need to set the DelayedValidation property to True for tasks following these first two tasks.

0


source






More articles:


All Articles