Use DTS to load text file

Use DTS to load text file

Post by Jennife » Sun, 21 Jan 2001 01:19:06

I am an Oracle DBA, who has been tasked with converting a portion of my
Oracle database into a separate SQL Server database. The data in the
tables are received weekly in the form of a text file from 6 different
sources. The data is loaded into various tables. We have a ProC program
written that takes care of that part.

So, I am trying to use SQl Server's DTS to load the text file. It does
not allow me to pick more than one table to load the text file into. I
need to load the data into 2 tables and also load an incrementing row
as part of the primary key.

I did stumble across the default Visual Basic Transformation Script. I
started playing with it, but I'm not sure it is going to work. And, I
dont' knwo how to finish it so I can increment. I'll include what I
have all ready written. If someone would be so kind as to take a look
and see if what I'm doing is allowed & give me a push in the right
direction for the incrementing part, I would really appreciate it. The
blank line after the two Ownerno variables is where I need to increment
each time a row is added.

Function Main()
        DTSDestination("MMSI_Vess.Vess_Clsn") = DTSSource("Col001")
        DTSDestination("MMSI_Vess.Vess_Name") = DTSSource("Col002")
        DTSDestination("MMSI_Vess.Mmsi_Num") = DTSSource("Col003")
        DTSDestination("MMSI_Vess.Reg_Num") = DTSSource("Col005")
        DTSDestination("MMSI_Vess.Gen_Type_Num") = DTSSource("Col006")
        DTSDestination("MMSI_Vess.Spec_Type_Num") = DTSSource("Col007")
        DTSDestination("MMSI_Vess.Ton") = DTSSource("Col009")
        DTSDestination("MMSI_Vess.Length") = DTSSource("Col008")
        DTSDestination("MMSI_Vess.Src_Num") = 4
        DTSDestination("MMSI_Vess.Ownerno") =
        DTSDestination("MMSI_Owner.Ownerno") =
        DTSDestination("MMSI_Owner.Src_Num") = 4
        DTSDestination("MMSI_Owner.Owner") = DTSSource{"Col010")
        DTSDestination("MMSI_Owner.Owner_Addr") = DTSSource("Col011")
        Main = DTSTransformStat_OK
End Function

Thank you in advance for any help!

Sent via


1. Loading Text File using DTS

I am trying to load a text file into fields in a table which are 100 chars
wide but I get this error. "AllowStringTruncation is possible but not
allowed for column pair x'

It seems to be a property of the transformation object in DTS. How can I
specify the transformation object to the size of the field I am importing


colin Larcombe

2. US-SC Data Modeler

3. Failed to Append a Text file into another Text file by Using DTS in SQL2000

4. transact sql compiler

5. Creating a text file using automation without using DTS

6. VFP 5.0a. Citrix, and combo boxes

7. Loading a Text File Through DTS via ASP Page

8. D3 , Sockets & HTML

9. Problems with load text file in DTS

10. dts error while loading empty fields from a text file

11. DTS strange Error loading from text file

12. Loading text files, DTS/Stored proc/TSQL/VB??

13. Loading Text Files with DTS