I was referring to your earlier message about loading the table from a text
file. Your DTS could create a new table every time you process a file in a
dynamic SQL script. The only thing that is necessary to know is what are the
possible columns of data, the script takes care of only loading the columns
that you have data for in a "record". I quoted record here because I believe
that you have a logical record comprised of multiple text records divided by
the token <end>. By the way why do you want to have a new table every day
you could add another column to your table that was the load date. This
would simplify later queries, no unions required. Any how I also have to
agree with Dave Cameron, you should try to get this in a different form say
comma or tab delimited records. That would be much easier to deal with.
Simplicity is better for everyone in the long run.

-----Original Message-----
From: Dave Parkins [mailto:dparkins@m...]
Sent: Wednesday, January 23, 2002 2:27 PM
To: sql language
Subject: [sql_language] RE: Adding the data from a text file to an exi
sting table


I'm not sure if you're referring to my question the problem is that the text
file will be a different size every day with a different number of fields
for each record. And what we would like to do is for it to create a new
table every day based on what's in the new text file. Again, here's just
part of what the text file looks like:

<type>
<inv date>011106
<start day>22
<end day>22
<page size>75
<key sel>100 1
<amt# 1>500
<end>
<type>
<inv date>011106
<start day>22
<end day>22
<page size>75
<page size2>115
<key sel>100 1
<end>

-----Original Message-----
From: Turner, J [mailto:JT@D...]
Sent: Wednesday, January 23, 2002 3:35 PM
To: sql language
Subject: [sql_language] RE: Adding the data from a text file to an exi
sting table


You should be able to do this in DTS. Here is a cut at what you would do,
first create global variables for each of the destination columns, these
should be of the appropriate type.
In the pre source data function set each of these to a null value. Next in
the row transform phase set up a parse code something like this
sub main
dim code, value
dim code_end

code_end = instr(2,source.col1,">")
code = mid(source.col1,2,(code_end -1))
value = mid(source.col1,(code_end + 1),999)

select case code
 case "end"
	' move the saved values to the destination columns
	destination.type = gl_type
	destination.lname = gl_name
	' do the rest of the columns here

	' clear out the global variables so that if you do not have a value
	' for one of the columns you will not replicate data from a previous
record
	gl_type = null ' you may need to just set it to ""
	gl_lname = null
	
	main = DTSTransformStat_OK
 case "type"
	gl_type = value
 	main = DTSTransformStat_SkipInsert
 case "lname"
	gl_lname = value
 	main = DTSTransformStat_SkipInsert
' do this for each of your columns
 case else
 ' you have a problem with your data you may want to abort or just skip the 
 ' record

end select

end sub

I have not tried this code but it should be close to what you need to do
what you asked. 

$subst('Email.Unsub').

$subst('Email.Unsub').