The end of the year is approaching so, before going any further, Happy 2016 to everyone. 
Have fun, don't do anything I won't do :-).

Now that is out of the way, I have been spending a lot time thinking on how fast technology is changing, now everything is cloud this, cloud that, but, the word cloud is just an overrated media frenzy for nothing more than San's or network computing.
Finally, I'm getting to good using PostgresSQL, but as things moving you get what you paid for. But, nothing beat free, but as stated again, you get what you paid for.
Cheers.

How to access PostgreSql from MS Sql server store procs.

Don't you have a bad idea or comment become weird and at the same time awesome?
Well today after a long meeting about why would be a good idea to used our postgres database directly using reporting services the reporting team kind of refused to go into the change, so, I came with a silly comment "why not just create several CRL's in ms sql server using the NPGSQL drivers and let the reporting team used those instead." the room got silence and now that's became my new POC project. So here we go a silly comment now became the solution. Next time I just keep my silly comments to myself.
Cheers,

Send Sql server agent job notifications Via Sql Script

Today I have to add some notifications about the status of specific sql server agent jobs, specially if the job fail and been a lazy programmer I create some metadata tables with specific messages and jobs, but I still want to share the core code used to send this message. Because this is set on a ssis sql task we only care to see a message if the job fails so, top 50 should be enough history to know something is wrong :-) .

Cheers.

declare @sqlcmd varchar(2000) = 'set transaction isolation level read uncommitted;

set nocount on; declare @today varchar(8) = convert (varchar(8),getdate(),112)

SELECT  top 50  j.name JobName ,

        h.step_name StepName ,

        CONVERT(CHAR(10), CAST(STR(h.run_date, 8, 0) AS DATETIME), 111) RunDate ,

        STUFF(STUFF(RIGHT(''000000'' + CAST (h.run_time AS VARCHAR(6)), 6), 5, 0,

                    '':''), 3, 0, '':'') RunTime ,

        h.run_duration StepDuration ,

        CASE h.run_status

          WHEN 0 THEN ''failed''

          WHEN 1 THEN ''Succeded''

          WHEN 2 THEN ''Retry''

          WHEN 3 THEN ''Cancelled''

          WHEN 4 THEN ''In Progress''

        END AS ExecutionStatus ,

        h.message MessageGenerated

FROM    sysjobhistory h

        INNER JOIN sysjobs j ON j.job_id = h.job_id

where j.name = ''Job_name Here''

and h.run_status in(1,0,3) and h.run_date >= @today

ORDER BY h.instance_id desc',

@today varchar(50) = 'NameOfAttachFileHere';

set @today = @today+'_'+ replace (replace(replace(convert(varchar(25),

getdate(),

120),

'-',

''),

':',

''),

' ',

'') +'.csv';

EXEC msdb.dbo.sp_send_dbmail

    @recipients = 'EmailRecipientsHere',

               @body= 'EmailBodyHere',

               @execute_query_database = 'msdb',

    @query = @sqlcmd,

    @subject = 'Subject Alert Here',

               @attach_query_result_as_file = 1,

               @query_result_separator = ',

',

               @query_result_no_padding = 1,

               @query_attachment_filename = @today;

Building dynamic queries to extract regular data or Json datatype from postgreSQL

As always a lazy programmer, today my task is to get the update from a set of tables on postgres while excluding an specific pattern plus the scripts is run twice per day so I need to capture changes in a 12 hour interval.
MMMMM>..... How i can do this and get it done quickly :-)
.........agony, agony, agony.. I don't want to spend all day doing this....So, here is the solution to build the scripts quick and dirty:
select 
  'Do $$ declare today timestamp = current_timestamp - interval''12 hours''; begin Create Temp table temp_'||table_name||' on commit drop as SELECT '||string_agg(column_name,',')||' FROM '||table_schema||'.'||table_name||' where modified_date >= today; end $$; SELECT '||string_agg(column_name,',')||' FROM temp_'||table_name||';'
from 
 information_schema.columns 
where 
table_schema = 'tableSchemaHere'
and table_name !~* 'StringPartternHere'
and is_updatable = 'YES'

group by table_schema,table_name;

