I have a DTS package that basically does the following:

1) Truncates a table called MYDB.tblParameters

2) Reads a CSV file called tblParameters.csv under the C:\DEV\MyApp
directory

3) Writes CSV information to SQL Server 2000 to database MYDB.tblParameters
using a specific username/password.

The problem I have is that all the connection and file/path information is
hardcoded in the DTS. I added a dynamic property to the DTS Designer and
thought I'd use an INI file to plug in the server name, username, password,
etc. for the connection.

After awhile, I realized that I have to change more than just the connection
properties. I have to change the database name in the task, in addition to
the input path to the csv file.

I'm creating a custom installer that will be used by three customers. All
three customers have their own databasename (unique) and the installation
path is different, depending on the client. I.E., it may be C:\Program
Files\My Company\Customer1 MyAppName for customer #1 and C:\Program Files\My
Company\Customer 2 MyAppName. In addition, the custom INI file that I have
will be in the client's installation directory. So DTS can't have this
hardcoded either!!!

When I create the DTS, my CSV file exist on my PC in C:\DEV\MyApp folder.
The input directory will be different per customer.

What is the best way for me to design the DTS package using a dynami
property sheet. I thought this would be easy. Right now I'm thinking
about just installing all thre apps on my PC in their correct directory,
building the three databases, and simply re-creating three separate DTS
tasks so they will have the right information. The only downside to this
approach is, I will install the apps on C:\Program Files\... And the
end-user could choose to install to D:\Program Files. If that is the case,
the DTS package won't find the CSV file to load because it will be looking
on C:\Program Files\...

Any ideas or opinions?

DTS seems nice and powerful, but it seems to shoot itself in the foot with
making it easy to deploy.

On solution would be to front-end the package execution with a windows
scripting host or simple VB application that would allow selection of paths
for installation (or registry lookups to find the installation path). The
ini file could be stored in the same directory as the script/executable.
Application execution path is fairly trivial to pull from the current
execution to load the ini file. Load the package using the DTS api calls,
set the global variables ( or dynamic properties collection values) and
call the execution method. Since the Script/App will perform the execution
of the package, you can even handle package execution errors of the package.

-Mike Gercevich

P.S. Yes, DTS can be a very powerful tool... It does exactly what you tell
it to do. Most problems do have a simple solution, but it is up to you to
determine the effort you are willing to put into it when you find there is
no easy-out-of-the-box solution. BTW... DTS has a Task extendable model to
extend to your hearts content; But try not to reinvent the wheel when
there is a better way to perform the task at hand. Sometimes the best
solution is not to use DTS at all. )

--
Message posted via http://www.sqlmonster.com