2

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?

Baldie47
  • 1,148
  • 5
  • 16
  • 45
  • 1
    `Package.Variables` That's a **variable** not a Parameter. What you're doing is like trying to set the value of a variable declared inside an SP, and hence the error. If you were using the SSIS Catalog, you would be having a much simply time here. – Thom A Apr 05 '20 at 20:09
  • So I could fix this by using variables inside package instead of parameters? and more important question, since I'm able to use catalog for me. how would it work for the third party? can he set a catalog and "upload" a package without having visual studio? – Baldie47 Apr 06 '20 at 07:53

1 Answers1

2

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.

Yogi
  • 410
  • 4
  • 16
  • Wait so you didn't have to change them to variables? I'm getting an error that says "Changing the Value of a variable failed because it is a parameter variable. Parameter variables are read-only" – Vin Shahrdar May 27 '22 at 20:24