Ah... More agony now I need to extract data from the audit tables that uses the json datatype...
mmm.... what to do... just change the above script just a little to get data from json
select Table_Name,
'Do $$ declare today timestamp = current_timestamp - interval''12 hours'';begin  Create Temp table temp_'||table_name||' on commit drop as SELECT action,'||cast (string_agg('new_object_data->'||replace (initcap(replace(''''||column_name||'''','_',' ')),' ','')||
' AS '||replace(initcap(replace(column_name,'_',' ')),' ',''),',')as varchar(2000))||
' FROM audit.audit_history Where (action = '||'''Update'') AND object_type = '''||replace(initcap(replace(Table_Name,'_',' ')),' ','')||''' 
AND (Created_date <= today) UNION ALL SELECT action,'||cast (string_agg('original_object_data->'||replace (initcap(replace(''''||column_name||'''','_',' ')),' ','')||
' AS '||replace(initcap(replace(column_name,'_',' ')),' ',''),',')as varchar(2000))||
' FROM audit.audit_history Where (action = '||'''Delete'') AND object_type = '''||replace(initcap(replace(Table_Name,'_',' ')),' ','')||''' AND (Created_date <= today);END $$; Select * from temp_'||Replace(Table_Name,'_','')
from 
 information_schema.columns
where 
   table_schema = 'schema_here'
   
group by table_name
order by 1

quick and dirty just the way I like... Cheers.

PostgresSql extract data using NPGSql Drivers

Ok we extract data from postgres but what happen when those tables have millions of records and you still need to used a datatable for it,
well the solution create two queries the first query get the limit 50000 records then extract the maxid from the datatable set your flag that indicate is the second run and loop 50k at the time until the extract count is less than the limit.... Here it is....:-)

/*
   Microsoft SQL Server Integration Services Script Task
   Write scripts using Microsoft Visual C# 2008.
   The ScriptMain is the entry point class of the script.
*/

using System;
using Npgsql;
using System.Data;
using System.IO;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_b7b6f2660bba404bb3315fc71b301f0e.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

        /*
The execution engine calls this method when the task executes.
To access the object model, use the Dts property. Connections, variables, events,
and logging features are available as members of the Dts property as shown in the following examples.

To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
To post a log entry, call Dts.Log("This is my log text", 999, null);
To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);

To use the connections collection use something like the following:
ConnectionManager cm = Dts.Connections.Add("OLEDB");
cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";

Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

To open Help, press F1.
*/
        Variables vars = null;
        public void Main()
        {


            //SSIS Variables 
            Dts.VariableDispenser.LockForRead("User::strBatchId");
            Dts.VariableDispenser.LockForRead("User::strfTableName");
            Dts.VariableDispenser.LockForRead("User::intPromoBatchId");
            Dts.VariableDispenser.LockForRead("User::strSqlCmd");
            Dts.VariableDispenser.LockForRead("User::strFileDirectoryPath");
            Dts.VariableDispenser.LockForRead("User::strPostgresCon5");
            Dts.VariableDispenser.LockForRead("User::strImportDirectory5");
            Dts.VariableDispenser.LockForRead("User::intExtractCount");
            Dts.VariableDispenser.LockForRead("User::bolFirstBatch");
            Dts.VariableDispenser.LockForRead("User::strSqlCmdSub");
            
      
            Dts.VariableDispenser.GetVariables(ref vars);

            int promoBatchId = (int)vars["User::intPromoBatchId"].Value;
         
            Boolean FirstBatch = (Boolean)vars["User::bolFirstBatch"].Value;
            String FileName = vars["User::strfTableName"].Value.ToString() + "_" + vars["User::strBatchId"].Value.ToString() + "_";
            FileName = FileName + promoBatchId.ToString() + ".txt";
            
            String Delimiter = "|";
            int PromoBatchId = promoBatchId;
            String fullFilePath = vars["User::strImportDirectory5"].Value.ToString() + FileName;
            Boolean isSuccess;
     
            String sqlcmd = vars["User::strSqlCmd"].Value.ToString();

            String ConnStr = vars["User::strPostgresCon5"].Value.ToString();

            isSuccess = Generate_PostGres_File(ConnStr, sqlcmd, fullFilePath, PromoBatchId, Delimiter);

            // TODO: Add your code here
            vars.Unlock();
            Dts.TaskResult = (int)ScriptResults.Success;
        }
        /*
         * Get Header File
         */
        private String GetFileHeader(ref DataTable dt, String FileDelimiter)
        {
            StringBuilder HeaderFile = new StringBuilder();
           
            String qoute = "";
            String RS = System.Environment.NewLine;
            foreach (DataColumn col in dt.Columns)
            {
                HeaderFile.Append(FileDelimiter);
                HeaderFile.Append(qoute + col.ColumnName + qoute);

            }
            HeaderFile.Remove(0, 1);

            HeaderFile.Append(RS);
            return HeaderFile.ToString();
        }

        private Boolean Generate_PostGres_File(String ConnectionString, String SqlCmd, String FullFilePath, int PromoBatchId, String FileDelimiter)
        {
            String tablename = vars["User::strfTableName"].Value.ToString();
            String SubQuery = vars["User::strSqlCmdSub"].Value.ToString();
            Boolean FirstBatch = (Boolean)vars["User::bolFirstBatch"].Value;
            Boolean head = false;
            String qoute = "";
            String RS = System.Environment.NewLine;
            Int64 PromotionId = 0;
            int ExtractCount = (int) vars["User::intExtractCount"].Value;

            int RowsCount = ExtractCount; 
            
            try
            {
                using (StreamWriter sw = File.CreateText(FullFilePath))
                {
                    while ( RowsCount >= ExtractCount)
                    {
                        using (NpgsqlConnection conn = new NpgsqlConnection(ConnectionString))
                        {
                            conn.Open();
                            using (NpgsqlCommand cmd = new NpgsqlCommand(SqlCmd, conn))
                            {
                                if (!FirstBatch)
                                {
                                    cmd.Parameters.AddWithValue("parameter", BatchId);
                                    cmd.Parameters.AddWithValue("parameter", primarykeyId);
                                }
                                else
                                {
                                    cmd.Parameters.AddWithValue("parameter", BatchId);
                                }

                                using (NpgsqlDataReader reader = cmd.ExecuteReader())
                                {
                                    DataTable dt = new DataTable();
                                    int cnt = 0;
                                    dt.Load(reader);

                                    if (dt != null)
                                    {
                                        String header = String.Empty ;
                                        if (FirstBatch)
                                        {
                                             header = GetFileHeader(ref dt, FileDelimiter);
                                        }
                                        foreach (DataRow row in dt.Rows)
                                        {
                                            StringBuilder rdata = new StringBuilder();
                                            StringBuilder tdata = new StringBuilder();
                                            if (!head && FirstBatch)
                                            {
                                                rdata.Append(header);
                                            }
                                            head = true;
                                            foreach (object item in row.ItemArray)
                                            {
                                                if (item is System.DBNull)
                                                {
                                                    tdata.Append(FileDelimiter + qoute + qoute);
                                                }

                                                else
                                                {
                                                    String columnData = item.ToString();
                                                    tdata.Append(FileDelimiter);
                                                    tdata.Append(qoute + columnData + qoute);

                                                }


                                            }
                                            tdata.Remove(0, 1);
                                            tdata.Append(RS);
                                            rdata.Append(tdata.ToString());
                                            tdata = null;

                                            sw.Write(rdata);

                                        }



                                    }
                                    
                                    cnt = dt.Rows.Count;
                                    RowsCount =  cnt;
                                    PromotionId = (Int64)dt.Compute("MAX(Promotion_Id)", string.Empty);
                                    if (FirstBatch)
                                    {
                                        FirstBatch = false;
                                        SqlCmd = vars["User::strSqlCmdSub"].Value.ToString();
                                    }
                                   
                                   
                                }

                            }
                            conn.Close();


                        }
                    }
                  
                }
---delete records that are simply empty (files)
                FileInfo _f = new FileInfo(FullFilePath);
                if (_f.Exists & _f.Length == 0)
                {
                    _f.Delete();
                }

                return true;
            }

            catch
            {
                return false;
            }
        }
    }
}

