June 24, 2016

String Sequence Combination using SQL

A friend make a great function that do a sequence combination, and after a little thought I said lets shared with the world as this could be a good example of cte recursion.
Cheers,

declare
@String VARCHAR(8000) = 'Hello how do you do today',
@Delimiter CHAR = ' ',
@n INT = 9

--output
declare @List TABLE (
Word VARCHAR(800),
WordNum INT,
n INT
)

DECLARE
@Iterator INT = 1,
@Word VARCHAR(800) = '',
@Pos INT = 1
;

DECLARE @Temp TABLE (
Word VARCHAR(800),
WordNum INT,
n INT
)

SET @String = REPLACE(LTRIM(RTRIM(@String)),'  ',' ');

WHILE @Pos > 0
BEGIN
SET @Pos = CHARINDEX(@Delimiter, @String)
IF @Pos > 0
BEGIN
SET @Word = SUBSTRING(@String,0,@Pos)
SET @String = RIGHT(@String, LEN(@String)- @Pos)
END
ELSE
BEGIN
SET @Word = @String;
END

INSERT INTO @Temp(Word, WordNum) VALUES(@Word, @Iterator);

SET @Iterator = @Iterator + 1;
END
;

WITH cte AS(
SELECT CAST(cte.Word AS VARCHAR(800)) AS Word,
cte.WordNum,
1 AS n
FROM @Temp cte
UNION ALL
SELECT CAST(cte.Word + @Delimiter + l.Word AS VARCHAR(800)) AS Word,
l.WordNum,
cte.n + 1
FROM cte
INNER JOIN @Temp l
ON cte.WordNum + 1 = l.WordNum
WHERE cte.n < @n
)
INSERT INTO @List(Word, WordNum, n)
SELECT Word, WordNum, n
FROM cte
ORDER BY n, WordNum

select * from @List
;

June 08, 2016

RSA encryption Using SSIS Script component




One of my latest task is to encrypt data using RSA so instead of spending my time building a new service, I create a package and added the below code to the pipeline and BOOM...
Task completed.
enjoy...
#region Help:  Introduction to the Script Component
/* The Script Component allows you to perform virtually any operation that can be accomplished in
 * a .Net application within the context of an Integration Services data flow.
 *
 * Expand the other regions which have "Help" prefixes for examples of specific ways to use
 * Integration Services features within this script component. */
#endregion

#region Namespaces

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;
using System.Security.Cryptography;
using System.Text;
#endregion

/// <summary>

