none
Unexpected Termination when importing an Excel (.xlsx) file

    Dotaz

  • Hi,

    In SQL Server 2016, I am currently experiencing an "Unexpected Termination" error when executing an SSIS package either via the SSMS Object Explorer or as a job in SQL Server Agent. The package will always execute successfully when I run it locally in Visual Studio, but when I deploy it to the server, the package always terminates during a Data Flow Task step in which it is reading data from an Excel (.xlsx) file.

    I have attempted to utilize an Excel Source Connection as well as a Source Script Component but the error is always the same and it will always occurs near the end of the Data Flow Task.

    I've confirmed the following:

    • The package will acknowledge the file and read from it. I am able to see that either some or all of the data from the excel file has been loaded to the destination table. However, it will always return an "Unexpected Termination" when some or all of the data has been imported.
    • There are no environment variables used. The values ran on the server as the same as those used in Visual Studio.
    • I have used Verbose Logging and created an SSIS log provider for SQL Server but neither show any errors in my execution.
    • I have attempted to convert the file to .xls and .csv via a Script Task, but the error remains.
    • I do not have parent-child package relationships. This is a standalone package that is not executed via another package.
    • I have verified that the necessary permissions have been given to the appropriate accounts, especially since I am executing the package manually with my own account.
    • I have both the 2010 and 2016 Microsoft Access Database Engine drivers installed.

    Thank you very much,

    Nate


    • Upravený natepay 5. dubna 2018 1:38
    5. dubna 2018 1:12

Všechny reakce

  • Hi Nate,

    without further details on how the package is implemented it is hard to judge what's wrong, but the key pieces appear to me being the parts where you say it acknowledges a file and "when some or all of the data has been imported" it fails.

    So I am fantasizing here you load the Excel data into a table each time a file gets dropped. Therefore, it runs from SSMS when there are no files deposited at a high rate.

    Which in turn makes me believe while you import the file an external process tries to write to the very file it or perhaps even worst you read from it when the process is writing thereby generating this error.


    Arthur

    MyBlog


    Twitter

    5. dubna 2018 2:15
    Moderátor
  • Hi Arthur,

    Thank you for the quick response. I manually place the file in a directory. The file is sitting in a directory and I run a Data Flow Task to import it directly from Excel to a loading table (for simplicity's sake) in SQL Server. No external process is trying to read or write to that same file. At this point I have not even parameterized the Excel Connection with a variable, it is hard coded because I want to implement as many controls as possible. 

    Please let me know what other details can help with this issue.

    Thanks,

    Nate


    • Upravený natepay 5. dubna 2018 4:34
    5. dubna 2018 4:32
  • Hi Arthur,

    Thank you for the quick response. I manually place the file in a directory. The file is sitting in a directory and I run a Data Flow Task to import it directly from Excel to a loading table (for simplicity's sake) in SQL Server. No external process is trying to read or write to that same file. At this point I have not even parameterized the Excel Connection with a variable, it is hard coded because I want to implement as many controls as possible. 

    Please let me know what other details can help with this issue.

    Thanks,

    Nate


    Let me replay what I understood

    1. You place an Excel file manually to the folder on the server

    2. You open package and change the connectionstring of the Excel connection manually to map to the current file

    3. You save the package and then execute it from the job/SSMS

    4. Its failing in between with Unexpected termination error

    5. In VS environment within the server its working fine for the same file dumping to your sqlserver instance

    Assuming above is the exact scenario you've on hand, the thing that comes to my mind immediately is this

     version difference - This can happen when VS shell you're using is of different version compared to your actual IS server version. That being the case, it can happen that ACE provider referred by VS shell may of different version compared to the one in server and due to some reason the one in server cant gracefully handle your excel data completely. You can check the versions and if different, you can try setting the correct TargetServerVersion in SSDT to be in line with what is in the server and see if it works correctly.


    Another thing you can try is to keep only one version of the Excel driver based on the version of Excel files used and try


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    5. dubna 2018 5:27
  • Hi Visak,

    I do not change the connection string before I execute it, it is using the default parameter from the package when I deployed it to the server.

    My apologies for not being clear. When I execute the package in Visual Studio, I mean on my local PC, separate from the server I am deploying it to. I do not have the ability to access Visual Studio on the SQL Server.

    Thanks,

    Nate

    5. dubna 2018 16:53
  • Hi Visak,

    I do not change the connection string before I execute it, it is using the default parameter from the package when I deployed it to the server.

    My apologies for not being clear. When I execute the package in Visual Studio, I mean on my local PC, separate from the server I am deploying it to. I do not have the ability to access Visual Studio on the SQL Server.

    Thanks,

    Nate

    Ok

    In that case on both cases, does it connect to the db in the same server instance?


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    5. dubna 2018 18:14
  • Yes. I have verified that the database and server are the same.
    5. dubna 2018 18:47
  • Check for server health maybe it runs out of resources. Check all the logs for clues.

    I am thinking also explaining how you designed the package would help.


    Arthur

    MyBlog


    Twitter

    5. dubna 2018 19:09
    Moderátor
  • I have confirmed with our Ops Team that there are no resource contention issues. No other process is locking the database and there are no error logs and the windows event viewer is not telling us anything either.

    I am not able to insert images but the process is simply a Data Flow Task that imports data from an excel file to a database table.

    I've been able to see this though:

    2018-04-05 14:45:03.44 spid68      Error: 4014, Severity: 20, State: 11.
    2018-04-05 14:45:03.44 spid68      A fatal error occurred while reading the input stream from the network. The session will be terminated (input error: 109, output error: 0).

    5. dubna 2018 22:38
  • It sounds familiar. Basically this error should lead to the culprit.

    Likely it is the TCP Chimney offload issue documented here https://www.mssqltips.com/sqlservertip/3538/fixing-sql-server-fatal-error-4014/ and here http://sqltouch.blogspot.ca/2013/03/tcp-chimney-offloads-and-sql-server.html

    Rare, but I now realize happens on newer OS builds and software.


    Arthur

    MyBlog


    Twitter

    6. dubna 2018 2:12
    Moderátor
  • Thank you very much for the input Arthur.

    I've sent our Ops Team the link you provided to see if they will implement the necessary changes.

    In the end, I was able to copy the contents of the Excel file into a CSV and have uploaded that file instead.

    I will confirm later on if the link has solved the issue.

    Thanks,

    Nate

    7. dubna 2018 0:28
  • Visakh, Thanks SO much for such great information. I had the same issue with unexpected termination, and the version mismatch between my VS IDE and the MSSQL Server was the culprit. For those who needs the steps:

    - In SSMS Object Explorer, right click the instance, select Properties

    - Compare your Version # to the version numbers here: https://support.microsoft.com/en-us/help/321185/how-to-determine-the-version-edition-and-update-level-of-sql-server-an

    - In Visual Studio, save your work, make a backup

    - In Visual Studio, right-click your Project, select Properties, expand Configuration Properties, expand General

    - In the TargetServerVersion dropdown, select your MSSQL version, select OK and follow the prompts

    - Re-Deploy your project, test

    Worked perfectly, on to the next unknown issue....

    -Bill

    17. května 2018 18:37