October 25, 2006 at 10:46 am
Hello,
I am a new programmer and need to do the following....
I have a text file with the following data:
aaa,bbb,ccc
ddd,eee,fff
ggg,hhh,iii
,,,
,,,
,,,
I need to delete the last three records (which have null values). I can only do this in DTS (not in a perl script or anything)....any ideas? Please respond soon (am in crunch mode). Thanks.
October 26, 2006 at 3:58 am
Hi,
Lines this Null in column 001 will not be imported.
Try this transformation script :
' Visual Basic Transformation Script
'************************************************************************
Function Main()
If IsNull(DTSSource("Col001")) = True then
Main = DTSTransformStat_SkipRow
else
DTSDestination("Col001") = DTSSource("Col001")
DTSDestination("Col002") = DTSSource("Col002")
DTSDestination("Col003") = DTSSource("Col003")
Main = DTSTransformStat_OK
end if
End Function
Gosta Munktell
October 31, 2006 at 3:57 am
What you have to remember is that dts treats csv files different from excel files.
If you have a numeric destination column and the entry is null in excel, it will bring in a zero or a blank. However, if it is null in a csv file it will fail. The trick is to add 0 to the column in the transformation task.
DTSDestination("Col001") = DTSSource("Col001") + 0
However, in your scenario above it does not apply and Gosta's solution will owrk for you. To make sure you skip when all three source columns are blank use:
If IsNull(DTSSource("Col001")) = True
and IsNull(DTSSource("Col002")) = True
and IsNull(DTSSource("Col003")) = True
Main = DTSTransformStat_SkipRow
else
DTSDestination("Col001") = DTSSource("Col001")
DTSDestination("Col002") = DTSSource("Col002")
DTSDestination("Col003") = DTSSource("Col003")
However, this does not leave a trail of the actual processing and you may have problems if the package fails. I would recommend importing everything and then running a sql statement at the end to delete the rows not required. This provide full traceability and will be easier to troubleshoot.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply