May 4, 2023 at 1:30 pm
Hi,
I successfully created an SSIS package and it executed perfectly in Visual Studio's development environment.
However, when I scheduled this very package within a SQL Server Job Agent, it mysteriously fails:
" Hresult: 0x80040E07 Description: "Conversion failed when converting date and/or time from character string.". End Error Error: 2023-05-04 13:33:28.19 Code: 0xC004701A Source: Load Readm SSIS.Pipeline Description: OLE DB Source failed the pre-execute phase and returned error code 0x80040E07. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 13:33:09 Finished: 13:33:28 Elapsed: 19.047 seconds. The package execution failed. The step failed."
I would appreciate some ideas.
Kind Regards
May 4, 2023 at 1:45 pm
What does the package do?
Could there be a date format difference (eg, DMY vs MDY) between the two environments?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 4, 2023 at 1:58 pm
I did not create it, but simply run it. I was only trying to automate the running of it in a SQL job agent.
It makes a copy of an existing MS Excel template and renames it for today's date. Then populate this Excel file with the data from a SQL Table.
May 4, 2023 at 2:36 pm
Most likely is going to be some form of localisation, say British English to American English, and DMY / MDY based formatting.
When the package is executed in Visual Studio it takes the format of the local user and if in SQL your account is mapped as a British English account it will assume dates are DMY, where as the agent may be mapped as English and want MDY, so you can easily get date based conversions failing, where it works in one place but not another.
Essentially always try to handle dates as YMD and use an ISO supported format, it will remove all ambiguity from your dates.
Would be one to look into the package and redesign it to handle dates in a more ISO format.
May 4, 2023 at 4:21 pm
Thanks. I tried adding this line at the beginning of the SQL Script in the package:
"SET DATEFORMAT ymd;"
But gave me error (similar to the one I am getting when the Job Agent fails):
"Conversion failed when converting date and/or time from character string. (Microsoft SQL Server Native Client 11.0)."
May 5, 2023 at 8:07 pm
Unfortunately the above does not work either, that is, I tried coding FORMAT( @d, 'yyyy/MM/dd', 'en-gb' ) for each Date field in every SQL Script in the SSIS package.
I also tried one more thing: I converted all Date fields within the MS Excel (Template) File to YYYY-MM-DD. The job agent still fails with this error:
"OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E07 Description: "Conversion failed when converting date and/or time from character string.". End Error Error: 2023-05-05 21:01:18.54 Code: 0xC004701A Source: Load Readm SSIS.Pipeline Description: OLE DB Source failed the pre-execute phase and returned error code 0x80040E07. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 21:01:16 Finished: 21:01:18 Elapsed: 2.219 seconds. The package execution failed. The step failed."
May 6, 2023 at 6:50 am
Let me step back a little. You are importing from Excel to SQL Server, is that correct? And you have one or more date columns in the Excel file which are causing this issue.
Can you confirm that each of the items in these columns is a valid date? No text or anything else in there?
Also, can you confirm that the dates are entered as numerics rather than strings? If this is the case, changing the date format of the cells will change the way in which they are displayed.
... for each Date field in every SQL Script in the SSIS package
Regarding your comment above, there is no such thing as a 'SQL Script' in SSIS. What do you mean? A data flow? An ExecuteSQL task? The assumption that people here will be making is that you are using a data flow to move data from Excel to SQL Server.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 9, 2023 at 8:13 am
Thanks Phil.
SQL Tables are being used to populate newly created Excel files during the execution of the SSIS package. We are NOT transferring any data from any MS Excel File to any SQL Table here.
The SSIS package executes perfectly in Development mode in Visual Studio, which seems to confirm there are no issues with any dates that may have been in a String Format i.e our Excel File(s) are being built - from scratch - with data being imported from the SQL Tables. Its just that executing the same using the SQL Job keeps failing.
'SQL Scripts in SSIS' refers to any components in the SSIS package where I am using SQL Code such as OLE DB Source components, Execute SQL Tasks etc.
May 22, 2023 at 12:54 pm
I have converted all the dates to ISO format, but the Job is still failing.
May 22, 2023 at 1:17 pm
I have converted all the dates to ISO format, but the Job is still failing.
OK, so you're going from SQL Server to Excel. That means you can use a query as your data source.
Please try changing your source query, such that the date column actually has a datatype of DATE as it goes into SSIS.
SELECT CAST(<StringDateColumn> as DATE)
Also, can you confirm that the source dataset you are using when the package succeeds in VS is the same dataset when the SQL Agent job fails?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply