none
SSIS - Import Excel Task Running Fine in Visual Studio, Failing as a SQL Job

    Question

  • Morning All,

    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.

    Cheers.[/indent]
    Friday, January 04, 2013 10:51 AM

Answers

  • How is the for each loop configured? Does it use a configuration or an expression?

    Does the account used to run the package have sufficient permissions on the folder where the files are stored?


    MCSA SQL Server 2012 - Please mark posts as answered where appropriate.

    • Marked as answer by Lynchie1987 Thursday, January 10, 2013 11:10 AM
    Friday, January 04, 2013 12:00 PM
  • The owner of the job doesn't matter, it's the account used in the jobstep that needs permission.

    MCSA SQL Server 2012 - Please mark posts as answered where appropriate.

    • Marked as answer by Lynchie1987 Thursday, January 10, 2013 11:10 AM
    Sunday, January 06, 2013 9:07 PM

All replies

  • Can you explain exactly what you do in the package? Which tasks/components you use and so on?

    MCSA SQL Server 2012 - Please mark posts as answered where appropriate.

    Friday, January 04, 2013 10:54 AM
  • OK,

    Here is a screenshot for an idea.

    Screen Shot 1Data Flow

    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.

    Friday, January 04, 2013 11:31 AM
  • It is possible that the account used to execute the package does not have the permission to open excel file from the location where they sit. When you are executing the code from VS, it is using your own identity to do so.

    Yibo MCT, MCPD, MCTS, MCITP

    Friday, January 04, 2013 11:52 AM
  • How is the for each loop configured? Does it use a configuration or an expression?

    Does the account used to run the package have sufficient permissions on the folder where the files are stored?


    MCSA SQL Server 2012 - Please mark posts as answered where appropriate.

    • Marked as answer by Lynchie1987 Thursday, January 10, 2013 11:10 AM
    Friday, January 04, 2013 12:00 PM
  • Hi Both,

    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.

    Cheers

    Friday, January 04, 2013 1:19 PM
  • The owner of the job doesn't matter, it's the account used in the jobstep that needs permission.

    MCSA SQL Server 2012 - Please mark posts as answered where appropriate.

    • Marked as answer by Lynchie1987 Thursday, January 10, 2013 11:10 AM
    Sunday, January 06, 2013 9:07 PM