Package Configurations

[MichaelRo1]

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 :-(

 

[WhoWantsTacos]

When you create the package and enable the configurations one of the options in the dropdown is the SQL Table.

 

[fredericofonseca]

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

<?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

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.

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

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886