Dynamic SSIS Configuration from Sql Server table while job scheduling

viral baitule 1 Reputation point
2021-11-19T02:13:59.283+00:00

Hi ,
i am trying to set the configuration for ssis package at run time from sql server table when scheduling job.
i tried using set values options in sql server agent
Property Path : \Package.Configurations.Configuration.Properties[ConfigurationString]
Value : LocalHost.Databasename;[dbo].[SSISConfigurations];package1config_prod

package1config_prod is the ConfigurationFilter from SSISConfigurations table .

i want to apply all the ConfiguredValue from SSISConfigurations table for package1config_prod ( ConfigurationFilter)

Can some please help .

Thanks

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,643 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. ZoeHui-MSFT 40,586 Reputation points
    2021-11-19T08:30:14.8+00:00

    Hi @viral baitule ,

    I did not find a good way to dynamic SSIS configuration when job scheduling.

    There are two links explain DYNAMIC PACKAGE CONFIGURATION in SSDT.

    dynamic-package-configuration-8533.html

    making-ssis-dynamic-configuration-manage

    Do you mean that you want to use TSQL to add ConfiguredValue from SSISConfigurations table to ConfigurationFilter table?

    Regards,

    Zoe


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  2. viral baitule 1 Reputation point
    2021-11-19T17:46:41.373+00:00

    Hi @ZoeHui-MSFT ,
    no we dont want to utilize the tsql, that would add additional step in each package,
    i know how to do it using xml config file when job scheduling , but we want to utilize sql server for config

    to give an example,
    lets say i have package name testpackage and now i want to create 4 different jobs for this package(testpackage) but with 4 different configuration.
    one of the way for doing this is using xml file config(we are currently using it just but adding different config in each job).
    but i want supply those 4 different config from sql server table (SSISConfigurations)
    schema for table looks like

    CREATE TABLE [dbo].[SSISConfigurations]
    (
    ConfigurationFilter NVARCHAR(255) NOT NULL,
    ConfiguredValue NVARCHAR(255) NULL,
    PackagePath NVARCHAR(255) NOT NULL,
    ConfiguredValueType NVARCHAR(20) NOT NULL
    )
    this is the link for reference for ssisconfiguration (https://www.tutorialgateway.org/ssis-package-configuration-using-sql-server/)

    i was looking for some way to change the configurationstring using set value option "\Package.Configurations.Configuration.Properties[ConfigurationString]"
    and specify "LocalHost.Databasename;[dbo].[SSISConfigurations];package1config_prod" inwhich i can change configfilter name
    but it didnt worked

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.