SQLSaturday Tampa #371

Lately I have been busy working with the ssis script task component, however, today I'm starting to work on my presentation from scratch again. So, lets the fun begin, one more week before traveling to Tampa.

As always, here is some code to remove special characters in Fly.

/* Microsoft SQL Server Integration Services Script Component

*  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)

    {

       

    }

    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);

    }

}