Clear data in Excel using SSIS

Question

I have created s Stored Procedure in SQL which imports data from a flat file, updates the data and imports the updated data within a table.

After some research, I found that the only way to export this (64bit) table would be to create a SSIS package and use a SQL Job to execute the package.

I have done all of this and managed to get the table data exported but the problem is that it does not clear the data before the import. I have then created the following

When dropping the excel table, I have the following SQL Statement : DROP TABLE [Sheet1$] When creating the table, I have the following SQL Statement :

CREATE TABLE 'Sheet1$' ( BRANCH NVARCHAR(10) , SRCBRANCH NVARCHAR(10) , DEPARTMENT NVARCHAR(10) , GLCODE NVARCHAR(10) , DOCDATE NVARCHAR(10) , VALUE NVARCHAR(50) , ITEMREFERENCE NVARCHAR(100) , MISCREFERENCE NVARCHAR(100) , SUFFIX NVARCHAR(10) , NARRATIVE [NVARCHAR(100) ) GO

After the table has been dropped, it clears all the data together with the header and then fails on the second SQL Task (Create Excel Table) with the following error message:

[Execute SQL Task] Error: Executing the query "CREATE TABLE 'Sheet1$' (
    BRANCH NVARCHAR(10) ,
    S..." failed with the following error: "Syntax error in CREATE TABLE statement.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

After fail, I cant execute the package due to a validation error. This is because my headers in the Excel sheet has been deleted.

Could someone please point me in the right direction. I have exhausted all options.

Regards

Solution

CREATE TABLE 'Sheet1$' 
(
    BRANCH NVARCHAR(10) ,
    SRCBRANCH NVARCHAR(10) ,
    DEPARTMENT NVARCHAR(10) ,
    GLCODE NVARCHAR(10) ,
    DOCDATE NVARCHAR(10) ,
    VALUE NVARCHAR(50) ,
    ITEMREFERENCE NVARCHAR(100) ,
    MISCREFERENCE NVARCHAR(100) ,
    SUFFIX NVARCHAR(10) ,
    NARRATIVE **[**NVARCHAR(100) 
)
GO

***This is your syntax error. This was asked quite a while ago so I would think you have rectified this by now but I wanted to post for others to know what the issue was.