CODEWITHSUNDEEP
parametersssisconfig
texpert
texpert

Reputation: 11

How to read SSIS-DTS package parameters from a file once 'deployed' to server using system file method?

How can you read project level parameters stored in the project.params file at build time for a package that is deployed to the Sql Server (v 2016) using the file system method? We want to read the parameters from a file.

enter image description here

We have tried to add the params file as a second config file but that didn't work. And, we don't want to read parameter values from a command line style call. We want to get them from a file.

This is the error (below) that we receive when we run the package. In this example, we used parameters for the Mail Task by linking a task property to a project level parameter.

Note that we are not deploying the SSIS package using the standard process that would load the package into the Integrated Services Catalogs section of the database. And, you can see in the error that it does not know where to get the value from.

Executed as user: [network user XXX]. memory error. End Error  Error: 2021-05-04 Code: 0xC0017003 Source: Send Mail Task      Description: The expression "@[$Project::EmailFrom]" on property "\Package\Send Mail Task.Properties[FromLine]" cannot be evaluated. Modify the expression to be valid. ...

The server file structure is:

And the params file contains an entry and value for the EmailFrom parameter as generated from Visual Studio.

Upvotes: 1

Views: 558

Answers (1)

billinkc
billinkc

Reputation: 61259

You're mixing your models.

Package Deployment Model the deployable unit is a package and it may reside on the file system or in the MSDB.

The Project Deployment Model's deployable unit is the .ispac file. An ispac file is zip file comprised of all the packages, the project.params file, a manifest file and optionally, any project level connection managers. The ispac is typically deployed to the SSISDB although you can run packages from the ispac.

There is no way to run a Package Deployment Model package and also use the project parameters.

You will want to use either the config file approach and store your email info there and remove your project parameters or deploy the .ispac to the SSISDB and dispense with using the config file - the Configuration option in the SSISDB is a much more flexible and secure approach over config files especially with regard to sensitive data like credentials.

Upvotes: 2

Related Questions