SQL Agent job throws error Microsoft.ScriptTask. The parameter is incorrect

Question

Hi,

I get error while running the SQL Agent job which executes SSIS package. The SSIS package job runs well when we run in VS manually, but throws error from SQL agent. Below are the details of config.
SQL Server 2019, VS 2019
SSMS v18.6
SSIS project TargetServerVersion is Sql Server 2019
SSDT for VS 2017 v15.9.25

Error - Description: Cannot create a task from XML for task "Get PackageRun Stats", type "Microsoft.ScriptTask" due to error 0x80070057 "The parameter is incorrect."
Below steps are performed
1. I have installed Microsoft.DataTools.IntegrationServices
2. Repair the VS

Please help

Answer 1

What is the package doing in detail? Does it work when you set the execution option "Use 32 bit runtime"?

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 2

It is simple package having a script task, a variable and execute sql task. The script task which takes the variable value and add the current date time to form a meaningful description.Once the description string is prepared, assigned to same variable. An execute sql task takes this variable value and inserts to a log table. 

Answer 3

Hi Prasant,

SSIS has 2 major environments:

1. Development environment , in VS with SSDT (or Integration Services Projects for VS2019).
2. Run-time environment on a server with MS SQL Server with SSIS.

So far you shared your dev. environments details and versions.

The SQL Server Agent job belongs to the run-time environment.

What about your SSIS run-time environment?

Its version shall match "...SSIS project TargetServerVersion is Sql Server 2019...".

Answer 4

I have shared our dev integration box environment details where we have sql agent job which connects to local db. The VS is also configured there, so that we can do debug. This environment is similar to local dev environment where multi user can login and contribute.

Below is the configurations

SQL server version 2019

Visual Studio 2019

SSDT 2017

Please let me know for any additional info needed.

Answer 5

Hi Prasant,

So your run-time environment is on the same machine. And it is MS SQL Server 2019.

You need to make sure that its SSIS component is installed too.

Few questions to ask:

• Did you create SSIS Catalog and SSISDB database?
• Did you deploy SSIS project to the SSIS Catalog?
• Did you try to run SSIS package directly from the SSIS Catalog manually without SQL Server Agent?

Answer 6

Hi Yitzhak,

Thanks for helping me here.

Its is package deployment model. The ssis packages are placed in file system. The sql agent job is referring to ssis package available in file system.

As mentioned in the initial post  I have installed Microsoft.DataTools.IntegrationServices. Is there any other package to be installed?

Answer 7

Hi Prasant,

• You need to make sure that MS SQL Server database engine and SSIS component are both installed.
  The Microsoft.DataTools.IntegrationServices is irrelevant for the run-time environment.
• "...The ssis packages are placed in file system..."
  - This is an outdated approach that was ok for the SSIS 2005-2008 era.
  - You are using SSIS 2019, which has completely new architecture. Starting from SSIS 2012 onwards: SSIS Catalog and SSISDB database.

Answer 8

Hi Prasant Nanda,

1. Could you please share the code in your Script Task?

2. May I know if you set the ProtectionLevel in your ssis package?

3. May I know if the user in sql agent job to execute ssis package has correct permission to access the .dtsx file?

Best Regards,

Mona

---

""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
For more information, please refer to the sticky post.

Answer 9

Hi Mona,

Thanks for replying. Below are the details.

1. The script task code is below

            string strTest = Dts.Variables["User::TestVar"].Value.ToString();
            Dts.Variables["User::TestVar"].Value = strTest + "- Package Run Time -" + DateTime.Now.ToString();

2. The protection level is EncryptSensitiveWithUserKey. The connection to local db server is on windows auth, so no sensitive credentials are there.

3. I am the admin in the system. a credential (crdJob) is created with identity as my user name. The package is configured to run as crdJob. The user which is having access to the ssis package.

Executed as user: DA-DEV-01\PNA. Microsoft (R) SQL Server Execute Package Utility  Version 15.0.4043.16 for 64-bit  Copyright (C) 2019 Microsoft. All rights reserved.    Started:  11:32:08 AM  Error: 2020-08-03 11:32:08.87     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

Please let me know a link where I can upload the dtsx package and any further information you need to help me.

Answer 10

Hi ,

Please upload by click the following "Insert Code Block".

Best Regards,

Mona

---

""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
For more information, please refer to the sticky post.

Answer 11

Answer 12

Dear All,

Thanks a lot for your help. The issue has been resolved now. I reinstalled the SQL Server 2019 and the issue got resolved.