invalid entries in text file 1

[sedgely]

i have a DTS job set up to import the contents of a text file into a SQL table. the problem i have is that some of the fields are time fields and the application that creates the text file puts in a colon ) where the field is blank. i am looking at the possiblity of changing the application but at the moment, that is a no goer.
is there a way to build something into the DTS job to say &quot;if this field contains &quot;:&quot; then make the field <null>&quot;
i am new to DTS, and could do with some good advice.
Thanks in advance

Cheers, Craig
Si fractum non sit, noli id reficere

 

[JohnDTampaBay]

You will need to use an ActiveX transformation. I assume that you have a Transform Data task that pumps data from the text file into the database. Right-click on the transform line between the source and destination connections. Go to the Transformations tab and locate the mapping for the columns in question.

Click on the mapping line and delete it, then drag and drop the source column name onto the destination column name. Select ActiveX Script from the Create New Transformation dialog box and click OK. Choose Properties... Modify the script below to fit your needs. I am not a VB scripting expert, but this should be close. Good luck!

'**********************************************************************
'  Visual Basic Transformation Script
'************************************************************************

'  Copy each source column to the destination column
Function Main()
	IF DTSSource("ColumnName") = ":" THEN DTSDestination("ColumnName") = Null  ELSE DTSDestination("ColumnName") = DTSSource("ColumnName")

	Main = DTSTransformStat_OK
End Function

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[sedgely]

Thanks Angel
Will try this and let you know


Cheers, Craig
Si fractum non sit, noli id reficere

 

[sedgely]

Angel
Have now tried your solution and with a bit of &quot;tinkering&quot; have got what i needed. Many thanks Have a star

Cheers, Craig
Si fractum non sit, noli id reficere