CODEWITHSUNDEEP
ssis
sam
sam

Reputation: 1304

How to make xml configuration path configurable in ssis 2012

I have a situation in which I need to provide ssis package for different environments. I need to follow xml configuration for this and have created the file for the same. However there is a problem The xml file is kept at my local system. When I send the SSIS package to client along with the XML file. The path where client keeps the xml is different and so the package does not fild the file and throws an error.

One way to provide the xml file path is through envoironment variable. We are not allowed to use Windows envoironment variable.

I also tried SQL server table configuration. But I am getting the same problem in making connection string configurable. Connection string has to be kept in environment variable

Can someone help me here to make xml configuration path configurable

Can I use sql server envoironment variable. I have never done this though. If yes will this environment variable be automatically created when client deploy's the ssis package or he needs to create one manually in ssisdb.

Upvotes: 0

Views: 360

Answers (1)

Pintu Kawar
Pintu Kawar

Reputation: 2156

If you are using SQL Server 2012+, the package configuration can be done through project level connection managers (new in 2012), where all packages within the project can access the same.

You have following options:

  1. Re-design your packages by using Project deployment model, using shared connections (Project level connection manager) and deploy your packages to SSIS Catalog.

  2. Modify the existing package with the Client XML location and ask client to put your configuration file there.

  3. You can keep the configuration/connection details in SQL Server tables without referring the XML file. Refer

Upvotes: 1

Related Questions