Request guidance for receiving data to SQL

Request guidance for receiving data to SQL

Post by JJ » Thu, 21 Nov 2002 15:00:44

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

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?


1. DTS design guidance requested

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


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.


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

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 ***
Don't just participate in USENET...get rewarded for it!

2. Graphical Development Environments?

3. Request guidance

4. Win2K, Access 2000 and MSDE problem

5. Guidance request = Tree

6. Group by bug or not?

7. Newbie Guidance Kindly Requested (simple database connection)

8. Complete newbie -- requests guidance

9. Sybase : Client Received With Active Request !!!!???

10. how to find how, how much data is send and received by sql-server

11. Receiving XML data from SQL

12. looping through received data in sql