Connect To PostgreSQL Using SSIS

Lately I have been working on a sync process between postgres and other databases, so after much research about drivers I found that the odbc drivers for postgres really sucks. So, after much pain I found the NPSQL drivers and after some thought said wait... I can connect using the script task and generate the files of my tables using metadata and because is ssis not need to build a new service. It is a pain but it gets the job done.
So... Here it is...
/*
   Microsoft SQL Server Integration Services Script Task
   Write scripts using Microsoft Visual C# 2008.
   The ScriptMain is the entry point class of the script.
*/

using System;
using Npgsql;
using System.Data;
using System.IO;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_b7b6f2660bba404bb3315fc71b301f0e.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

        /*
The execution engine calls this method when the task executes.
To access the object model, use the Dts property. Connections, variables, events,
and logging features are available as members of the Dts property as shown in the following examples.

To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
To post a log entry, call Dts.Log("This is my log text", 999, null);
To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);

To use the connections collection use something like the following:
ConnectionManager cm = Dts.Connections.Add("OLEDB");
cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";

Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

To open Help, press F1.
*/
        Variables vars = null;
        public void Main()
        {
           
            
            //SSIS Variables 
            Dts.VariableDispenser.LockForRead("User::strBatchId");
            Dts.VariableDispenser.LockForWrite("User::strFileName");
            Dts.VariableDispenser.LockForRead("User::strTableName");
            Dts.VariableDispenser.LockForRead("User::strSqlCmd");
            Dts.VariableDispenser.LockForRead("User::strFileDirectoryPath");
            Dts.VariableDispenser.LockForRead("User::strPostgresConStr");

            Dts.VariableDispenser.GetVariables(ref vars);
            String FileName = vars["User::strTableName"].Value.ToString() + "_" + vars["User::strBatchId"].Value.ToString()+".txt";
       

            String Delimiter = "|";
            int PromoBatchId = 5;
            String fullFilePath = vars["User::strFileDirectoryPath"].Value.ToString() + FileName;
            Boolean isSuccess;
            //String UID = "postgres";
            //String PWD = "Pa$$w0rd1";
            String sqlcmd = vars["User::strSqlCmd"].Value.ToString();

            String ConnStr = vars["User::strPostgresConStr"].Value.ToString();
                //String.Format("Pooling=true;MinPoolSize=1;MaxPoolSize=20;Timeout=30;Server=" + ServerName + ";Database=" + DatabaseName + ";Port=" + DbPort + ";Integrated Security=true");

            isSuccess = Generate_PostGres_File(ConnStr, sqlcmd, fullFilePath, PromoBatchId,Delimiter);
            vars.Unlock();

            if (isSuccess)
                Dts.TaskResult = (int)ScriptResults.Success;
            else
                Dts.TaskResult = (int)ScriptResults.Failure;


            // TODO: Add your code here
            
        }
        /*
         * Get Header File
         */
        private String GetFileHeader(ref DataTable dt,String FileDelimiter)
        {
            StringBuilder HeaderFile = new StringBuilder();

            String qoute = "";
            String RS = System.Environment.NewLine;
            foreach(DataColumn col in dt.Columns)
            {
                HeaderFile.Append(FileDelimiter);
                HeaderFile.Append(qoute + col.ColumnName + qoute);
               
            }
            HeaderFile.Remove(0,1);       
          
            HeaderFile.Append(RS);
           return HeaderFile.ToString();
        }
        private Boolean Generate_PostGres_File(String ConnectionString, String SqlCmd,String FullFilePath,int PromoBatchId,String FileDelimiter)
        {
            String tablename = vars["User::strTableName"].Value.ToString();
            Boolean head = false;
            String qoute = "";
            String RS = System.Environment.NewLine;
            try
            {
                using (StreamWriter sw = File.CreateText(FullFilePath))
                {
                    using (NpgsqlConnection conn = new NpgsqlConnection(ConnectionString))
                    {
                      
                        conn.Open();
                        using (NpgsqlCommand cmd = new NpgsqlCommand(SqlCmd, conn))
                        {
                            //if (tablename.ToLower().CompareTo("promotion")==0)
                            //{
                            //    cmd.Parameters.AddWithValue("promobatchid", PromoBatchId);
                            //}
                            //cmd.CommandText = sqlcmd;
                            using (NpgsqlDataReader reader = cmd.ExecuteReader())
                            {
                                DataTable dt = new DataTable();
                               
                                dt.Load(reader);
                                String header = GetFileHeader(ref dt, FileDelimiter);
                                foreach (DataRow row in dt.Rows)
                                {
                                    StringBuilder rdata = new StringBuilder();
                                    StringBuilder tdata = new StringBuilder();
                                    if (head == false)
                                    {
                                        rdata.Append(header);
                                    }
                                    head = true;
                                    foreach (object item in row.ItemArray)
                                    {
                                      if (item is System.DBNull)
                                      {
                                          tdata.Append(FileDelimiter + qoute + qoute);
                                      }
                                    
                                    else
                                    {
                                        String columnData = item.ToString();
                                        tdata.Append(FileDelimiter);
                                        tdata.Append(qoute + columnData + qoute);
                                        
                                    }
                                     
                                      
                                    }
                                    tdata.Remove(0, 1);
                                    tdata.Append(RS);
                                    rdata.Append(tdata.ToString());
                                    tdata = null;

                                    sw.Write(rdata);
                                    rdata = null;
                                   
                              
                                }

                            }
                        }
                       
                        conn.Clone();
                    }
                }
                 return true;
            }
               
            catch (Exception e) 
            {
                Dts.Events.FireError(0, "PostGres FileGenerator Error", e.ToString(), string.Empty, 0);
                return false;
              
            }
        }
    }
}

