Can Not Read Config File in Ssis

Now in SQL 2012 we take parameters that make information technology easy, just configuration files are still an selection and I withal see a lot of my clients using them even on 2012 due to several reasons, just mostly because of the work to convert over.

SSIS packages are great ETL tools and tin can do but almost annihilation you need in terms of ETL. Most organizations start out creating SSIS packet ane by i until they have dozens, hundreds, or even thousands of packages. I accept worked with one client that ran over 4,000 packages. This tin be a nightmare to maintain. You can save yourself a lot of work by deciding upfront how to configure your packages using configuration files or tables. We are going to hash out configuration files in this article.

We are going to look at a simple example of passing information to a package with a configuration file. Then we will become over using configuration files on multiple packages. Imagine running dozens of packages that signal to a server and the server proper name changes. If you lot have a configuration file that is feeding this server proper name to every package you lot tin can brand a single change to the configuration file and all the packages are updated. This tin can reduce your maintenance time significantly.

Here is a simple package example:

1. Elevate in a script task into a blank SSIS package.

ii. Create a string variable on the package named strData

iii. Set up the value of the variable to "Package"

four. Double click on the script task.

5. Add the strData variable to the read but variables.

6. Click Edit Script

7. Under the principal function add the code MsgBox(Dts.Variables("strData").Value)

8. Click save and so close the window

9. Close the script editor past clicking ok

10. Run the package

When you run the package a popup box appears show the work Parcel. This is the value of the variable saved in the package. Now we volition set up a configuration file on the bundle to give u.s. the ability to change the value of the variable from outside the bundle.

i. Close the popup box and stop the parcel.

2. Right click in the control flow and select Bundle Configurations.

iii. Place a check in Enable Package Configurations.

iv. Click Add.

5. Click Next in the in the welcome screen if information technology appears.

6. Click on browse and select a location y'all have rights to write to.

seven. Proper noun the file FirstConfig

8. Click salvage and so click next

9. Click the plus next to variables >strData > Properties

x. Identify a check next to value, notice the value on the right

11. Click Side by side > Finish > Close

We now have a configuration file on the parcel but the value is still the value from the package. Now we will open the configuration file and modify the value. The configuration file is an XML file and I like to apply XML notepad (Gratuitous from Microsoft) to open information technology. You volition await for the configured value in this file. This configured value is the value passed to the package.

one. Open the folder containing the configuration file

ii. Open the configuration file past correct clicking and select open up with

3. Select a program you tin apply to edit the file (Example: Notepad, Wordpad, XML notepad)

4. Check the configured value from Package to Config

5. Salvage and Close the File

six. Return to the package and run it

You should see a popup showing Config. This is the value from the configuration file. The value saved in the bundle is overwritten at run time.

This is only i small example of using configuration files. A popular fashion to use configuration files is on connections. When you lot have a connection on a package the properties of this connection show in the configuration manager. You tin can place a check adjacent to the connection string property or you can place a check adjacent to the individual elements that make up the connection string, initial itemize, server name, user name, and password. The user name and password are not needed when using windows authentication.

The countersign is not stored in the configuration file automatically even if you lot select it in the configuration managing director. This is done by blueprint for security. Microsoft did non desire y'all saving your configuration file in plain text without knowing it. So y'all volition have to open the configuration file and add together the password. If you selected the connection string the password will become right after the user name. You must type in "Countersign =####;", (#### Represents your password). Don't forget the semicolon after the password. Now this configuration file can exist used in whatever other package using this connexion.

There is an issue when using a configuration file in multiple packages. The bundle that is using the configuration files will effort to load every connection in the configuration file. It the package does not contain that connection it volition neglect validation and the parcel will not run. This causes an issue when trying to share a configuration file with many packages. There are three methods for handling these issues. You tin can create a configuration file for each parcel or create a configuration file for each connection or a combination of both.

The first method of a configuration file for each bundle works well if you do not take a lot of packages. If you have a thousand connections and l packages, a per-package solution is the obvious pick. If every package has a different set of connections this is almost necessary.

The second method of a configuration file for each connection works well if you accept a lot of packages and fewer connections. If you have 50 connection and a thousand packages it volition be much easier to maintain a per connectedness solution. In this situation a packet with ten connections would have ten configuration files, each with one connectedness.

The third pick is to combine the first two options in some course. For example, if y'all have one connection that is used by every package use this configuration file in every package. The other connections can have a package level configuration file. This is harder to maintain and yous need certificate which packages are using which configuration files.

With all the options of configuration files it is important to program out how you will use them in your surround before you create thousands of packages and create a maintenance nightmare. Planning your SSIS parcel configuration architecture is of import and should not exist over looked. It is like shooting fish in a barrel to put it off when y'all only have a couple of packages. Nearly environments accept their packages abound in number faster than anticipated. Planning your configuration files volition save you a huge retro fit project in the future.

davisginusbact.blogspot.com

Source: https://mikedavissql.com/2014/03/17/using-configuration-files-in-ssis/

Share this post