Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Package Configurations

Status
Not open for further replies.
May 10, 2004
12
GB
Quite new to SSIS and obviously Package Configurations.

I have inherited a system where an external application alters the XML package configuration to define an XML Source File Path.
This application no longer works and I want to recreate something within SSIS.

I hoped that by moving the Config into a SQL Server table I can update the Source Path using Script or SQL and import the data.

While I can just about understand the XML Config File I have no idea how to re-create it in the table (ConfigurationFilter etc)

HELP :-(
 
When you create the package and enable the configurations one of the options in the dropdown is the SQL Table.
 
The configuration table is a quite simple one.

Any SSIS configuration is made up of a few strings.
If saving to a XML file you will normally get the following ones
ConfiguredType (contains property normally)
PATH - contains the "path" to the package object and property of the said object
ValueType - string, integer or whatever the field type is defined as
Configuredvalue - the value to assign to the property defined above.
sample below
Code:
<?xml version="1.0"?>
	<DTSConfiguration>
	<DTSConfigurationHeading>
		<DTSConfigurationFileInfo  GeneratedFromPackageName="Load Flat Files" GeneratedFromPackageID="{7A07BDFC-84EE-410F-A878-383815F8AA26}" GeneratedDate="29/02/2008 13:00:00"/>
	</DTSConfigurationHeading>
	<Configuration ConfiguredType="Property" Path="\Package.Connections[Conn_Management].Properties[ConnectionString]" ValueType="String">
		<ConfiguredValue>Data Source=WINDOWS7;Initial Catalog=database_name;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;</ConfiguredValue>
	</Configuration>
	</DTSConfiguration>

The above xml configuration is setting the property "ConnectionString" of connections "Conn_Management" to "Data Source=WINDOWS7;Initial Catalog=database_name;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;"

Most of the companies I worked with would have a configuration file defined as above containing the connection string to the database that contains further configuration stored on a database table

In my case for example if I go to the configuration table on my server I have
Code:
ConfigurationFilter	ConfiguredValue	PackagePath	ConfiguredValueType
feed_registrar_configurations	C:\Program Files\Framework 2008\Feeds	\Package.Variables[User::configuration_folder].Properties[Value]	String
archive_configurations	Data Source=WINDOWS7;Initial Catalog=dwh_Archive;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;	\Package.Connections[Conn_Archive].Properties[ConnectionString]	String
staging_configurations	Data Source=WINDOWS7;Initial Catalog=dwh_Staging;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;	\Package.Connections[Conn_Staging].Properties[ConnectionString]	String
folder_configurations	C:\Program Files\Framework 2008\Feeds\process	\Package.Variables[User::process_folder].Properties[Value]	String
folder_configurations	C:\Program Files\Framework 2008\Feeds\Process	\Package.Variables[User::csv_file_connection].Properties[Value]	String
folder_configurations	C:\Program Files\Framework 2008\Feeds\Archive	\Package.Variables[User::archive_folder].Properties[Value]	String

In this case, the field "ConfigurationFilter" is the one used on SSIS to identify the entries of the configuration table to load into the package.

The remaining 3 fields follow what I said on the xml definition above.

I hope this info is what you were looking for. if not clarify a bit more

The SQL Server config layout should contain at least the following.
Code:
CREATE TABLE [admin].[configuration](
	[ConfigurationFilter] [nvarchar](255) NOT NULL,
	[ConfiguredValue] [nvarchar](255) NULL,
	[PackagePath] [nvarchar](255) NOT NULL,
	[ConfiguredValueType] [nvarchar](20) NOT NULL
) ON [PRIMARY]
It can have more columns, but they will not be used by SSIS

Regards

Frederico Fonseca
SysSoft Integrated Ltd

FAQ219-2884
FAQ181-2886
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top