I'm having difficulty with a package I have created to import an Excel Spreadsheet into a SQL Table.
Basically we have an aged Call System that doesn't have an ODBC setup for exporting Call Stats information, however it is able to export the information into a CSV format and email out.
I have created a VBA Process in Outlook that saves said file to a folder upon it being emailed out. I have then created an SSIS Package that takes whatever file is in the directory and imports it into SQL.
This works without issue in Visual Studio and then Executing the package using the debugger, however when the job actually runs in SQL Server Agent the job states it completes successfully however the file remains and doesn't import (the file is supposed to be deleted at the end of the SSIS Package.)
Anybody able to shed some light on this for me.
Here is a screenshot for an idea.
Basically I use a For Loop to loop through a folder containing these Call Stats.xls Files.
The Set Date creates a data and pass's it via a Single Row Result to a Variable (This is because the Call Stats is always sent out the following day so I just use a SQL Query on a GETDATE() minus 1 day to get the previous day otherwise the stats would be a day ahead).
The Data Flow then gets the information from the Flat File / XLS, the derived column changes the date column and replaces the data in there with the result from the SQL GETDATE() Query.
The Data Conversation converts the relevant fields to fit in my SQL Table and then the OLE DB location is my Table in SQL.
Afterwards a File System Task deletes the XLS file as it is no longer required.
I hope that is enough information please let me know if you need anymore.
The For Each Loop is based on configuration except the file name is mapped in Variable Mappings so that I can pass it later to the File System Task.
I have changed the owner of the SQL job to my own domain log in and re-ran the job and it still completes successfully but no import and the file remains.