November 15, 2007 at 7:57 am
Hello,
We have an existing OLD System in SQL Server 2000 DTS Packages.
The Whole application runs on DTS.
There are several Packages which are called from a Master Package. Each Child packages have their own Global Variables. Most of them are the File Location variables to have the Source Location of the Input Data, mainly from the Excel Files.
Now, even if the Global Variables are there to change whenever they want to change the Locations of the Files, they have to goto each child package and change the variable themselves.
To resolve this issue, they want a configuration File (INI) / Table which would store those Variables. My thought is to read from that File/Table and Update all the packages' global variables through an ActiveX Script as the First Step of the Master package. That would eliminate the need of changing anything in the existing System.
But the Problem is the management (PM / DBAs / Team members) have different views to store the Configuration data.
1. Some wants it into a Different Database, having one table for this application so in future they can also add another table for some other application.
2. Some wants to store it in a Table in the Same Database of this Application.
3. Some wants to save it as a INI file.
As i'm the one who's going to really implement it, they have asked me to research for a best solution out there.
so I request to help me to decide which is a good solution and why.
Best Regards,
Deepak
November 20, 2007 at 2:27 pm
Hello again Deepak,
Following up you previous post...
We use the database, since the DBAs have the correct access, they can lock it down and its gets backed up. System admins do not need to be involved to lock down the directory where ini files exist.
We create a database called PackageProperties with a table called PackageProperties.
Here is the structure:
CREATE TABLE [dbo].[PackageProperties] (
[PackageName] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ObjectName] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PropertyName] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PropertyValue] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PackageProperties] ADD
CONSTRAINT [PK_PackageProperties] PRIMARY KEY CLUSTERED
(
[PackageName],
[ObjectName],
[PropertyName]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
PackageName will hold the name of the package or some portion of a name that a group of packages could use.
ObjectName will hold the human name of a an object i.e. Truncate Table Load not Execute_SQL_TASK_1.
PropertyName will hold the dts name of the property being set i.q SQLStatement for and the Execute SQL Task Truncate Table Load
PropertyValue will hold the value to load into the package. i.e. TRUNCATE TABLE dbo.Load
To populate PackageProperties
INSERT INTO PackageProperties VALUES('A package','Truncate Table Load', 'SQLStatement','TRUNCATE TABLE dbo.Load')
The SQLStatement property is set via a dynamic properties task that runs the following query against a connection that points to the PackageProperties database.
SELECT PropertyValue
FROM dbo.PackageProperties
WHERE PackageName = 'a package'
AND ObjectName = 'Truncate Table Load'
AND PropertyName = 'SQLStatement'
This can set a global variable as well as any other property in the DTS package.
See my other post to you about how to use the dynamic properties task.
Regards,
Norman
DTS Package Search
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply