Problem with package execution

[TomaszB12]

Hi,

I've got a job on my sql server instance which calls a ssis package. The job code in the attachment.

Getting following error:

Description: In order to execute this package, you need to specify values for the required parameters. Source: .Net SqlClient Data Provider Started: 17:15:00 Finished: 17:15:00 Elapsed: 0.156 seconds. The package execution failed. The step failed.

What am I doing wrong?

Any suggestions appreciated!

Regards,
Tomasz

 

[mark williams]

The error message indicates that your SSIS package requires certain parameters to be provided for execution, and these parameters are not being specified when the SQL Server Agent job is running the package. To resolve this issue, follow these steps:

1. Identify Required Parameters: First, ensure you know all the parameters that the SSIS package requires.

2. Edit the SQL Server Agent Job: Configure the job to pass the necessary parameters to the SSIS package.

Here's a step-by-step guide to configuring the job properly:

Step 1: Open SQL Server Management Studio (SSMS)
- Open SSMS and connect to the SQL Server instance where the job is located.

Step 2: Edit the Job
- In the Object Explorer, navigate to SQL Server Agent -> Jobs.
- Find the job that runs your SSIS package, right-click it, and select Properties.

Step 3: Edit the Job Step
- In the Job Properties window, go to the Steps page.
- Select the step that executes the SSIS package and click Edit.

Step 4: Configure the Command Line for the Job Step
- In the Edit Job Step window, if you are using the Type as SQL Server Integration Services Package, ensure the parameters are passed correctly.
- If you are using a command line, switch to the Command Line tab.

Step 5: Add Parameters
- If the package is executed using the `dtexec` utility, you need to specify the parameters in the command line. An example command line with parameters looks like this:

 ```bash
    dtexec /F "C:\Path\To\Your\Package.dtsx" /SET "\Package.Variables[User::ParameterName].Value";"ParameterValue"
    ```

Repeat the `/SET` option for each parameter you need to pass.

Step 6: Save and Test
- After adding the necessary parameters, save the job step and the job.
- Run the job manually to verify that it works with the specified parameters.

Example
Here's an example to illustrate this:

Assume your SSIS package is `C:\Packages\MyPackage.dtsx` and it requires two parameters: `InputFile` and `OutputDirectory`.

1. Open the Job Step properties.
2. In the Command Line tab, modify the command to include:

dtexec /F "C:\Packages\MyPackage.dtsx" /SET "\Package.Variables[User::InputFile].Value";"C:\Data\Input.csv" /SET "\Package.Variables[User::OutputDirectory].Value";"C:\Data\Output"

3. Save and close the properties.
4. Run the job to ensure it executes correctly.

By following these steps and ensuring all required parameters are specified, your SSIS package should execute without encountering the "required parameters" error.