CODEWITHSUNDEEP
sql-serverssis
Steam
Steam

Reputation: 9856

SSIS Deploy package - Too many configurables - how to do quickly?

I am trying to create a deployment config/file for package. I have many configurables like variables, connection managers etc. For variables, I only want to configure the value and nothing else. I don't want to go to each variable and then click the checkbox for that. Is there a quicker way of configuring the package for deployment ?

Upvotes: 0

Views: 101

Answers (1)

billinkc
billinkc

Reputation: 61249

Once you have defined that a package uses a configuration, you can close the package, edit the configuration source (file or table entries) and presto, one configuration becomes 30.

I've created a new package and added some variables. Using the wizard, I'm going to put the Variable Blasto's value property into the configuration file.

nothing up my sleeve

I establish configuration for only the Variable Blasto

Package Configuration Wizard

Verifying that what will be built out into my config file matches previous screenshot

Configuration confirmation

The resulting XML would look like

<?xml version="1.0"?>
<DTSConfiguration>
  <DTSConfigurationHeading>
    <DTSConfigurationFileInfo GeneratedBy="home\bfellows" GeneratedFromPackageName="Package1" GeneratedFromPackageID="{085B2116-4597-4E3A-8659-2815C82E5055}" GeneratedDate="1/15/2014 12:34:37 PM"/>
  </DTSConfigurationHeading>
  <Configuration ConfiguredType="Property" Path="\Package.Variables[User::Blasto].Properties[Value]" ValueType="Int32">
    <ConfiguredValue>-1</ConfiguredValue>
  </Configuration>
</DTSConfiguration>

If you went back in and selected Variable3, the net result would be that one more entry would be added to the existing configuration file. My approach, especially when I've forgotten to have something configured after deploying is to just edit the configuration entity. I used SQL Server tables but the same holds true with XML.

When the Configurations are applied, for SQL Server and XML sources, those can define collections of configurations to apply in contrast with environment variable or registry (I'm too lazy to gin up a parent/child configuration).

If I copy, paste, edit the lines specific to the Configuration tag to include these 3 variables

  <Configuration ConfiguredType="Property" Path="\Package.Variables[User::Variable].Properties[Value]" ValueType="Int32">
    <ConfiguredValue>100</ConfiguredValue>
  </Configuration>
  <Configuration ConfiguredType="Property" Path="\Package.Variables[User::Variable1].Properties[Value]" ValueType="Int32">
    <ConfiguredValue>200</ConfiguredValue>
  </Configuration>      <Configuration ConfiguredType="Property" Path="\Package.Variables[User::Variable2].Properties[Value]" ValueType="Int32">
    <ConfiguredValue>300</ConfiguredValue>
  </Configuration>

Bickety bam, when I reopen the package it discovers those 3 variables are now driven by configuration.

enter image description here

Caveats

Casing and path to the Variables is important. A broken configuration file will not cause the package to abort. Instead, you get a wee lil' warning that says a configuration was missed but don't worry, it will run with the design-time values. Had a colleague who managed to mix dev data into a production environment due to this. Big "oopsie" on their part but just drives home the mantra of "warnings are just errors waiting to grow up"

Warnings for missed configurations

Upvotes: 2

Related Questions