DTS design guidance requested

DTS design guidance requested

Post by Sean Raffus » Wed, 14 Nov 2001 10:08:38



Hello.  I have a large number of pairs of text files named in the following way:

2001FRMPM10.dat
2001FRMPM10.dad

where the .dat file contains colmnated data and the .dad file describes the data in the dat file.  A typical .dad file might look like this:

Loc_code   5   CHAR    ""
Date       8   CHAR    ""
Param1    12   NUM     -9
Param2    10   NUM     -9
.
.
ParamN    10   NUM     -9

where the first column is the field name, the second is the column width, third is data type, and last is the null value.

What I would like to do is create a DTS package that will read a dad/dat combo and put the data into a flat SQL table that "fits" the data.

eg.

create table 2001FRMPM10
Loc_code char(5) NOT NULL,
Date, datetime NOT NULL,
param1 float,
param2 float,
.
.
paramN float
GO

Now my question: What is the best way (or at least a good way) to get the values from the dad file into variables that I can then use to create the SQL table?  I have done some reading and it appears I could take a number of routes, but I am very new to DTS (and SQL overall) and I thought it wise to ask the pros.

Thanks in advance,

Sean Raffuse

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

1. Request guidance for receiving data to SQL

Here is a quick rundown on how things currently work:

Data is recevied from various places in the world, using various databases.
(Outof my control, I have to live with it.) However, all data is receive in
a very specific XML format, and is uploaded to an FTP server.

The received data structure is checked against a DTD for sanity.

If the sanity check passes then SQL picks up the data.

The problem that we are having is the fact that XML/DTD is too rigid. I need
to build in a little wiggle room for some of the fields. Example:

Field name: MyChoice - can be foo or bar only. (This is the current format.)

I need MyChoice to take whatever the endusers are dumping into it. Right
now, everytime someone needs a new choice we have to add it to the DTD.
Which is a PITA. The other problem with the DTD is best explained by this
example:

Record 1  foo Nancy green
Record 2 bar Nancy blue
Record 3 foo Bill green
Record 4 bar Nancy Red

The DTD validation will reject this record set since one of the Nancy's came
after a Bill. In other words, the Nancy's all need to be together. I realize
that the DTD could more than likely be massaged to take care of a lot of
this, but the ultimate question is: Is there a better way to validate the
data structure, and import into SQL other than XML?

2. Renaming Columns 8i

3. Request guidance

4. sql server install question

5. Guidance request = Tree

6. termcap for AIX

7. Newbie Guidance Kindly Requested (simple database connection)

8. Basic newbie question on database sizes etc

9. Complete newbie -- requests guidance

10. VB Newbie - could use some design guidance for inventory project

11. Suggestions requested for DB design issue...

12. Request for Microsoft docs on Design

13. Request for sample test on Implementing Database Design using Sql 7.0