Override SSIS package configurations through an SQL Server 2008 R2 Job

ByAdam Gilmore Updated on
1 Comment

If you thought you could override an SSIS package’s configurations when running it through an SQL Server 2008 R2 Job… think again.

In the SQL Server Job Step Properties dialog, there is a  Configuration tab that allows you to specify configuration files. The logical assumption is that this will override existing configurations with the same configuration file name. Unfortunately this isn’t the case.

A quote from MSDN DTEXEC Utility Documentation: “You can use the /ConfigFile option to load additional configurations at run time that you did not specify at design time. However, you cannot use the /ConfigFile option to replace configured values that you also specified at design time.”

The proper way to use SQL Server 2008 R2 Job Configurations, is to not define configurations in the package, and only specify them in the Job. This means that at design time the packages will need to contain hard coded values for you to run them on your development workstation.  A definite hassle if there are 2 or more developers with different local setups (Drives, Instance names etc). This is the reason, among others, that I always recommend developers have exactly the same configuration, and that it mirrors the production environment if possible.

Adam Gilmore

Adam Gilmore is a Data Architect with 19 years' experience in the Data field. He writes for Dimodelo and develops courses covering Data Analysis to Data Architecture.

Similar Posts

How to add a custom SSIS component to Visual Studio Tool Box

ByAdam Gilmore

This article discusses how to add a Custom SSIS Component to the SSIS Tool Box in Visual Studio, so you can use it in your Control or Data Flows. Custom components are often distributed as DLL files, so some manual installation is usually required. 1. Add the DLL to your system The SSIS BIDS toolbox locates components…

|

Improve performance of SSIS using defaultBufferSize

ByAdam Gilmore

[et_pb_section admin_label=”section”][et_pb_row admin_label=”row”][et_pb_column type=”4_4″][et_pb_text admin_label=”Text” background_layout=”light” text_orientation=”left” text_font_size=”14″ use_border_color=”off” border_color=”#ffffff” border_style=”solid”] Use the defaultBufferSize setting to improve the throughput of SSIS package. Key things you need to know: Each component in an SSIS data flow is assigned a memory buffer. The default is about 10MB. When your source is a wide table (i.e. has many or large…

| | |

Understanding Visual Studio Solution Configuration Manager : Quick Tip

ByAdam Gilmore

Understanding the Visual Studio Solution Configuration Manager Dialog. Below is an example screenshot of a Visual Studio Solution Configuration. This example shows a solution configuration called DEV. Within the project are two projects called Data_Integration and Data_Integration_Database. Associated with the DEV Solution configuration is the DEV Project Configuration of the Data_Integration and Data_Integration_Database projects. The…

| | |

Configuring SSIS Projects for Deployment : Quick Tip

ByAdam Gilmore

When you build an SSIS Project, you can optionally also generate an SSIS deployment Utility. The Deployment Utility can be used to Deploy the SSIS Packages and Configuration Files on a Server. Below is an example SSIS Project configuration, which will trigger the generation of a Deployment Utility. In the image you can see that…

Connecting to Client's Servers via VPN

ByAdam Gilmore

Here at Dimodelo Solutions we often work remotely. With today’s technology, we can use Virtual Private Network (VPN) technology to securely connect into our client’s network and access their Servers, as if we were there on site. All that is required is the appropriately authorized Server credentials (User Name and Password) and a VPN Server configured at the client site. Today,…

|

Defining a Dimension with Smart Keys

ByAdam Gilmore

While conventional wisdom (and Kimball) says that using meaningful identifiers for surrogate keys is a bad idea, there are some exceptions where it makes sense. Typical examples include: Calendar. An example of a Calendar smart key is – year, month, day e.g. 20110721. A DateTime value can be converted to this date format using this statement convert(char(8),date, 112)….

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

One Comment

  1. I know this is a little late for your post, but so far yours is the one that makes the most sense to me. I understand that the BIDS config file will take precedence over the job configuration tab. Here is my confusion, in BIDS I have a specific location I keep the config files. The same file path exists on the prod and dev servers. When I build the deployed version and move it to the prod server, the config file it uses is in the deployed location, not the specified location within BIDS. For example in BIDS the config file is in d:applicationsSSISSSISConnectionsx.dtsconfig but the deployed version is using d:applicationsSSISSSISProjectsxbinx.dtsconfig. I endup with config fiels everywhere. Can I force the deployed version to use the d:applicationsSSISSSISConnections location? Or should I just put everything in the bin directory and have BIDS point to it instead?

    Reply