[dba-SQLServer] DTS Guru's

Hello Susan and other DTS-er's out there.

I have been using DTS for the last six or seven years now, but today
for the very first time, I wrote some VB script within in one.

Cool, I was able to use the powerful TRIM function instead of SQL's
rather less powerful ltrim and rtrim functions.

However, I have one problem yo guys may know how to handle.  I have an
if statement with my VB script that filters certain fields and does
not copy the column if we fail the criteria check,  but the DTS
package still inserts a record, so I have to allow nulls and finally
delete the records where the column values are Null.

What I had hoped the If would do was skip the insertion completely if
we fail to meet its condition.

Can this be done?  Would be create a performance hit because if really
has to do a record by record check?

Thanks in advance for any suggestions

Mark Breen
Ireland

PS . here is the DTS package

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

'  Copy each source column to the destination column
Function Main()

	If DTSSource("Col008") = "PPID" Then

		strPartRev =  trim(DTSSource("Col003"))
		strRev = right( strPartRev ,3)

		' First we get the location of the Hyphen
		intHypyen  = (InStr(  strPartRev, "-" )) 

		' We move back one character from the actual hyphen, because we do
not want it
		intHypyen =intHypyen -1


		strPart = left(strPartRev, intHypyen)

		' we do not know how many characters, so we pad it with the whole six zeros	
		strPart =  "000000" &  strPart 

		' And now we remove any characters more than six

		DTSDestination("DI_Part") =  right(strPart , 6 )		
		
		' And here are the other columns
		DTSDestination("DI_Rev") = strRev
		DTSDestination("DI_JobNum") =  trim(DTSSource("Col006"))
		DTSDestination("DI_JobQty") =  trim(DTSSource("Col007"))
		DTSDestination("DI_DueDate") =  trim(DTSSource("Col009"))
		DTSDestination("DI_Material") =  trim(DTSSource("Col011"))


	End If

	Main = DTSTransformStat_OK
	


End Function