 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / Services / Integration Services / July 2009Configuration File Location in Clustered server
Hi, We have many SSIS Packages. All the connections/filepaths are configured/stored in SQL Table (Configuration.dbo.SSISConfigurations). We have an XML dtsConfig file called "Configurationfile.dtsConfig" to store the connection information for this SSISConfigurations table (which server, which database). Configurationfile.dtsConfig will be stored in the same file path in say C:\SSISConfiguration of dev/qa/prod environment. The file will be edited to have the correct server name corresponding to the environment. With clustered environments, we cannot have the dtsConfig file path as hardcoded to C Drive.We cannot have it in same drive across environments. We may have to put it in E drive in X server & F drive in Y server etc. So we wanted to configure file location in environment variable. We face issue with this approach in one of our Production server, which is an Active-Active Cluster with QA. Prod and QA are setup as Active-Active cluster. Everything is hosted in the same physical server so they have their C Drive, Registry settings, Environment variables etc as common. So we are not able to store the DtsConfig File path in environment variables. We end up having different paths for QA and Prod and this neccessitates editing all SSIS package XML files to change the path of DTSConfig. We tried using SSIS Deployment tool but it is not working as expected. Any suggestion on where to place DtsConfig file path in such Active Active QA Prod cluster environment to avoid manual edit of packages? Thanks, Lalitha Hello Lalitha, Sounds like you have been reading my blog on WordPress concerning SSIS Configurations. What you are going to need is a system variable available inside SSIS that is specific to each INSTANCE, and unfortunately, I don't see one. So you may have to eliminate the dtsConfig file altogether and go with a hard-coded connection to the SSIS Config database. BUT, when you deploy a package to the Production Server and set it to run, you will have to set the SQL Agent Job (or what ever method you use to invoke the ssis execution) to override the connection string of that SSIS Config database, having it point to the proper server/instance. I feel for you on this one. There is no good reason to host TWO environments on ONE machine, and a plethora of reasons NOT to. Good luck.  Signature===== Todd C > Hi, > We have many SSIS Packages. All the connections/filepaths are [quoted text clipped - 24 lines] > Thanks, > Lalitha Hi Todd, Thanks for the response. Yes I did read your blog on SSIS Configurations and it was very helpful... Regarding your suggestion to provide dtsConfig file path during run time in SQL Agent, I did try that approach as well.. But I was having issues because the child packages invoked from a parent ssis package were not able to retrieve the path provided in SQL Agent. May be I have to redesign packages to have global variable and provide dtsConfig path through SQL Agent to the globa variable. This way child packages may be able to fetch them.. But involves lot of rework now :-( Thank you, Lalitha > Hello Lalitha, > Sounds like you have been reading my blog on WordPress concerning SSIS [quoted text clipped - 42 lines] > > Thanks, > > Lalitha You could use the Parent Package Variable as a way to assign values in the Configuration of Child Packages. Once the Parent has the location (Connection String) of the SSIS Config Db, it can pass that along to the children. You could even pass along the Connection Strings of any and all Connection Managers used in the children. On a purist note, this is prefered over having each child go to the XML file/SSIS Config Db to get the info, because it reduces round-trips to the server. But yes, it involves re-work. No way around that. ===== Todd C > Hi Todd, > Thanks for the response. Yes I did read your blog on SSIS Configurations and [quoted text clipped - 57 lines] > > > Thanks, > > > Lalitha |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2010 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.