Post reply

SSIS Configuration File Problem

  • September 1, 2011 at 7:42 am

    #244100

    What is the best way to solve SSIS configuration file problem

    Many Thanks

  • September 1, 2011 at 10:27 am

    #1376059

    Can you provide some detail as to what problem that you're having? No one can help you if you don't describe what's wrong.

  • September 1, 2011 at 3:39 pm

    #1376207

    thanks for your reply...I am looking at keeping my parameters in SQL server....and retrieving them from there..... when i need them.... to solve the configuration files from going back to the default server

  • September 2, 2011 at 5:59 am

    #1376384

    I don't know if I can offer any assistance. I've not used table-based configurations, all of the SSIS solutions that I build use config files without a problem. I always use a parent package to call all others. The parent package uses a config file, and all of the child packages use parent package variables. The migration procedure where I work allows for changing the path of the config file in the parent package when they are deployed out of Subversion. Inherited parent package variables don't get overridden at runtime in SSIS 2008.

  • September 2, 2011 at 6:03 am

    #1376386

    Have a look here:

    http://msdn.microsoft.com/en-us/library/cc895212.aspx

    You can store config parameters in a database.

    If you want help with anything specific, I'd ask a specific question, taking some care to explain it.

  • September 2, 2011 at 7:12 am

    #1376411

    Thanks for the replies,

    What I have done so far is :

    1. I have created 2 tables with relationship, one is describing what the package does and the other storing package parameters

    2. I have created Excute sql task to take this parameters using the resultset and variables and load this into the package.

    What I want to ultimately achieve is to remove the hard-coded conn string and take all these confiuration from the database i have set up

    Any help and advice will be appreciated

  • September 2, 2011 at 7:21 am

    #1376419

    OK, if I'm understanding you correctly, you're effectively using a custom configuration rather than using SSIS's configuration settings.

    That's fine, but at the end of the day, something has to tell your package where it needs to find the database.

    The classic way to do this is with SSIS's built in configuration system (which I admit is quite awkward at times).

    If you look at the link I showed above, you can create a XML configuration file which holds the connection string for your database. This can then be configured to different values for different environments (test, live etc.) without having to have a different version of the package for each environment. The only really annoying thing about this is that you can't have a relative path to the config file (in the same way as you can with a .Net config file), so you must have the same drive letters/paths to the config files on each server where the package is configured

  • September 2, 2011 at 7:31 am

    #1376428

    the use of the database for that connection string works, this is successfully retrieved from SQL server, but the challenge is retrieving the parameters for the OLE DB source connection.....

    I keep the value in the variable empty....all the parameters are retrieved from the database in which i store all these configurations a good example is the parameter_value is sqldev1 and i use an execute sql task to call this parameter to execute the package

    an example is this

    SELECT PARAMETER_VALUE AS ConfigDB

    FROM batchconfiguration.dbo.SSIS_PACKAGE_PARAMETERS

    WHERE (PARAMETER_NAME = 'ConfigDB')

    where parameter_ value is sqldev1

    can i get any help pls on this

  • September 2, 2011 at 7:42 am

    #1376434

    Not sure I'm understanding you.

    So, are you saying you've put the location the package needs to look for the database config in the database and asking how you can retrieve the location you need to get the config parameters from...from that database???

    That's like putting the key for a locked box inside the locked box and asking how you retrieve it...you need to put the key outside the box (e.g. XML config files or similar)

    Or are you just saying you can retrieve a config parameter into a variable, but don't know how to use that variable to set a OLEDB connection string? If it's this, you need to use an expression on the connection if you're doing custom configurations.

    There's an example here:

    http://www.sqlserver007.com/2009/06/20/adding-expressions-in-ssis/

    If you're not using the in-built config mechanism, you'll also need to set delay validation to true for the connection

Viewing 9 posts - 1 through 8 (of 8 total)

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