Find Object reference across Database

Don't you always want to know where a table or database base object is been used... Well here it is... enjoy..
Use DatabasNameHere;
go
Set transaction isolation level read uncommitted;
set nocount on;

declare @TableName varchar(80) = 'TableNameGoesHere'
declare @TableSchema varchar(50) = 'TableSchemaGoesHere'
declare @objectid bigint = object_id(@TableSchema+'.'+@TableName);


;with cte_refe as
(
SELECT 
    OBJECT_SCHEMA_NAME ( referencing_id ) AS referencing_schema_name,
    OBJECT_NAME(referencing_id) AS referencing_entity_name, 
object_name(o.Parent_object_id) as Parent_Name,
    o.type_desc AS referencing_desciption, 
    COALESCE(COL_NAME(referencing_id, referencing_minor_id), '(n/a)') AS referencing_minor_id, 
    referencing_class_desc, referenced_class_desc,
    referenced_server_name, referenced_database_name, referenced_schema_name,
    referenced_entity_name, 
    COALESCE(COL_NAME(referenced_id, referenced_minor_id), '(n/a)') AS referenced_column_name,
    is_caller_dependent, 
is_ambiguous,
is_replicated,
o.is_published,
m.definition

FROM sys.sql_expression_dependencies AS sed
INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id
inner join sys.tables t on t.object_id = @objectid
left outer join sys.all_sql_modules m
on (m.object_id = sed.referencing_id) 

WHERE  ((@objectid = referenced_id) or (@objectid = o.parent_object_id))
--order by 3,1,2
)
select distinct 
       referencing_desciption
  ,Parent_Name
 ,isnull (referencing_schema_name,'dbo')+'.'+referencing_entity_name referencing_entity_name
 ,isnull (referenced_schema_name,'dbo')+'.'+referenced_entity_name As TableName
