* Write scripts using Microsoft Visual C# 2008.
* ScriptMain is the entry point class of the script.*/
using System;
using System.Data;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Dts.Pipeline;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
public override void PreExecute()
{
base.PreExecute();
/*
Add your code here for preprocessing or remove if not needed
*/
}
public override void PostExecute()
{
base.PostExecute();
/*
Add your code here for postprocessing or remove if not needed
You can set read/write variables here, for example:
Variables.MyIntVar = 100
*/
}
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
//Row.AltImages = RemoveCharacter(Row.AltImages);
//Row.Brand = RemoveCharacter(Row.Brand);
//Row.Bullets = RemoveCharacter(Row.Bullets);
//Row.CategorizationDetails= RemoveCharacter(Row.CategorizationDetails);
//Row.Color = RemoveCharacter(Row.Color);
//Row.Gender = RemoveCharacter(Row.Gender);
//Row.League = RemoveCharacter(Row.League);
//Row.PlayerName = RemoveCharacter(Row.PlayerName);
//Row.Team = RemoveCharacter(Row.Team);
//Row.ProductName = RemoveCharacter(Row.ProductName);
//Row.Vendor = RemoveCharacter(Row.Vendor);
//Row.DCS = RemoveCharacter(Row.DCS);
//Row.MCS = RemoveCharacter(Row.MCS);
//Row.GenderAgeGroup = RemoveCharacter(Row.GenderAgeGroup);
//Row.TeamTypes = RemoveCharacter(Row.TeamTypes);
//Row.Sport = RemoveCharacter(Row.Sport);
//Row.ProductURL = RemoveCharacter(Row.ProductURL);
//Row.AltImageURLs = RemoveCharacter(Row.AltImageURLs);
//Row.partnerSiteName = RemoveCharacter(Row.partnerSiteName);
//Row.KeyWords = RemoveCharacter(Row.KeyWords);
//Row.PlayerNameAndNumber = RemoveCharacter(Row.PlayerNameAndNumber);
}
public override void ProcessInput(int InputID, PipelineBuffer Buffer)
{
string pattern = "[^ -~]*";
Regex reg_exp = new Regex(pattern);
//bool fireAgain = false;
//ComponentMetaData.FireInformation(0, "",
// Buffer.ColumnCount.ToString() + " columns",
// "", 0, ref fireAgain);
int index = 0;
String DataBackup = "";
while (Buffer.NextRow())
{
try
{
for (int columnIndex = 0;
columnIndex < Buffer.ColumnCount;
columnIndex++)
{
index = columnIndex;
string columnData = null;
if (Buffer.IsNull(columnIndex))
{
columnData = "is NULL";
}
else
{
BufferColumn columnInfo = Buffer.GetColumnInfo(columnIndex);
switch (columnInfo.DataType)
{
case DataType.DT_WSTR:
case DataType.DT_STR:
columnData += Buffer.GetString(columnIndex);
DataBackup = columnData;//Save backup
columnData = reg_exp.Replace(columnData, "");
Buffer.SetString(columnIndex, columnData);
index = 0; //Reset index;
DataBackup = ""; //Reset data
break;
// add code to support more data types here
default:
columnData = "";
index = 0; //Reset index;
DataBackup = ""; //Reset data
break;
}
}
//ComponentMetaData.FireInformation(0, "",
// "Column " + columnIndex.ToString() + ": " + columnData,
// "", 0, ref fireAgain);
}
}
catch
{
Buffer.SetString(index, DataBackup);
index = 0;
DataBackup = "";
}
}
base.ProcessInput(InputID, Buffer);
}
}
Thank you SO MUCH for this script. It worked like a charm in SSIS. This is the best solution I have come across!
ReplyDelete