January 20, 2007

SSIS Package Configurations using SQL Server table...an alternative to the Indirect method

Recently, I was involved in a discussion about how to get the SQL Server based configurations work using the indirect settings; since I have not had the chance of getting into it; I thought it would be helpful to share a technique that yields similar results.

This post describes an approach for implementing SQL Server based package configuration that uses an environment variable to facilitate the deployment of the packages onto different servers without having to modify the packages. I have used this technique for a while, and I think, it may be used as an alternative to the indirect method described in BOL.

Let’s supposed we have a package that has a connection manager called ‘Source’, which has the connection information of the source DB where the package is trying to pull data from.:






Since during the development we are pointing to a development DB instance, we need the ability to change the connection information to point to the QA or Production source databases without the hassle of having to edit the package each time. It is here where package configurations come to the rescue. We have decided that the configuration values will be stored in SQL Server table; hence we need to create an additional connection manager that points to the location of our configuration table in the development environment. The name of this new connection manager will be ‘Configuration’.






The next step is to bring the configuration wizard and create an entry to set the connection string of ‘Source’ connection manager; it should be something like:



As you can see, this package configuration uses 'Configuration' connection manager to get acces to the Configuration table. On this example the name of the table is SSISConfiguration.

So, far we have used the direct method to get access to the configuration table; which is not a very portable solution as we have to manually change the connection information of ‘Configuration’ connection manager. So, here comes the trick: go back to the configuration wizard and create an entry to set the connection string of ‘Configuration’ out of an environment variable. In this example the environment variable is called SSIS_CONFIG; and its value is a connection string that point to the DB where the configuration table resides. Make sure you place this configuration above the other entries that use ‘Configuration’ (in this case, above of SourceConnectionEntry). Here is how my example looks like:




By doing this we have turned the package into a very portable one. Going forward, all we have to do, when deploying the packages into a different server, is to create a variable called SSIS_CONFIG in that server; set the proper connection string as its value, and make sure the rows in the configuration table have the right values.

Just in case, the value for the SSIS_Config is just a connection string that can be consumed by the connection manager; something like:

Provider=SQLNCLI.1;Data Source=MARINERLAPTOP14;Integrated Security=SSPI;Initial Catalog=ETLRafLabDev


I have successfully used this approach using an XML file instead of an environment variable; which comes handy when the use of the second ones are not an option. Please feel free to post your comments.

I hope you find this example helpful.

Update: Since I receive a lot of request asking for a copy of the package, I have make it available in my SkyDrive:


you would have to modify the connection strings to point to DBs that exists in your environment.

23 comments:

  1. Hi Rafael,

    Is that possible to make all your packages downloadable?

    Thanks,
    Silaphet,

    ReplyDelete
  2. Silaphet,
    I am traying right now to find a place to upload the files. If you email me I will forward a copy of the package.

    ReplyDelete
  3. Hi Rafael,

    I would also like to have a copy of this package (if possible).

    If you could send me the package at the following address: fofaucher@gmail.com
    I would appreciate!

    Thanks
    FO

    ReplyDelete
  4. Hi Rafael,

    Can u also send me a copy?

    arthur@euclides.nl

    Thx in advance

    ReplyDelete
  5. We're using that approach
    Just one thing to note though in that if you have a server with multiple SQL Server instances installed this approach does not allow you to port the packages between instances as you'd either have to have instance specific names for the environment variable or face a conflict on it's usage

    ReplyDelete
  6. That is true; this approach does not fit very well when you have multiple instances in the same server; and to be honest I don't see a clean way to implement a solution under that scenario. I have worked in projects where my Dev and QA environments were on the same server; I ended up using a virtual server to get a clear separation of the environments; not always an option though .Thanks for pointing that out.

    ReplyDelete
  7. I would like a copy of this package please.

    ReplyDelete
  8. Could I also have a copy of this package please?
    Or could someone tell me how to create a Connection Manager once I have this SSIS_Config env. variable and the SSIS Configurations table?
    I am just starting with SSIS.
    Thank you.
    Mariola, e-mail mgburzyn@ca.ibm.com

    ReplyDelete
  9. I keep trying this approach but I have a problem because my SSIS connection string value is not set as the environment variable, dont know why..
    Can I get a copy of that package please? quimera [at] gmail [dot] com

    ReplyDelete
  10. Ive just found the problem. I havent restarted Visual Studio. Now it works, thanks!

    ReplyDelete
  11. Did you Import/Deploy that package to another server? If so which one did you use, and did it pickup the EV if you try to run that SSIS package from within Managment Studio?

    ReplyDelete
  12. Hi Rafael,

    Can you please send a copy of the package or configuration files to email splee1987@hotmail.com

    ReplyDelete
  13. Hi Rafael,

    Wonder if you ever uploaded the file? It's hard to view the setup from this link, thanks.

    Chris

    ReplyDelete
  14. This is all very nice, and there's tons of articles out there on how to create SQL and other package configurations to make your packages portable, but in weeks of searching I haven't found a single thread explaining how to use the variable back in your package!

    I have a package-scoped variable, User::FolderName that obviously should read the folder name from the package configuration. My SQL package config contains the value (the folder name) I want to use. But, User::FolderName is always blank when I try to run my package.

    How the **** do you set your package variable to the value in the package config!?

    ReplyDelete
  15. In my situation, my dev and qa instances are on the same physical server. Can I use this approach?

    ReplyDelete
  16. Hi Rafael,

    Could you please send me a copy of the package.

    More over i have an issue. Currently we are migrating our 2005 packages to 2008. All the package does not use all the connection from the configuration. This works fine in 2005. Where as in 2008 i am getting connection collection error. how to resolve this issue. Can you please me. Mail me at preerathi@yahoo.com

    thanks.....Preethi

    ReplyDelete
  17. Rafael, Is it possible to add encryption in this process, i.e. it would be nice to have the passwords for DB connections encrypted ?

    ReplyDelete
  18. n u send copy 2 me
    sikandar@sris-global.com

    ReplyDelete
  19. Hi Rafael,
    I used exactly the method you outlined here, but when using the pacakge from a SQL agent job, I find that the package does not use the Configuration table values at all. Any idea what might be missing? Could this be a permission issue?

    ReplyDelete
  20. Good article. Just what I needed :-)

    ReplyDelete
  21. Pramod paluri12/9/11 6:17 AM

    "Just in case, the value for the SSIS_Config is just a connection string that can be consumed by the connection manager; something like:

    Provider=SQLNCLI.1;Data Source=MARINERLAPTOP14;Integrated Security=SSPI;Initial Catalog=ETLRafLabDev"

    Can we also give SQL Server username and password in the windows environmental variable?

    ReplyDelete
  22. In sql server Configuration approach discussed above No where in the SSISConfig table the name of the package is stored to uniquely identify it.So shall I have to create 10 diff SSISConfig tables for deploying 10 diff diff Master packages??

    Thanks & Regards, Shovan

    ReplyDelete

I will love to hear your feedback and comments. Thanks.