SSIS: Creating Configuration File for both Kinds of Authentication Mode

Article
1/17/2024

Introduction

This article demonstrates two ways of creating config files for an OLE-DB connection in SSIS. Config file creation for a connection depends on how the connection is made to the server (Windows authentication or SQL Server authentication).
Many companies encourage reusing the config files already created. Almost all the time that will not help as your package may make connection to a new server so this document will help create a config file based on how you connect to that server.

Important

If config files are not configured correctly then package "will fail" and it needs proper understanding as to how config files work and what are the different ways of creating config files.

Steps

1. Right click in control flow---> configuration---> add ---> browse and specify where you want to save config file
2. The next step is what is different, normal process of any company would be to select 1 connection per config file.
a. For config file with Windows Authentication you can select all the sub options like
i. Name
ii. Connection string
iii. value
iv. username
v. password
vi. description

Remarks

It is a normal practice to select only what is necessary because when any package is run the first step of a package is to read the config file and the more information a config file has the longer it takes. Performance in SSIS is really important.

Normally you would need:
i. Connection string
ii. Name
iii. Password
iv. Value

b. For config file with Sql Server authentication you need to select only the "Connection string " option
It is really important that you select only the connection string option. This connection string option will contain username, connection string but it will not contain password so you need to manually add password as in the following example :

Note: In the above image look at where the password is placed and the syntax, it goes as** password=************; **

Conclusion

These are the 2 ways of creating config file to work with package, config files are really important to dynamically change the server connection string and its attributes like username and password so that it can be deployed to different server on moments notice instead of opening and changing hardcoded connection string values in connection manager of SSIS package.

Share via