DTS - import flat textfile into two separate tables

I have a report that's created each day as a flat textfile.

Because I came from the Access world, I created a macro that imports
it with a schema that gives meaningful names to the various columns,
and then uses a query to massage some of the data for me (deletes the
first blank row and does a couple of calculations)

Then I use DTS to import the Access query as a table.

the textfile has a column called "File_num", and among several others,
a column called "Serial_num". (the file numbers represent shipments,
and sometimes there are more than one serial number in the shipment,
etc., so there is a separate line for every serial number)

Naturally, I would like to split this info into two tables..one that
does not contain the serial numbers and has a primary key on the
"File_num" column, and another table that would contain just the
"File_num" and "Serial_num" columns. That way I could relate them
later...but most importantly, it will give me a table where I can use
the "File_num" as my primary key.

What would be the best way to import these two tables from one source
textfile? The other thing that gives me problems is that the text
file has no column names, and the first row is always blank.

I'm very new to SQL and DTS and would appreciate any direction.

Thanks,

Larry

- - - - - - - - - - - - - - - - - -
"Forget it, Jake. It's Chinatown.""Larry Rekow" <larry@.netgeexdotcom> wrote in message
news:3h1cj0976dtpkliussk5c1nr7jmubvt4rp@.4ax.com...
>I have a report that's created each day as a flat textfile.
> Because I came from the Access world, I created a macro that imports
> it with a schema that gives meaningful names to the various columns,
> and then uses a query to massage some of the data for me (deletes the
> first blank row and does a couple of calculations)
> Then I use DTS to import the Access query as a table.
> the textfile has a column called "File_num", and among several others,
> a column called "Serial_num". (the file numbers represent shipments,
> and sometimes there are more than one serial number in the shipment,
> etc., so there is a separate line for every serial number)
> Naturally, I would like to split this info into two tables..one that
> does not contain the serial numbers and has a primary key on the
> "File_num" column, and another table that would contain just the
> "File_num" and "Serial_num" columns. That way I could relate them
> later...but most importantly, it will give me a table where I can use
> the "File_num" as my primary key.
> What would be the best way to import these two tables from one source
> textfile? The other thing that gives me problems is that the text
> file has no column names, and the first row is always blank.
> I'm very new to SQL and DTS and would appreciate any direction.
> Thanks,
> Larry
> - - - - - - - - - - - - - - - - - -
> "Forget it, Jake. It's Chinatown."

A common technique is to import the source file directly into a staging
table, then transform the data using SQL. This is often easier than trying
to implement complex transformations in DTS itself. In your case, you could
do something like this:

insert into dbo.FileTable
(file_num, col1, col2, ...)
select file_num, col1, col2, ...
from dbo.StagingTable

insert into dbo.FileSerialTable
(file_num, serial_num)
select file_num, serial_num
from dbo.StagingTable

Simon

Dts

I have 3 separate DTS to transfer tables.
They transfer tables from 3 different Access databases
into one SQL server database.
But I would like to make them into one dTS so that I can
just check on error log file.
How can I do this?
Thanks for any suggestions.Basic copy-pasting of the entire contents of your DTS packages into one would do the trick because a DTS can have many separate connections and many "tasks" even if they are not related.

Now, you might want to keep them separate because if one fails (because of data transformation for example), you could still run the other two. If you keep them separate, use 'dtsrun' with the SQL Server Agent to run all your packages in different steps (and setting the "On failure" option to "Go to the next step") hence -kind of- grouping them together.

Good luck,

SC|||You can enable DTS package logging as specified by BOL:
To enable package logging

Open the Data Transformation Services (DTS) package for which you want to create a log.

On the Package menu, click Properties to display the DTS Package Properties dialog box.

Do one of the following:
Save package logs to Microsoft SQL Server by clicking the Logging tab, selecting the Log package execution to SQLServer check box, and then clicking an available server on which to save the package logs.

Security Note When possible, use Windows Authentication.

Save package logs to SQL Server 2000 Meta Data Services by clicking the Advanced tab, and then selecting the Show lineage variables as source columns and Write lineage to repository check boxes. On the Package menu, click Save As, and then in the Save DTS Package dialog box, in the Location list, select Meta Data Services.
... which will help you to assess the package(s) information.|||Thanks for all your responses.