How to transfer a text source file into multiple destination tables?

[mariayx]

Hi DTS gurus,
I have a text source file and the data in it is supposed to go to different SQL Server tables. Is there a way of doing it?
Thanks in advance for any help.

 

[JayKusch]

Sure ... There is ALWAYS a way - LOL. First of all, are all the table schemas the same ... they probably are not? I am assuming that the text file has various columns that need to be parsed into the various tables that you mentioned. Is this SO?

What we can do is use an xp_cmdshell 'type c:\YourFileName.txt' and load either a temp table or a table variable if you are using SS2K. Once we have this, we can parse that table/variable and load the tables accordingly.

What flavor of SQL Server are you running AND does the above statments reflect what you are trying to accomplish.



Thanks

J. Kusch

 

[mariayx]

Thanks for the response! I appreciate it.
The first three positions of the text file always hold data type followed by data of that type. The total length of each line is 100 with carriage return. Each column has fixed length also.
The file looks like this:
V 1019060 PNP
B 383430473 E
SC1140PTOHCO 2DTT707904 CCONTINENTAL AIRLINES
SC270 X20030822010332Y
N01383430473 EXXXXXXXXX PACKARD ELECTRONIC SYSTRUSS
N02POST OFFICE BOX 431 WARREN, OH. 44486
N03WARREN
N01CXYZ AUTOMOTIVE (SOC. UNIPES)
N02LARGO 1 DE DEZEMBRO 6300 GUARDA RTUGAL
N03GUARDA
V 1023930 PNP
B 383430473 E
.
.
.

V type data goes to table V, B goes to tableB, SC1 and SC2 goes to table SC, and N01, N02, N03 go to tableN.

We have SQL Server 2000. The input file is from an FTP server. Destination tables have different structures. We are planning to use DTS package with Active X script to parse the data. Since I am new to DTS, I am not quite sure whether it is going to work. Hope you would shed more light on this.

 

[JayKusch]

First off ... I am sure you typed the example of the data file in the thread above. The reason I say this is, if the first three positions of the text file always hold data type ... then the records w/ a V or B are off. In your first example the record type would be "V 1" and the second would be "B 3".

Aside from this, I would import the text file into a table. During the DTS import operation, you can mark where the fields should be broken based on their length.

This should get you started in the right direction.

Thanks

J. Kusch

 

[mariayx]

Good to know that I am in the right direction. But one more thing I am not sure how to handle is that if you look at the data format, one V or B type record could have more than more one N01, N02, N03 records. I wonder whether you have handled something similar with this before and could give me some help.
Thanks.
mariayx