Search code examples
sql-serverssisetljobssql-job

Pass parameters from SQL Server Agent job to SSIS Package on Step "set value"

I have an SSIS package that will be run in a another database and has some parameters that will depend on the environment in which will be running (database name, FilePath for an excel file, ServerName, etc) and I want to set these in a way that when I send the package to the third party they are able to configure these parameters and run the package in an SQL job using SQL server agent (is the easiest way I thought for them to run a manual SSIS package).

In order to do this, I have "parametrized" the values I need in SSIS enter image description here

these parameters are set at Package level.

and what I'm trying to do now is to pass these parameters from SQL Server Agent job configuration step set data tab.

enter image description here

I'm getting this error when running the job:

Executed as user: NT Service\SQLSERVERAGENT. Microsoft (R) SQL Server Execute Package Utility  Version 14.0.2027.2 for 32-bit  Copyright (C) 2017 Microsoft. All rights reserved.    
Started:  9:47:32 PM  
Error: 2020-04-05 21:47:32.91     
Code: 0xC001F016     
Source: Package      
Description: Changing the Value of a variable failed because it is a parameter variable. Parameter variables are read-only.  End Error  
DTExec: Could not set \Package.Variables[Database_Name].Value value to dev.  
Started:  9:47:32 PM  
Finished: 9:47:32 PM  
Elapsed:  0.125 seconds.  
The package execution failed.  The step failed.

These is the important line:

DTExec: Could not set \Package.Variables[Database_Name].Value value to dev.

I'm thinking that is having problems setting this variable, I don't know if it's something I setup wrongly in SSIS or the way I'm trying to assign it in SQL Sever Agent. (the way I'm calling it I took it from recommendations online).

to summarize; I need to send parameters from sql server agent to the SSIS package. Plus*:I'm also open to recommendations extra for a way to have SSIS packages executed easily for a third party without SQL knowledge.

P.S. I've been recommended to use catalog, but will this be possible to use when a third party has to use the package in his environment? i.e. will he be able without visual studio to "deploy" the package?

Solution

  • I was getting this error also:

    DTExec: Could not set \Package.Variables[.....].Value value to ....

    In my case, I was using VS2019. The solution project target server is 2019, which is different from our test SQLServer (2017). After changing the target server to 2017, the parameters got passed to the dtsx as expected.