Saving SSIS Variable values in SQL Database

By | July 18, 2014

Have you ever wondered how to make your SSIS more dynamic by putting your variable values in SQL server? Well its quite straightforward all you need to know is to enable package configuration on your solution.

To do that is quite simple as well, lets say you have the following variables, lets concentrate with the variable name IsProduction which is of boolean type.

0 Variable

Now go to your package properties and look for Configurations then click on the ellipsis after the collection

1 Configuration Collection

It will open the Package Configuration Organizer, tick the Enable package configuration and add the variable you want to store into the database.

2 Enable Package Configuration

For configuration select SQL Server then set up a new connection if nothing is available.

3 Create Config Tables

Set up a new configuration table as well and you will be presented with a create table script.

4 Create Tables

Set up the configuration filter name it similar to your variable so its not confusing.

5 Complete Setup

Then click next and choose the variable value.

6 Variable Value

This will then map the local variable with the database stored variable after you clicked finish.

7 Final Step

Now you can see the configuration is saved.

8 Completed

And the package configuration is now enabled on the design screen, so you can access it easily when you want to add and map new variables.

9 Package Configuration Enabled

Double check if its in the database, now you can alter the ConfiguredValue via application or if you’re lazy manually.

10 SQL Data

Happy Coding Everyone!

Leave a Reply