April 20, 2018 at 9:17 pm
Hello!
I'm experiencing a SSIS package error during execution. The package runs ok without error and runs the proccess below:
1. Create a Excel file;
2. Input data into Excel file;
3. Format sheets with bold, fonts, colors and more...
When I run this package over Visual Studio, no error occurs. But I have no same situation when I try to run over SQL Server Agent. The error returned is below:
Date 4/21/2018 12:12:27 AM
Log Job History (DTS - Homologação - Envia Notificação)
Step ID 1
Server #########
Job Name DTS - Homologação - Envia Notificação
Step Name DTS - EXECUTA
Duration 00:00:02
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: XXXXXXX. Microsoft (R) SQL Server Execute Package Utility Version 13.0.4474.0 for 32-bit Copyright (C) 2016 Microsoft. All rights reserved. Started: 12:12:27 AM Error: 2018-04-21 00:12:29.66 Code: 0x00000001 Source: Script Task Description: Exception has been thrown by the target of an invocation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 12:12:27 AM Finished: 12:12:29 AM Elapsed: 2.281 seconds. The package execution failed. The step failed.
Do anybody experiencied this error also? I need some help with it...
April 21, 2018 at 5:44 pm
Looks to me like an Excel Driver Issue, this might need 64 bit drivers. Ensure that excel data source settings are correct in SQL Agent Job.
or
Try running the job by selecting the option Run in 32 bit environment.
=======================================================================
April 22, 2018 at 6:19 am
cvbarbosa93 33845 - Friday, April 20, 2018 9:17 PMHello!I'm experiencing a SSIS package error during execution. The package runs ok without error and runs the proccess below:
1. Create a Excel file;
2. Input data into Excel file;
3. Format sheets with bold, fonts, colors and more...
When I run this package over Visual Studio, no error occurs. But I have no same situation when I try to run over SQL Server Agent. The error returned is below:Date 4/21/2018 12:12:27 AM
Log Job History (DTS - Homologação - Envia Notificação)Step ID 1
Server #########
Job Name DTS - Homologação - Envia Notificação
Step Name DTS - EXECUTA
Duration 00:00:02
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0Message
Executed as user: XXXXXXX. Microsoft (R) SQL Server Execute Package Utility Version 13.0.4474.0 for 32-bit Copyright (C) 2016 Microsoft. All rights reserved. Started: 12:12:27 AM Error: 2018-04-21 00:12:29.66 Code: 0x00000001 Source: Script Task Description: Exception has been thrown by the target of an invocation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 12:12:27 AM Finished: 12:12:29 AM Elapsed: 2.281 seconds. The package execution failed. The step failed.Do anybody experiencied this error also? I need some help with it...
Based on what you mention the script is doing I would guess that you are using the Office Interop assemblies - this being the case it would also mean you need to have a copy of Office installed on your server which is not supported - and would also require you to have a second license of office bought for each user that directly or indirectly uses that server.
Script Task Description: Exception has been thrown by the target of an invocation. - this will normally be
- missing dll/assembly
- incorrect dll/assembly version
- 32/64 bit issue
If it is just 32/64 bit issue then you can "fix" it by forcing dtexec to run in either 32/64 as applicable - most of the times you do wish to have it on 64 bit, even for excel - but if on your own PC 32 bit may be the requirement.
If it is a different issue you should first try and identify the correct error.
this can be done by using a try/catch block around your code and firing a event to log the error
try
{
Dts.Log("Rows processed: " + rowsProcessed.ToString(), 0, emptyBytes);
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception ex)
{
//An error occurred.
Dts.Events.FireError(0, "Script Task Example", ex.Message + "\r" + ex.StackTrace, String.Empty, 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}
https://docs.microsoft.com/en-us/sql/integration-services/extending-packages-scripting/task/raising-events-in-the-script-task?view=sql-server-2017 has more info on these.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply