I am importing a text file delimited in fixed width format. (Sql Server 2000)
Even though I have set the width of all the fields correctly in the dts
wizard when I run the import, for some records I seem to get the start of
the next record appended onto the end of the record and the next record is
skipped entirely. I think the problem is because the last field in the text
file is text of variable length and if the length is less than the maximum
the DTS seems to take part of the next line to make up the space. Any
solutions?
--
Scott

You can try padding the columns

Have a look at this

http://www.databasejournal.com/featu...le.php/1462341

Allan

"scottrm" <scottrm (AT) newsgroup (DOT) nospam> wrote

Thanks for the reply but it does seem a bit of a messy workaround. Although
if the limitations of DTS mean it has to be done...

Any other ideas would be appreciated.
--
Scott


"Allan Mitchell" wrote:

Hi Scott,

Since this a by design behavior as BOL states that when you import fixed
width files via DTS, the rows must have the same number of characters.



You may consider the workarounds:



1. Use Jet/MS Access to import: The Jet database engine has special code
which will handle the situation mentioned above and import the file
correctly. Then you can use DTS against the Access db to import to SQL
Server.



2. Add an Active X Script to the package which reads the original text
file, and spits it back out as a DELIMITED text file, then use then use DTS
to point to the new text file. This ActiveX task should be the first task
in the package


The following sample script imports a file called test.txt, reads through
it's four fields per record, and then spits it out to a delimited text file
named testfile.txt. You'll need to have a schema.ini file setup for the
code to be able to read the orginal test.txt file correctly, and the
schema.ini file must be in the same folder as the file we're importing. For
more details on this file see the orginal case, or use the following link:



http://msdn.microsoft.com/library/bo...c8_body_34.htm



Here's what the schema.ini for text.txt would look like



[test.txt]

ColNameHeader=False

format=FixedLength

Col1=MyFlag integer Width 1

Col2=Field1 Text Width 5

Col3=Field2 Text Width 5

Col4=Field3 Text Width 4





Function Main()



Dim strBuild

Dim fso, tf

Set fso = CreateObject("Scripting.FileSystemObject")

Set tf = fso.CreateTextFile("c:\testfile.txt", True)

Set conn = CreateObject("ADODB.Connection")

Set rs = CreateObject("ADODB.Recordset")



conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\;Extended
Properties=Text;"

rs.Open "Select * from test.txt", conn



rs.MoveFirst

Do While Not rs.EOF

strBuild = Trim(rs(0)) & "," & Trim(rs(1)) & "," & Trim(rs(2)) & ","
&
Trim(rs(3))

tf.WriteLine (strBuild)

rs.MoveNext

Loop



tf.Close

Main = DTSTaskExecResult_Success

End Function


Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!


Sincerely yours,

Michael Cheng
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.