Hi I apoligize about any cross-post, since I posted a similar message
in the sql...odbc group too - I didn't see the dts group prior to
posting -

I've been tasked with setting up a datawarehouse using SQL that will
pull down about 70 MAS 200 files (MAS 200 is written in Providex with
an ODBC for access)into SQL and be used for reports for users that
don't/or shouldn't have access to the live MAS 200 system. The system
needs to update nightly.

I've heard of DTS but as I'm new to this, I'm not sure if it is the
best option. Is DTS easy to setup and maintain? Can it be set to run
like a task (schedule for a particualr time)? Does it have a log of
success/unsuccessful events? Does DTS run appends, imports, ?

Thanks for any thoughts and suggestions.

In message <4461204f.0402041446.63f88683 (AT) posting (DOT) google.com>, Mig
<support (AT) chismet (DOT) com> writes
First off, if you have an ODBC driver avilable then DTS should be able o
connect, and access the data.


I think DTS is easy to setup and maintain, certainly better than writing
your own ETL layer.

DTS packages can be scheduled through whatever mechanism you chose. They
can be execute through a COM object model or more usually through DTSUN,
a command line tool. The simplest way to schedule a package is
right-click it in Enterprise Manager, and select Schedule package. This
creates a SQL Server Agent job, which uses DTSRUN.

Packages can log to a text file or to some pre-defined SQL Server
tables. These options are set on the package properties in the designer.
You can also add your own "log" tasks, linked by On Success or On
Failure workflow in which you write your own logging calls, as
appropriate for the task you choose.

It can do a append type load, but it is usually easier to use Dts to
load into staging tables, and use T-SQL to perform the type 1,2,3
dimension loading into the actual data store.

To start with try the Import/Export Wizard. Then open the package and
see what it built.

Importing A Text File Using The DTS Wizard
(http://www.sqldts.com/default.aspx?276)


Or

A simple tutorial on building a package from scratch-

Building a Package in the DTS Designer
(http://www.sqldts.com/default.aspx?278)


--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Darren Green <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote

Darren - thank for the information and especially the links. I
appreciate your reply.

Darren,

I have just finished deployment of a data warehouse using the ProvideX
client-server ODBC driver pulling data from our FACTS 7.1 system running on
a SCO server. 2 GB of data pulled, cleaned and indexed using DTS in just
under 10 minutes. Given how fast this works, we are considering pulling data
several times each day instead of every night.

Total records pulled over 2 million.
Total tables over 200.
Total development time under four weeks.

Jeff



"Mig" <support (AT) chismet (DOT) com> wrote