July 28, 2009 at 12:14 am
I have now implemented a configuration scheme like this, and thought that I would share my learnings.
I had to add "Instead Of" triggers to my view to manage the inserts, updates and deletes of config items. Without this, the records were inserted with null environments, and the udpates got errors when setting configuration values.
I also have a case where two environments share a server. Earlier in this comment list it is suggested that the program_name() function be used to separate the different environments. I do not think that this is possible, as the connection used for reading SQL Server configurations is not configurable, i.e. you cannot change the "Application Name" in the scheduled job for that connection. We do have different user accounts for the separate environments, so we ended up using the user name, so scheduling the package as the QA user picks up the QA settings, and as the UAT user picks up the UAT settings.
July 28, 2009 at 12:22 am
I have now implemented a configuration scheme like this, and thought that I would share my learnings.
I had to add "Instead Of" triggers to my view to manage the inserts, updates and deletes of config items. Without this, the records were inserted with null environments, and the udpates got errors when setting configuration values.
I also have a case where two environments share a server. Earlier in this comment list it is suggested that the program_name() function be used to separate the different environments. I do not think that this is possible, as the connection used for reading SQL Server configurations is not configurable, i.e. you cannot change the "Application Name" in the scheduled job for that connection. We do have different user accounts for the separate environments, so we ended up using the user name, so scheduling the package as the QA user picks up the QA settings, and as the UAT user picks up the UAT settings.
November 11, 2009 at 12:08 pm
Okay, so I've implimented this and I like it, a lot! I am having an issue that I'm hoping anybody can help me resolve. I am a consultant and the client Im at doesn't have a standard UAT Integration Services environment (dont ask ;). As a result, we deploy the packages to the Dev environment with Package Config enabled, but I override a couple values using the Data Values tab in the SQL Job. It works great for the database connections (data source tab) and for 2 INT variables, but one of the variables that needs to be set for the UAT environment is a folder directory where some files are located.
It appears the SQL Job isn't passing in my string value correctly. Its a basic UNC path to a folder, but the path does have some spaces it a couple of the directory names and I think this is where its blowing up. In the SSIS pkg, I have a script task that uses this path to do stuff with the files but I keep getting errors with everything I've tried thus far. The simplest solution is to quote this entire string in the SQL Job, the error I get is "Invalid characters in path". The errors I get are from inside the package and not from the job. Furthermore, I can execute using dtexec by just quoting my string and using dbl back slashes and that works, so this says to me the SQL Job isnt passing the string into the package properly.
This is only a problem when I try overriding these values through the SQL Job. Using the Config normally or in Debug mode everything works fine with the string value I'm trying to use.
Examples of what I've tried:
"\\server\dir1\dir 2\" - error "invalid characters in path"
\\\\server\\dir1\\dir 2\\ (with and w/o quotes around the whole string). This error shows me the correct path, but says it cant find the file.
\/\/server\/dir1\/dir 2\/ (with and w/o quotes around the whole string). My VB script task doesnt error but doesnt do anything. The next step in the package (to open the file) gives the error: No destination flat file name was provided.
//server/dir1/dir 2/ generated an error in the next step in the package. Doesnt generate an error, but the package doesnt do what its suppose to.
\\server\dir1\dir%202\ (with and w/o quotes around the whole string). Illegal characters in path.
Any feedback is greatly appreaciated!
November 11, 2009 at 12:32 pm
I think you may need to escape the quotes too... I vaguely recall running into something like this myself a couple of years back... Something to do with the quotes being stripped out at runtime?? I think it depends on how you call the package from the SQL agent. i.e. If you call DTEXEC from the cmd option or do you use the Integration Services option.
HTH
Kindest Regards,
Frank Bazan
November 11, 2009 at 1:22 pm
jdurandt (7/28/2009)
Earlier in this comment list it is suggested that the program_name() function be used to separate the different environments. I do not think that this is possible, as the connection used for reading SQL Server configurations is not configurable, i.e. you cannot change the "Application Name" in the scheduled job for that connection.
You can configure the connection used for SQL configuration, but not with a command-line configuration. Command-line configs are applied after the other package configs. The SQL Agent SSIS Task builder is just a fancy interface for creating DTEXEC commands, with any changes you make added as command-line parameters.
You can use indirect configuration to get the SQL Config connection string from an environment variable, for instance, assuming that config item appears earlier in the list than the SQL config items. Then you could take the DTEXEC command from your existing SSIS job step and put it in a bat file after some SET commands for the environment variables.
Not that I'm recommending this approach, I just had to mention it as a possibility. Every additional level of indirection makes it more difficult to manage and debug.
December 23, 2009 at 8:35 am
adrinkwine (11/11/2009)
It appears the SQL Job isn't passing in my string value correctly. Its a basic UNC path to a folder, but the path does have some spaces it a couple of the directory names and I think this is where its blowing up.
Here is what I use to pass in a string variable that may contain spaces etc. The \" is for double quote with the backslash being the escape character. In the event your string variable value will be empty you must pass in two single quotes instead.
/SET "\"\Package.Variables[MyVariable].Value\";\"\\servername\foldername\filename.txt\""
One little caveat to doing this, Sql Agent doesn't treat this very nicely, it will mess it up when you save the file so you have to manually change it using the procs for the job. I create jobs through the .NET interface so its not an issue, but if you're using the SQL Agent beware it's going to fubar it.
December 20, 2010 at 1:42 pm
Hi Scott,
If Properties that are the same in all environments have one record with a value of 0, then how come we acess them when environment changes bcause production is say 1, development is say 2 and so on. Since the properties are environment dependent how come we point the single property values in different servers? I am really struck at this point. Please clarify my point
Thanks,
Siva
December 20, 2010 at 2:00 pm
Hi Scott,
We ran into an interesting issue. We are running our jobs on application database servers arapt from regular database servers( prod,dev,test). So the concept of hostname() is not working because it is pointing to only Application production servers which is purely dedicated to run SQL agent jobs. I don't have clue to proceed further. Please help me out with this.
Thanks,
Siva
December 21, 2010 at 6:46 am
The dev and test servers I use have copies of the production databases on separate servers, so when configuring connections the server name is different for each environment but the database name (initial catalog) is the same in every environment.
If you have multiple dev, test, and prod servers for different application domains you can define more than one server as dev, test, or prod. My setup has evolved to use a table to define the roles for a large number of servers.
CREATE VIEW [dbo].[SSIS_Config] AS
SELECT ConfigurationFilter, PackagePath, ConfiguredValueType, ConfiguredValue
FROM dbo.SSIS_Config_base b
JOIN (
SELECT CurrentEnvironment = ISNULL( ( SELECT Environment FROM ServerEnvironment WHERE ServerName = HOST_NAME() ), 2 )
) e
ON b.EnvironmentEnum = 0 OR b.EnvironmentEnum = e.CurrentEnvironment
If you need more separation to isolate different groups of SSIS packages you could put the configuration for each domain in different servers and/or databases.
April 4, 2011 at 1:19 pm
Hi, Scott. Thanks for the great article. To clarify a point that you made in the comments, to configure the configuration connection string, you have to use an environment variable? Otherwise, simply use a dedicated configuration connection? This is always the case with SSIS and SQL Server configuration?
Thanks...Chris
August 20, 2011 at 11:10 am
(Sorry for the late response, I've been ignoring my generic email because it's full of junk.)
You can configure the connection for SQL configuration several ways, including environment variables. What's important to keep in mind are the ways you can't configure them.
You can't configure the configuration connection with a command line switch, as this switch will not be processed until after all the configuration settings have been read. Ditto for parent package configurations.
Any other direct configuration technique (environment variable, XML file, a separate SQL configuration) can modify the connection manager to control the source for later SQL configurations.
Expressions on the SQL config connection manager will not be evaluated until after the configuration settings are read, so the connection manager properties must be configured directly and not through a package variable.
August 30, 2011 at 6:40 am
No worries on the delay, Scott. I understand how it can be with the deluge of e-mails.
The behavior that you mentioned, not having the ability to use a command-line switch, has changed in SQL 2008 right? You can use the /connection switch to set the connection from which you would obtain configuration values. (http://msdn.microsoft.com/en-us/library/ms141682.aspx)
I work in an environment where we cannot use environment variables and using XML files is not an option either (obtaining the permissions on the network shares for the SQL Server accounts would take FOREVER, if possible!), so we use SQL Server configuration, but with the /connection switch. It's not as robust as your solution, but it works. And I will keep your solution in mind for different environments.
Thanks...Chris
September 15, 2011 at 5:05 pm
I second the earlier question... How would you apply this to environments with firewalls, or in my case security restrictions, between application tiers? I currently have this set up in my company with Dev, Stage, and Prod all hitting the same Production SSIS_Configurations database, and it is working, but I get constant push-back from engineering on why Stage is hitting a Production table. We used to have two separate SSIS_Configurations databases, one for Prod and one from Dev/Stage, but it was high-maintenance, and to me that seemed to defeat the purpose, which is to manage everything from one table. Has anyone else had this problem?
October 8, 2011 at 3:25 pm
Hello Scott,
Very useful article.
I've some different scenerio. I've one server where all the SSIS package jobs runs. I would like to have only one SSIS package to run in all the different prod servers. Luckly, this SSIS package is using same database in all the environments.
Your help is appreciated.
July 24, 2012 at 12:14 pm
I know this is an old post, but I have a question about this approach. I have used this in multiple servers in the past with no issues, but my current customer is using the same physical server for dev and qa. This means I get the same HOST_NAME() when running SSIS packages in both environments.
Is these any way to make this work that will allow me to deploy packages and run via SQL agent jobs on both environments? Is there any way for the SSIS package to distinguish between them and return the proper rows in the view?
Thanks,
Kim
Viewing 15 posts - 31 through 45 (of 57 total)
You must be logged in to reply to this topic. Login to reply