SQL Server Business Intelligence

SQL SERVER 2005 & 2008 IS THE MAIN THEME OF THIS BLOG

Saturday, December 31, 2005

Package Configurations - DTSConfig File

SSIS supports 5 different types of sources to use the setting the properties of objects and they are :

  1. XML Configuration File
  2. Environment Variable
  3. Registry Entry
  4. Parent Package Variable
  5. SQL Server

I have used XML configuration file to do all the SQL Server connection settings. In the future if you wish to include more connections / settings you can add to this configuration file. If you continue to use this method, you can execute SSIS Packages in different environments without making any changes to the packages. Environments.dtsconfig is the only file you would have to change between environments.

Configuration file is located at C:\Config\Enironments.dtsconfig

This configuration file should exist at the same place in all environments. During runtime SSIS package will pickup the setting from that location and update the connection settings according to the XML configuration file.

XML configuration file will look like the one in the link below:
http://www.smart-bi-solutions.com/BlogStuff/31122005_01.jpg

When you migrate the SSIS packages from one environment (Dev) to the other (UAT), you would need to do the following to the configuration file :

  1. Copy “Enironments.dtsconfig” from your dev environment to UAT environment. Please make sure that you place this configuration file in the same location as development.
  2. Edit “Enironments.dtsconfig” using WordPad or something similar.
  3. Rename Data Source values. For example if your development environment is called “Beast\SQL2005” and UAT environment is called “Sql2005\Uat”. You need to search and replace the value for data source.
  4. Save the file.

Thanks
Sutha

10 Comments:

  • At 2:09 AM, Blogger gowthaman said…

    I got created dtsConfig file but how can i use that config file for that package in another desktop system.

    Thanks,
    Gowdhaman

     
  • At 2:11 AM, Blogger Sutha Thiru said…

    If you do move it to different server / desktop you would need to change the servername in the dtsconfig file. You can modify the file in VS or notepad.

    Thanks
    Sutha

     
  • At 10:37 AM, Blogger Dutchman said…

    Hi Sutha,

    I am using SSIS and I have ome parent package and two child packages. Can the child packages usethe config file created in the parent package? In my case, when I attempt to add variables of the child package, the original contents (variables of the parent package) get wiped out. What am i doing wrong? Any suggestions?
    Thanks in advance.

     
  • At 11:37 PM, Blogger Sutha Thiru said…

    Sorry for the delay. I was on holiday until today. You probably have got it fixed by now.

    I am sure you know all these. You have to add Package Configurations under SSIS on BIDS. Select "add" and select configuration type called "Parent Package Variable". Then you have to enter the exact variable name which is passed down to this child package and click next. Finally you need to map the Value (under properties) for a given local variable. I can provide you a working example if you like.

    Thanks
    Sutha

     
  • At 11:37 PM, Blogger Sutha Thiru said…

    Sorry for the delay. I was on holiday until today. You probably have got it fixed by now.

    I am sure you know all these. You have to add Package Configurations under SSIS on BIDS. Select "add" and select configuration type called "Parent Package Variable". Then you have to enter the exact variable name which is passed down to this child package and click next. Finally you need to map the Value (under properties) for a given local variable. I can provide you a working example if you like.

    Thanks
    Sutha

     
  • At 1:35 AM, Blogger Akshay said…

    Hi Sudha,

    We are using Indirect XML Configuration. we have stored the configuration in XML file and have stored the path of XML file in Environment variable.

    We are using a master and child package. On Production server we have not created any Environment vairable. While executing the master package we give the cofiguration file path and it succesfully take the path and connect to database.

    The proble we are facing is that it not able to use that config file path in the child package. We have read about the Parent Package configuation, but we do not want to set any value.

    We simply need that the same confiuration file to be used in child packages also. Can you please help us out. At Production environment we do not want to create any environment variable.

    Thanks in advance.

    Regards
    Akshay

     
  • At 12:51 PM, Blogger Russell Vaitha said…

    Hi Sutha,

    I was hoping you could provide an example where we can link Package Configurations to various components in the SSIS Package. I have created my xml configuration file but am not sure how to link it to my Package Components (for example : Execute a SQL Task) ..Thanks

     
  • At 1:17 PM, Blogger Sutha Thiru said…

    I am not sure I am understanding the question well. The XML configuration can be used in number of way. Recommend way is to have the intitial database connection defined in there, which would enable you retrieve all the information from a table and set connection string etc during runtime dynamically. Feel free to mail me back with your email and I will send you an example?

     
  • At 9:35 PM, Blogger prabhu said…

    Hi Sutha,

    I have created an SSIS package which uses xml dtsconfig file. I have configured the xml path in developement enivronment. The problem I have is I want to deploy the package on production server. I want to handle or change the xml dtsconfig path where my ssis package was configured in the developement environment without opening the ssis package. I am new ssis developement. Can you help me out how to point my ssis package to look for xml dtsconfig file in different path without opening ssis package.

    Regards,
    Prabhu.S

     
  • At 9:36 PM, Blogger prabhu said…

    Hi Sutha,

    I have created an SSIS package which uses xml dtsconfig file. I have configured the xml path in developement enivronment. The problem I have is I want to deploy the package on production server. I want to handle or change the xml dtsconfig path where my ssis package was configured in the developement environment without opening the ssis package. I am new ssis developement. Can you help me out how to point my ssis package to look for xml dtsconfig file in different path without opening ssis package.

    My email Id: prabhus4mail@gmail.com

    Regards,
    Prabhu.S

     

Post a Comment

Links to this post:

Create a Link

<< Home