DTS with "dynamic" source schema

DTS with "dynamic" source schema

Post by Joe T » Sun, 17 Mar 2002 05:40:40



Any ideas on how I could handle a DTS import situation
where my source table (actually Unix/Informix) will
actually vary a bit from import to import (extra field or
2 or maybe even minus a field or 2).  The only thing I can
bank on is the source table name.

In other words, is it possible to somehow bypass
the "destination" tab in the Designer and have DTS create
the destination table on the fly?

Here's my intention:

-->load Unix/Informix table "as is"  (staging table)
-->using syscolumns, compare table structure with prior
load
-->if same as prior run, append to production table
-->if not, then error handle, email notify, etc.

Thanks in advance for any suggestions,

Joe T.

 
 
 

DTS with "dynamic" source schema

Post by Dick Campbel » Sun, 17 Mar 2002 08:12:24


You can use "Disconnected Edit" to explore the Connections, Steps etc in the
Package and examine their properties. As you do this you will find such
things as the source/destination connection details and even the Transform
Script. It is possible to set all of these from a VB Script Task that runs
first in the Package perhaps using Global Variables as inputs.

Whether this is more or less work than simply designing a new Package for
each transfer depends on the application.

Dick Campbell


Quote:> Any ideas on how I could handle a DTS import situation
> where my source table (actually Unix/Informix) will
> actually vary a bit from import to import (extra field or
> 2 or maybe even minus a field or 2).  The only thing I can
> bank on is the source table name.

> In other words, is it possible to somehow bypass
> the "destination" tab in the Designer and have DTS create
> the destination table on the fly?

> Here's my intention:

> -->load Unix/Informix table "as is"  (staging table)
> -->using syscolumns, compare table structure with prior
> load
> -->if same as prior run, append to production table
> -->if not, then error handle, email notify, etc.

> Thanks in advance for any suggestions,

> Joe T.