/// This is the class to which to add your code.  Do not change the name, attributes, or parent
/// of this class.
/// </summary>
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    #region Help:  Using Integration Services variables and parameters
    /* To use a variable in this script, first ensure that the variable has been added to
     * either the list contained in the ReadOnlyVariables property or the list contained in
     * the ReadWriteVariables property of this script component, according to whether or not your
     * code needs to write into the variable.  To do so, save this script, close this instance of
     * Visual Studio, and update the ReadOnlyVariables and ReadWriteVariables properties in the
     * Script Transformation Editor window.
     * To use a parameter in this script, follow the same steps. Parameters are always read-only.
     *
     * Example of reading from a variable or parameter:
     *  DateTime startTime = Variables.MyStartTime;
     *
     * Example of writing to a variable:
     *  Variables.myStringVariable = "new value";
     */
    #endregion

    #region Help:  Using Integration Services Connnection Managers

    /* Some types of connection managers can be used in this script component.  See the help topic
     * "Working with Connection Managers Programatically" for details.
     *
     * To use a connection manager in this script, first ensure that the connection manager has
     * been added to either the list of connection managers on the Connection Managers page of the
     * script component editor.  To add the connection manager, save this script, close this instance of
     * Visual Studio, and add the Connection Manager to the list.
     *
     * If the component needs to hold a connection open while processing rows, override the
     * AcquireConnections and ReleaseConnections methods.
     * 
     * Example of using an ADO.Net connection manager to acquire a SqlConnection:
     *  object rawConnection = Connections.SalesDB.AcquireConnection(transaction);
     *  SqlConnection salesDBConn = (SqlConnection)rawConnection;
     *
     * Example of using a File connection manager to acquire a file path:
     *  object rawConnection = Connections.Prices_zip.AcquireConnection(transaction);
     *  string filePath = (string)rawConnection;
     *
     * Example of releasing a connection manager:
     *  Connections.SalesDB.ReleaseConnection(rawConnection);
     */
    #endregion

    #region Help:  Firing Integration Services Events

    /* This script component can fire events.
     *
     * Example of firing an error event:
     *  ComponentMetaData.FireError(10, "Process Values", "Bad value", "", 0, out cancel);
     *
     * Example of firing an information event:
     *  ComponentMetaData.FireInformation(10, "Process Values", "Processing has started", "", 0, fireAgain);
     *
     * Example of firing a warning event:
     *  ComponentMetaData.FireWarning(10, "Process Values", "No rows were received", "", 0);
     */
    #endregion

    /// <summary>

    /// This method is called once, before rows begin to be processed in the data flow.
    ///
    /// You can remove this method if you don't need to do anything here.
    /// </summary>
    public override void PreExecute()
    {
        base.PreExecute();
        /*
         * Add your code here
         */
    }

    /// <summary>

    /// This method is called after all the rows have passed through this component.
    ///
    /// You can delete this method if you don't need to do anything here.
    /// </summary>
    public override void PostExecute()
    {
        base.PostExecute();
        /*
         * Add your code here
         */
    }

    /// <summary>

    /// This method is called once for every row that passes through the component from Input0.
    ///
    /// Example of reading a value from a column in the the row:
    ///  string zipCode = Row.ZipCode
    ///
    /// Example of writing a value to a column in the row:
    ///  Row.ZipCode = zipCode
    /// </summary>
    /// <param name="Row">The row that is currently passing through the component</param>
    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        /*
         * Add your code here
         */
    }

    // Going to encrypt the pipeline as it comes. 

    public override void ProcessInput(int InputID, PipelineBuffer Buffer)
    {
      
       
        String publicPrivateKey = this.Variables.strPublicPrivateKey.ToString();
        String publicKey = this.Variables.strPublicKey.ToString();

        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 = RSAToColumnEncrypt(columnData, publicKey);
                                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;
                        }
                    }
                    
                }
            }
            catch
            {
                Buffer.SetString(index, DataBackup);
                index = 0;
                DataBackup = "";
            }
        }
        base.ProcessInput(InputID, Buffer);
    }
    static public String  RSAToColumnEncrypt (String ColumnData,String EncrKey)
    {
          try
        {
            //Create a UnicodeEncoder to convert between byte array and string.
           // UnicodeEncoding ByteConverter = new UnicodeEncoding();

            //Create byte arrays to hold original, encrypted, and decrypted data.

            
            byte[] dataToEncrypt = System.Text.Encoding.UTF8.GetBytes(ColumnData);// Plain Text 
            byte[] encryptedData;
           

            //Create a new instance of RSACryptoServiceProvider to generate

            //public and private key data.
            using (RSACryptoServiceProvider RSA = new RSACryptoServiceProvider())
            {
                RSA.FromXmlString(EncrKey); // reference public key

                //Pass the data to ENCRYPT, the public key information 

                //(using RSACryptoServiceProvider.ExportParameters(false),
                //and a boolean flag specifying no OAEP padding.
                
                encryptedData = RSAEncrypt(dataToEncrypt, RSA.ExportParameters(false), false);
                String encryptedString = Convert.ToBase64String(encryptedData);
                return encryptedString;

                //Pass the data to DECRYPT, the private key information 

                //(using RSACryptoServiceProvider.ExportParameters(true),
                //and a boolean flag specifying no OAEP padding.
                //decryptedData = RSADecrypt(encryptedData, RSA.ExportParameters(true), false);

                //Display the decrypted plaintext to the console. 

                //Console.WriteLine("Decrypted plaintext: {0}", ByteConverter.GetString(decryptedData));
            }
        }
        catch (ArgumentNullException)
        {
            //Catch this exception in case the encryption did
            //not succeed.
            return "Encryption failed.";

        }

    }
    static public String RSAToColumnDecrypt(String EncrypData,String privatekey)
    {
        try
        {
            byte[] EncryptArray = Convert.FromBase64String(EncrypData);
            byte[] DecryptArray;

            

            using (RSACryptoServiceProvider RSA = new RSACryptoServiceProvider())
            {
                RSA.FromXmlString(privatekey); 
                DecryptArray = RSADecrypt(EncryptArray, RSA.ExportParameters(true), false);
                return Encoding.UTF8.GetString(DecryptArray);
            }

        }

        catch
        {
            //Catch this exception in case the encryption did
            //not succeed.
            return "Decrypt failed.";
        }
    }
    static public byte[] RSAEncrypt(byte[] DataToEncrypt, RSAParameters RSAKeyInfo, bool DoOAEPPadding)
    {
        try
        {
            byte[] encryptedData;
            //Create a new instance of RSACryptoServiceProvider.
            using (RSACryptoServiceProvider RSA = new RSACryptoServiceProvider())
            {
               
                //Import the RSA Key information. This only needs
                //toinclude the public key information.
                RSA.ImportParameters(RSAKeyInfo);

                //Encrypt the passed byte array and specify OAEP padding.  
                //OAEP padding is only available on Microsoft Windows XP or
                //later.  
                encryptedData = RSA.Encrypt(DataToEncrypt, DoOAEPPadding);
            }
            return encryptedData;
        }
        //Catch and display a CryptographicException  
        //to the console.
        catch (CryptographicException e)
        {
            Console.WriteLine(e.Message);

            return null;

        }

    }

    static public byte[] RSADecrypt(byte[] DataToDecrypt, RSAParameters RSAKeyInfo, bool DoOAEPPadding)
    {
        try
        {
            byte[] decryptedData;
            //Create a new instance of RSACryptoServiceProvider.
            using (RSACryptoServiceProvider RSA = new RSACryptoServiceProvider())
            {
                //Import the RSA Key information. This needs
                //to include the private key information.
                RSA.ImportParameters(RSAKeyInfo);

                //Decrypt the passed byte array and specify OAEP padding.  

                //OAEP padding is only available on Microsoft Windows XP or
                //later.  
                decryptedData = RSA.Decrypt(DataToDecrypt, DoOAEPPadding);
            }
            return decryptedData;
        }
        //Catch and display a CryptographicException  
        //to the console.
        catch (CryptographicException e)
        {
            Console.WriteLine(e.ToString());

            return null;

        }

    }



}


Contact Form

Name

Email *

Message *