Code Snippet
using System;
using System.Collections.Generic;
using System.Text;
using System.Runtime.InteropServices;
// Microsoft.SqlServer.DTSPipelineWrap.dll
using Microsoft.SqlServer.Dts.Pipeline;
// Microsoft.SqlServer.PipelineHost.dll
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
// Microsoft.SQLServer.DTSRuntimeWrap.dll
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
// Microsoft.SQLServer.ManagedDTS.dll
using Microsoft.SqlServer.Dts.Runtime;
namespace TxNullifyNonDateStrings
{
[
DtsPipelineComponent
(
DisplayName = "Nullify NonDate Tranform" ,
ComponentType = ComponentType.Transform ,
Description = "Nullifies non-date strings"
)
]
public class TxNullifyNonDateStrings : PipelineComponent
{
#region Component Specific Methods / data
private bool IsValidDateString( string possibleDate ,
DateTime minDateAllowed ,
DateTime maxDateAllowed )
{
DateTime parsedDate;
if ( String.IsNullOrEmpty( possibleDate ) )
{
return false;
}
if ( !DateTime.TryParse( possibleDate , out parsedDate ) )
{
return false;
}
// have a legit date, check that the date is in allowed range
if ( parsedDate.Subtract( minDateAllowed ).Days < 0 ||
parsedDate.Subtract( maxDateAllowed ).Days > 0 )
{
return false;
}
return true;
}
#endregion
#region Run-Time
public override void ProcessInput( Int32 inputID , PipelineBuffer buffer )
{
bool isValid = false;
String candidate = String.Empty;
int columnCount = columnInfos.Length;
if ( buffer.EndOfRowset ) { return; }
while ( buffer.NextRow() )
{
bool isError = false;
for ( Int32 i = 0 ; i < columnCount ; i++ )
{
ColumnInfo colInfo = columnInfos[i];
try
{
if ( buffer.IsNull( colInfo.bufferColumnIndex ) ) { continue; }
if ( colInfo.dataType == DataType.DT_NTEXT ||
colInfo.dataType == DataType.DT_TEXT )
{
Int32 blobLength = ( Int32 )
buffer.GetBlobLength( colInfo.bufferColumnIndex );
Byte[] columnData =
buffer.GetBlobData(
colInfo.bufferColumnIndex , 0 , blobLength
);
if ( colInfo.dataType == DataType.DT_NTEXT )
{
candidate = Encoding.Unicode.GetString( columnData );
}
else
{
candidate = Encoding.GetEncoding(
colInfo.codePage ).GetString( columnData );
}
}
else
{
candidate = buffer.GetString( colInfo.bufferColumnIndex );
}
isValid = IsValidDateString( candidate , DateTime.MinValue , DateTime.MaxValue );
if ( !isValid )
{
buffer.SetNull( colInfo.bufferColumnIndex );
}
}
catch ( Exception ex )
{
switch ( colInfo.columnDisposition )
{
case DTSRowDisposition.RD_FailComponent:
throw ex;
case DTSRowDisposition.RD_NotUsed:
throw ex;
case DTSRowDisposition.RD_IgnoreFailure:
continue;
case DTSRowDisposition.RD_RedirectRow:
isError = true;
buffer.DirectErrorRow( errorOutputID , 1 ,
colInfo.lineageID );
goto DONE_WITH_ROW;
}
}
} // for ( Int32 i = 0 ; i < columnCount ; i++ )
DONE_WITH_ROW:
if ( !isError )
{
buffer.DirectRow( normalOutputID );
}
} // while ( buffer.NextRow() )
}