Post reply

How to set Config File

  • June 5, 2012 at 4:47 am

    #274997

    Hi,

    Created a SSIS package in Dev.

    Created a table Sample_Config in database with below columns with datatype nvarchar

    ConfigurationFilter, ConfiguredValue, PackagePath

    Need to create a config file using SQL Server, so that the package will be run in Dev and test environment if we updated the table.

    Please help me how to create config file and how to update the table

  • June 5, 2012 at 5:01 am

    #1496972

    Open the package in BIDS.

    Right click and select "Package Configurations..." or click on SSIS menu and select "Package Configurations..."

    Click on "Enable package configuration" check box.

    Click on "Add..." button.

    ....

  • June 5, 2012 at 5:31 am

    #1496979

    Thanks Suresh.

    But i need to set the config file from SQL Server.

    Inserted the config script in Sample_Config table.

    Done the following things.

    Package Configuration -----? Right Click -----------?(Wizard Opened) Selected Config Type as SQL Server -----? selected connection and config table

    I need to know the other steps

  • June 5, 2012 at 6:57 am

    #1497019

    What I understand from question is:

    You need to enable SQL configurations for your package.

    For this you need to have two connection first connection will point your package to SQL configuration table and second connection will be the SQL configuration table.(This is called indirect configuration)

    For First connection you can create an environment variable or you can creat a XML configuration file.

    In Env variable: value would be.

    Data Source=<Your database server>;Initial Catalog=<Configuration database name>;Provider=< Use a provider I use SQLNCLI10.1>;Integrated Security=SSPI;

    For SQL configuration step. You need to have one entry for every connection.

    That entry should be chosen as Configuration filter for that particularr connection.

    For example you have two connection one for SQL and one for Oracle you will have two configuration entries in the SQL config table. While in the package there should be three entries one for environment variable and one one for Oracle and SQL connection each.

  • June 5, 2012 at 9:10 am

    #1497114

    Thanks shailesh thapliyal

    🙂

  • June 5, 2012 at 11:50 pm

    #1497403

    Is it necessary to use XML config or Environment variable? Without using them, is there any other way to use SQL Server table for setting configuration?

  • June 6, 2012 at 12:20 am

    #1497414

    sqlstud (6/5/2012)

    Is it necessary to use XML config or Environment variable? Without using them, is there any other way to use SQL Server table for setting configuration?

    Hi Sqlstud,

    No it is not necessary to use XML config or Environment variable, but, it is of great benefit, anyway, you can setup an Sql Server table to set your packages configurations; please follow these steps:

    1. You need a table at your Sql Server (I named mine SSIS Configurations), the table structure is shown below, with some records.

    2. Right-click at your package control flow and select Package Configuration as shown below

    3. You will get an screen like the one below, those entries are part of my tests, in your case you will get it empty.

    4. Select Sql Server from the Configuration Type drop-down, as shown below

    5. Select Sql Server details to reach your configuration table, e.g. SSIS Configurations shown at step 1; you will need to create a connection (in your package) to the Sql Server containing your configuration table before you can add use it as described by these steps.

    Cheers,

    Hope this helps,
    Rock from VbCity

  • June 6, 2012 at 12:37 am

    #1497422

    Thanks Rock..

    But i need to use it in different environments ie Dev and Test etc...

    How can we pointing to different environments?

    If we run the package, it should take the corresponding environment details from the table and to be execute accordingly

    Regards

    SqlStud

  • June 6, 2012 at 12:43 am

    #1497423

    Rock from VbCity (6/6/2012)

    ... please follow these steps:

    ...

    Thanks for the detailed post with screen shots.

    You can publish it as a blog or article. Becuase, I believe no one has published anything about it so far.

    Regards.

  • June 6, 2012 at 1:04 am

    #1497427

    sqlstud (6/6/2012)

    Thanks Rock..

    But i need to use it in different environments ie Dev and Test etc...

    How can we pointing to different environments?

    If we run the package, it should take the corresponding environment details from the table and to be execute accordingly

    Regards

    SqlStud

    That is when an external Xml file and environment variable work together, you turned off that option

    Hope this helps,
    Rock from VbCity

  • June 6, 2012 at 1:11 am

    #1497428

    Suresh B. (6/6/2012)

    Rock from VbCity (6/6/2012)

    ... please follow these steps:

    ...

    Thanks for the detailed post with screen shots.

    You can publish it as a blog or article. Becuase, I believe no one has published anything about it so far.

    Regards.

    Thanks Sûresh,

    I will follow your advice, I am fairly new to SSC and unfamiliar with its features, I think I should email Steve Jones to find out more; I had written articles in the past at VbCity.

    cheers

    Hope this helps,
    Rock from VbCity

  • June 6, 2012 at 1:17 am

    #1497431

    Thanks Rock.

    That means, if we update the table to Dev, it will execute it in Dev environment and update the tabel to test, it will execute it in Test environment?

    ( I cant use any xml and environment variable?

    Whether my understanding is correct?

    Regards

    SqlStud

  • June 6, 2012 at 1:28 am

    #1497435

    Your package will have a connection to a database containing the configuration table, if your dev and prd enviroments share this server-database you will be fine, if they do not, then you should consider the external xml config file as a minimum.

    Hope this helps,
    Rock from VbCity

  • June 6, 2012 at 1:40 am

    #1497444

    Thanks Rock.

    I have the below Configuredvalue in SQL server table for config

    Data Source=Sample_Dev;Initial Catalog=Medical_Loss;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;Packet Size=32767;Application Name=SSIS-Load OAM_Data-{C1404D63-52E4-4629-86C5-7CF476146C02} Sample_Dev. Medical_Loss;

    Dev Server : Sample_Dev

    Db Name : Medical_Loss

    If we update the test server and db name in the table, whether it will be executed?

    Test Server : Sample_Test

    DB name : Medical_Loss

    Regards

    Sqlstud

  • June 6, 2012 at 1:47 am

    #1497445

    Actually sqlstud,

    There is a third option, setting up a package parameter taking the name of your target (dev or prd) server, then internally build the connection string that you will be using.

    Hope this helps,
    Rock from VbCity

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply