SSIS Configuration File Problem

Question

SSIS Configuration File Problem

pespes009

Mr or Mrs. 500

Points: 574
More actions
September 1, 2011 at 7:42 am

#244100

What is the best way to solve SSIS configuration file problem
Many Thanks

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

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

Dave23 Hall of Fame Points: 3903 More actions · Answer 1

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.

pespes009 Mr or Mrs. 500 Points: 574 More actions · Answer 2

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

Dave23 Hall of Fame Points: 3903 More actions · Answer 3

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.

HowardW One Orange Chip Points: 29109 More actions · Answer 4

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.

pespes009 Mr or Mrs. 500 Points: 574 More actions · Answer 5

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

HowardW One Orange Chip Points: 29109 More actions · Answer 6

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

pespes009 Mr or Mrs. 500 Points: 574 More actions · Answer 7

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

HowardW One Orange Chip Points: 29109 More actions · Answer 8

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