April 8, 2002 at 6:27 pm
I have an import text file in my DTS package and the problem is that between each record that I am importing there are two empty records, kinda like this:
2002~M~ABcde~123Mr~35Fed
empty space
empty space
2002~M~ABcde~123Mr~35Fed
Where the empty space is in the text file it accually stores two empty records with all null values.
col1 col2 col3 col4 col5
2002 M ABcde 123Mr 35Fed
null null null null null
null null null null null
2002 F Arte8 qwert ytRed
My first thought was to create a SQL script after the table was populated into the DB and simply Delete Where(is null) values using the unique ID column. Is there another way to delete these extra entries in active xscript prior to being ran in dts.
April 8, 2002 at 7:08 pm
You mean like this? (you'll have to convert to vbScript)
Dim fso As New Scripting.FileSystemObject
Dim f As File
Dim ts, ts2 As TextStream
Dim s As String
Set f = fso.GetFile("c:\test.txt")
Set ts = f.OpenAsTextStream(ForReading)
Set ts2 = fso.CreateTextFile("c:\testout.txt", True)
Do Until ts.AtEndOfStream
s = ts.ReadLine
If Len(s) > 0 Then
ts2.WriteLine s
End If
Loop
ts.Close
ts2.Close
s = ""
Set ts = Nothing
Set ts2 = Nothing
Set f = Nothing
Set fout = Nothing
Set fso = Nothing
April 9, 2002 at 7:03 am
Hate to be a pain, but would I convert it to VB.
April 10, 2002 at 3:07 am
Try using the standard ActiveX Copy feature within the Transform task.
Basically, Add a Text Source, Add a Database Table Targe, Connect using a Transformation, Identify the Source, Identify the Target then within the Copy use an ActiveX Copy :-
'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************
' Copy each source column to the destination column
Function Main()
If IsNull(DTSSource("col1")) Then
Main = DTSTransformStat_SkipRow
Else
DTSDestination("col1") = DTSSource("col1")
Main = DTSTransformStat_OK
End If
End Function
You may have to play around with the test for Null, but it can be used to automatically skip the row of erroneous data.
Hope this helps............
Simon Sutcliffe............
"To ask is human, to assume is dangerous"
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply