I'm creating a new SSIS package using VS2022 to target Sql Server 2019 server.
I have ended up with a new, very basic package that contains a script component to use C# only. This simple package will execute in VS2022 without errors. I have made no changes to the script and no code.
When I create a Sql Agent job to execute this package located in the file system, it fails with the long output:
Executed as user: VENSON\Backup3x3cV. Microsoft (R) SQL Server Execute Package Utility Version 15.0.4415.2 for 64-bit Copyright (C) 2019 Microsoft. All rights reserved. Started: 17:42:57 Error: 2024-12-17 17:42:57.89 Code: 0xC001F02A Source: Package Description: Cannot create a task from XML for task "Script Task", type "Microsoft.ScriptTask" due to error 0x80070057 "The parameter is incorrect.". End Error Error: 2024-12-17 17:42:57.89 Code: 0xC0010018 Source: Script Task Description: Failed to load task "Script Task", type "". The contact information for this task is "". End Error Error: 2024-12-17 17:42:57.91 Code: 0xC0010026 Source: Script Task Description: The task has failed to load. The contact information for this task is "". End Error Error: 2024-12-17 17:42:57.91 Code: 0xC0024107 Source: Script Task Description: There were errors during task validation. End Error Error: 2024-12-17 17:42:57.91 Code: 0xC0010025 Source: Package Description: The package cannot execute because it contains tasks that failed to load. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 17:42:57 Finished: 17:42:57 Elapsed: 0.219 seconds. The package execution failed. The step failed.
When I delete the script component, store the package in the file system and execute the agent job, it executes without error.
Adding a database connection and a simple execute sql task runs fine.
Using VS2022 with latest patches on my dev PC. Server has 2019, 64 bit with CU30 (15.0.4415.2)
Project Target = SQL Server 2019
Script Task using script language Microsoft Visual C# 2019. Changing to VB makes no difference.
Are there any suggestions as I need some c# code to run this project?
December 18, 2024 at 3:33 am
Not that it helps you particularly, but the error message indicates that it is a script task which failed, not a script component, which is a different thing.
I would suggest deploying the package to SSISDB and running it from there. You might get better error diagnostics. It might even work first time.
December 18, 2024 at 8:09 pm
Thanks for the suggestion Phil.
I'll try that tomorrow when I can get an administrator to create the SSISDB.
The error message was for the script component. Confusingly, a newly created script component is named "Script Task" which is the component in the error messages. To check, I renamed the script component and this new name appeared in the error report.
Secondly, I added logging to the SSIS package hoping that more information is available. But nothing further other than nicely formatted.
#Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message
OnPreValidate,xxxx,xxxx,Package,{B78A4263-A59C-4331-BAD2-15FE0C9D0780},{F296B512-4724-49CF-A1C6-69B13B5547F2},18/12/2024 19:13:45,18/12/2024 19:13:45,0,0x,
OnError,xxxx,xxxx,Package,{B78A4263-A59C-4331-BAD2-15FE0C9D0780},{F296B512-4724-49CF-A1C6-69B13B5547F2},18/12/2024 19:13:45,18/12/2024 19:13:45,-1073676251,0x,The package cannot execute because it contains tasks that failed to load.
I also tried editing the C# code in the script and added some code - a variable assignment. No change in the result!
Any further suggestions are welcome.
December 19, 2024 at 2:02 am
OK, got it!
My next suggestion would be to create a simple script task that does nothing external, eg
int z = 1;
and delete the script component and then try running it again.
This should help narrow down whether this is purely a C# issue, or something else.
December 19, 2024 at 8:49 am
Hi Phil,
I have created the package with no code - fails. Added the 1 line variable declaration - fails again.
Deleting the script component, then the package works without failure. But my project really needs a small script component!
At each time, the package runs fine in VS2022 and fails when executed from a sql agent job that simply runs the package. I also tried creating a package that targets sql server 2022 and 2017 to no effect.
I've asked the site to install the SQL Agent catalog and whether they have a sql 2022 instance available.
Any further suggestions?
December 19, 2024 at 9:15 am
Running out, I'm afraid. I haven't used packages deployed to the file system for years. I would guess that there's something going on with .NET versions, but the errors you are receiving really don't help identify what.
Are you simply copying the packages to the server when 'deploying', or using some other method? If you do copy, are you taking the source package or the one in the BIN folder? Sorry, I can't remember this stuff now.
December 19, 2024 at 9:35 am
when you say job setup to run the package from filesystem - are you using the SQL Agent Integration Step to execute it, or using a command line step with dtexec on it?
if command line it may mean you are picking up the incorrect DTEXEC.exe.
if Integration Step - this would likely be an issue with the Integration Services installation (may even not be installed at all.. check it).
In both cases I suggest reinstalling Integration Services on that server. and checking to see if there are any "non-licensed" installs of DTEXEC on the server.
December 19, 2024 at 12:57 pm
Thanks frederico_fons
I'm running the package by creating a sql agent job, then adding a job step of type "SQL Server Integration Services Package" with the package located on the file system on another server using a UNC path to the dtsx file. It is accessible as the package will execute successfully when it does not include a script component.
The dtsx package is already on the file share as VS2022 is opening the solution in this folder, so I don't need to copy any files.
Integration Services is installed since packages can be run without errors (when no script component is present) from Sql Agent jobs. I'll request that Integration Services is reinstalled.
that is absolutely the wrong way to do it.
as you working with vs2022 I would advise you to test the following
in vs build the solution (you said target was 2019), then copy the resulting .dtsx file from the bin directory to a location on the local server, and try executing from there.
you can also change the SQL step to point to this build folder instead of the current one.
and have you confirmed that Integration services is installed on that server? if it is you should have a SSISDB and the Integration Services Catalogs should show something.
December 23, 2024 at 6:40 pm
@frederico_fons & @phil_parkin,
many thanks for your assistance,
it's taken a bit of time to diagnose further as the site was reluctant to touch anything on the server in this holiday period or reinstall integration services due to the holiday period.
However, the systems manager did check today and found that Integration Services was not installed! Unfortunately, I couldn't check that and more surprising that the installation is a like-for-like copy of the old server. Once installed, the package runs as expected from Sql Agent and my problem is solved
Now, if only the Sql Server/Sql Agent could generate a real error message when attempting to run a SSIS package without Integration Services, it would have been so simple.
Regards
Grant
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy