SSIS multi-environment configuration in a single SQL Server table

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Scott Coleman » SSIS multi-environment configuration in a...

47 posts, Page 4 of 5

«««1 2 3 4 5 »»

SSIS multi-environment configuration in a single SQL Server table

Rate Topic

Display Mode

Topic Options

Author

Message

jdurandt

Posted Tuesday, July 28, 2009 12:14 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 12:40 PM
Points: 54, Visits: 145

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.

Post #760524

jdurandt

Posted Tuesday, July 28, 2009 12:22 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 12:40 PM
Points: 54, Visits: 145

Post #760527

adrinkwine

Posted Wednesday, November 11, 2009 12:08 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, January 04, 2013 12:35 PM
Points: 11, Visits: 98

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!

Post #817375

Frank Bazan

Posted Wednesday, November 11, 2009 12:32 PM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 06, 2013 3:02 PM
Points: 268, Visits: 1,055

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
@Bikeride2Africa

Post #817392

Scott Coleman

Posted Wednesday, November 11, 2009 1:22 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:39 PM
Points: 2,818, Visits: 1,038

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.

Post #817423

Bradley Deem

Posted Wednesday, December 23, 2009 8:35 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, May 17, 2013 9:22 AM
Points: 551, Visits: 1,150

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.

Post #838556

sivaprasadyellala

Posted Monday, December 20, 2010 1:42 PM

SSC Journeyman

Group: General Forum Members
Last Login: Saturday, May 18, 2013 10:24 PM
Points: 82, Visits: 301

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

Post #1037377

sivaprasadyellala

Posted Monday, December 20, 2010 2:00 PM

SSC Journeyman

Group: General Forum Members
Last Login: Saturday, May 18, 2013 10:24 PM
Points: 82, Visits: 301

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

Post #1037391

Scott Coleman

Posted Tuesday, December 21, 2010 6:46 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:39 PM
Points: 2,818, Visits: 1,038

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.

Post #1037677

CGSJohnson

Posted Monday, April 04, 2011 1:19 PM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 1:31 AM
Points: 1,385, Visits: 1,085

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

Post #1088253

« Prev Topic | Next Topic »

47 posts, Page 4 of 5

«««1 2 3 4 5 »»

Permissions