Sql-server – Why am i getting a truncation error when running a SSIS package on the sql server agent, but not when i manually run it

sql serversql-server-2008-r2sql-server-agentssis

I created a SSIS package that drops then creates a table, to transfer data from Sql Server 2008 r2 to an access file. When I run it in BIDS 2008r2 it runs just fine, no truncation issues at all. When I take that package and run it on the SQL Server agent, it fails saying it had a truncation error on the date field. When I manually look at the data (it’s a mere 159 records) I don’t see any funky data.

Both my account and the service account are server administrators so no difference of access on the server side, and we are both in the local administrator group on the destination file share. I also run 4 other packages that are built the same way, but this is the only one that has the data truncation error only on the agent, and not manually run in bids.

Below is the full text of the error minus the user account information. 

  Executed as user:<Domain>\<sql service account>. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.6560.0 for 64-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.
    Started:  7:13:49 AM
  Error: 2019-08-12 07:13:50.78
     Code: 0xC0202009
     Source: Data Flow Task 1 chips dest [705]
     Description: SSIS Error Code DTS_E_OLEDBERROR.
       An OLE DB error has occurred. Error code: 0x00040EDA.
  End Error  
  Error: 2019-08-12 07:13:50.78
     Code: 0xC020901C
     Source: Data Flow Task 1 chips dest [705]
     Description: There was an error with input column "DATE" (959) on input "Destination Input" (718). The column status returned was: "The value could not be converted because of a potential loss of data.".
  End Error
  Error: 2019-08-12 07:13:50.78
     Code: 0xC0209029
     Source: Data Flow Task 1 chips dest [705]
     Description: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.
       The "input "Destination Input" (718)" failed because error code 0xC0209077 occurred, and the error row disposition on "input "Destination Input" (718)" specifies failure on error. An error occurred on the specified object of the specified component.
       There may be error messages posted before this with more information about the failure.
  End Error
  Error: 2019-08-12 07:13:50.78
     Code: 0xC0047022
     Source: Data Flow Task 1 SSIS.Pipeline
     Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED.
       The ProcessInput method on component "chips dest" (705) failed with error code 0xC0209029 while processing input "Destination Input" (718). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
       There may be error messages posted before this with more information about the failure.
  End Error
  DTExec: The package execution returned DTSER_FAILURE (1).
  Started:  7:13:49 AM
  Finished: 7:13:53 AM
  Elapsed:  4.197 seconds.
  The package execution failed.
  The step failed.

Best Answer

This is one of those annoying inconsistencies due to the fact that the SQL Agent may run under different settings than when you run it yourself (e.g. it might have ARITHABORT off when you have it on, so forth).

Rather than twiddling with the settings, I find it more robust to use solution that are not sensitive to different settings, which means avoid any possible precision loss. Because your target is an Access database, the date/time has a precision of one second. If you're using old datetime data type, it has a weird precision of 1/3 milliseconds, which can potentially cause the precision loss that SSIS loves to tell everyone and their dog about.

To work around that, match the precision exactly by using datetime2(0) which has the same precision (one second) which should then avoid the error, regardless of what settings you/the agent are running under.

Related Question