SSIS package fails when run by SQL Agent - “Invalid character value for cast specification”

2
votes

I have a SSIS package which reads data from .xml and .xlsx files and writes them to staging SQL Server database. The package is deployed to SSIS catalog on Microsoft SQL Server (Enterprise) instance. When the package is executed by SQL Agent job step it fails with the following error message:

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005

An OLE DB record is available. Source: "Microsoft OLD DB Provider for SQL Server" Hresult: 0x80004005. Description: "Invalid character value for cast specification".

When the same package is run manually from Visual Studio it works just expected. There is no problem with feeding data to staging tables.

Any idea what can caused this issue?

1
sql-serverssis
Are you running against the same file in VS and within the agent? A Google implies that somewhere you're likely trying to convert an empty string, or NULL character to a different datatype (like a date). – Larnu
They are same files. The error message suggests the problem with data type (conversion). But I have same files, same tables where I want to put data into. The issue only persists once the package is run by SQL Server Agent. Not manually from Visual Studio. – Yorki
Khairul Alam what option? – Yorki
You may check this option, Go to Properties of ssis project -> Configuration Properties -> Debugging -> Debugging Options -> Run64BitRuntime. Set Run64BitRuntime = False. Save and Build the project then deploy again. – Khairul Alam
Khairul Alam, I've tried that one. It helped but with different error. The previous one referred to connection manager. And after I had changed Run64BitRuntime = False it was resolved. However now I am struggling with different issue which is described in this topic. – Yorki

1 Answers

2
votes

I finally find an answer and will here share with you how to resolve it.

Even though the parameter Run64BitRuntime =false at Project Properties wchich I had deployed to SSIS catalog it looks like it was actually executed with 64bit mode as at Job Step properties at configuration tab you have option to run it with "32-bit runtime"

Job Step Properties