-- ,referenced_server_name
 ,@@SERVERNAME ServerName
,is_caller_dependent 
  ,is_ambiguous
,is_replicated
,is_published
,definition
 
from
cte_refe
where referencing_entity_name  not like '%syncobj_0%'
--where referenced_server_name is not null
order by 1 desc,2

Search All objects that reference a table or object

Lately I have been searching and analyzing many of our systems, so, as been a lazy programmer here is a script to search any object within sql sever 2008 and above and get all the references that are using that table, procedure, view etc...

Here it is...
use AdventureWorks2012
go
set transaction isolation level read uncommitted;
set nocount on;

declare @TableList table (ObjectID bigint,Table_Schema varchar(50),Table_Name varchar(80))

insert into @TableList(objectid,Table_Schema,Table_Name)
select
 object_id(Table_Schema+'.'+Table_name),TABLE_SCHEMA,TABLE_NAME
from INFORMATION_SCHEMA.tables where TABLE_SCHEMA = 'Person'

SELECT 
    OBJECT_SCHEMA_NAME ( referencing_id ) AS referencing_schema_name
    ,OBJECT_NAME(referencing_id) AS referencing_entity_name 
    ,o.type_desc AS referencing_desciption 
    ,COALESCE(COL_NAME(referencing_id, referencing_minor_id), '(n/a)') AS referencing_minor_id
    ,referencing_class_desc
, referenced_class_desc
    ,referenced_server_name
, referenced_database_name
,referenced_schema_name
    ,referenced_entity_name
,object_NAME (parent_object_id) as Parent_Name
    ,COALESCE(COL_NAME(referenced_id, referenced_minor_id), '(n/a)') AS referenced_column_name
    ,is_caller_dependent
,is_ambiguous
FROM 
sys.sql_expression_dependencies AS sed
INNER JOIN sys.objects AS o 
ON sed.referencing_id = o.object_id
INNER JOIN @TableList tcs 
ON ((referenced_id = tcs.objectid) or (parent_object_id = tcs.ObjectID))
order by 3 desc

SSIS Remove Special Characters For All Columns

/* 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)
    {
        //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);
    }

}