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;

        }

    }



}


April 22, 2016

ssis import data folder validation

/*
   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 System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;

namespace ST_2b49dec3a4254f37bbda2c12f9bc6f95.csproj
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    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.
*/

        public void Main()
        {
            Variables vars = null;
            Dts.VariableDispenser.LockForRead("User::strLoadDirectory");
            Dts.VariableDispenser.LockForRead("User::strHoldDirectory");
            Dts.VariableDispenser.LockForRead("User::strArchiveDirectory");
            Dts.VariableDispenser.LockForRead("User::strDuplicateDirectory");
            Dts.VariableDispenser.LockForRead("User::strErrorDirectory");
            Dts.VariableDispenser.LockForRead("User::FilePath");
            Dts.VariableDispenser.GetVariables(ref vars);

            String Load_Folder_Path = vars["User::strLoadDirectory"].Value.ToString();
            String Hold_Folder_Path = vars["User::strHoldDirectory"].Value.ToString();
            String archive_folder_path = vars["User::strArchiveDirectory"].Value.ToString();
            String dups_folder_path = vars["User::strDuplicateDirectory"].Value.ToString();
            String error_folder_path = vars["User::strErrorDirectory"].Value.ToString();
            String target_folder_path = vars["User::FilePath"].Value.ToString();
            try
            {
                //folder/path verification archive_folder_path
                if (!System.IO.Directory.Exists(Load_Folder_Path))
                {
                    //'MsgBox(archive_folder_path, MsgBoxStyle.Critical, "Invalid archive_folder_path")
                    throw new FileNotFoundException("The Load_folder_path (" + archive_folder_path + ") does not exist!");
                }

                //folder/path verification archive_folder_path
                if (!System.IO.Directory.Exists(archive_folder_path))
                {
                    //'MsgBox(archive_folder_path, MsgBoxStyle.Critical, "Invalid archive_folder_path")
                    throw new FileNotFoundException("The archive_folder_path (" + archive_folder_path + ") does not exist!");
                }
                if (!System.IO.Directory.Exists(dups_folder_path))
                {
                    //'MsgBox(dups_folder_path, MsgBoxStyle.Critical, "Invalid dups_folder_path")
                    throw new FileNotFoundException("The dups_folder_path (" + dups_folder_path + ") does not exist!");
                }

                if (!System.IO.Directory.Exists(error_folder_path))
                {
                    //'MsgBox(error_folder_path, MsgBoxStyle.Critical, "Invalid error_folder_path")
                    throw new FileNotFoundException("The error_folder_path (" + error_folder_path + ") does not exist!");
                }


                if (!System.IO.Directory.Exists(target_folder_path))
                {
                    //'MsgBox(target_folder_path, MsgBoxStyle.Critical, "Invalid target_folder_path")
                    throw new FileNotFoundException("The target_folder_path (" + target_folder_path + ") does not exist!");
                }
            }
            catch
            {
                Dts.TaskResult = (int)ScriptResults.Failure;
            }
            finally
            {
            }
            // TODO: Add your code here
            vars.Unlock();
            vars = null;
            // TODO: Add your code here
            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}

Contact Form

Name

Email *

Message *