Postgres To SQL Server Prototype Using NPGSQL Drivers

One more task down. We have move away from ms sql server but, still part of legacy system so once in awhile we need to extract postgres data into ms sql. On my previous code example extraction was done by creating files,but, this time said...mmmm.. let used a data table instead,
this example don't used paging but the logic is hidden somewhere there you just need to look of it.
As Akame will said. "Eliminated"

#region Namespaces

using System;

using System.Data;

using Microsoft.SqlServer.Dts.Runtime;

using System.Windows.Forms;

using Npgsql;

using System.Data.SqlClient;

#endregion

namespace ST_2e3c2d93319f4e9182d04dba0cbac275

{

    /// <summary>

    /// ScriptMain is the entry point class of the script.  Do not change the name, attributes,

    /// or parent of this class.

    /// </summary>

               [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]

               public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

               {

    

         Variables var = null;

                              public void Main()

                              {

          

            Dts.VariableDispenser.LockForRead("User::intNumberRecords");

            Dts.VariableDispenser.LockForRead("User::strPostGresSqlConn");

            Dts.VariableDispenser.LockForRead("User::SQLConnection");

            Dts.VariableDispenser.LockForRead("User::strSqlcmdBase");

            Dts.VariableDispenser.LockForRead("User::strSqlCmdLoop");

            Dts.VariableDispenser.LockForRead("User::strSqlCmdCount");

            Dts.VariableDispenser.GetVariables(ref var);

            Int64 TopNRecords = (Int64)var["User::intNumberRecords"].Value;

            String ConnectionString = var["User::strPostGresSqlConn"].Value.ToString();

            String SqlCmdBase = var["User::strSqlcmdBase"].Value.ToString();

            String SqlCmdLoop = var["User::strSqlCmdLoop"].Value.ToString();

            String SqlCmdCount = var["User::strSqlCmdCount"].Value.ToString();

        

            

            Int64 RecordCount = GetRecordCount(ConnectionString, SqlCmdCount);

            Int64 paging = (int)Math.Ceiling((double)RecordCount / (double)TopNRecords);

            bool isLoaded = ExtractData(ConnectionString, paging);

           

            var.Unlock();

                                             Dts.TaskResult = (int)ScriptResults.Success;

                              }

      

        private Int64 GetRecordCount(String ConnectionString, String SqlCmd)

        {

            try

            {

                Int64 count = 0;

                using (NpgsqlConnection conn = new NpgsqlConnection(ConnectionString))

                {

                    conn.Open();

                    using (NpgsqlCommand cmd = new NpgsqlCommand(SqlCmd, conn))

                    {

                        count = (Int64)cmd.ExecuteScalar();

                    }

                    conn.Close();

                }

                return count;

            }

            catch (Exception e)

            {

                Dts.Events.FireError(0, "ErrorDescription", e.Message.ToString(), String.Empty, 0);

                return 0;

            }

        }

        private bool ExtractData(String ConnectionString, Int64 NumberPage)

        {

            bool IsSucess = false;

            String SqlCmdTempate = var["User::strSqlCmdLoop"].Value.ToString();

            String SqlcmdBase = var["User::strSqlcmdBase"].Value.ToString();

            String SqlConn = var["User::SQLConnection"].Value.ToString();

           

         

            try

            {

                using (NpgsqlConnection conn = new NpgsqlConnection(ConnectionString))

                        {

                            conn.Open();

                            using (NpgsqlCommand cmd = new NpgsqlCommand(SqlcmdBase, conn))

                            {

                                DataTable dt = new DataTable();

                                using (NpgsqlDataReader reader = cmd.ExecuteReader())

                                {

                                  dt.Load(reader);

                                }

                                if (dt != null)

                                {

                                    LoadDataToSql(ref dt, "SQLTableNameHere",SqlConn);

                                }

                             else

                                {

                                  

                                    //raise error not data found

                                    Dts.Events.FireError(0, "No Data Return From Postgres", "No Data Was Return From Postgress,Rerun or validate", String.Empty, 0);

                                }

                                dt = null;

                            }

                            conn.Close();

                        }

                        return IsSucess;

                    }

         catch (Exception e)

            {

                String message = e.Message.ToString();

                Dts.Events.FireError(0, "ErrorDescription", message, String.Empty, 0);

                //raise error

                return false;

            }

        }

        private void LoadDataToSql(ref DataTable dt,String Destination,String ErrorDescription)

        {

            

            try

            {

                using (SqlConnection sqlcon = new SqlConnection(ErrorDescription))

                {

                    sqlcon.Open();

                    CopyData(dt, sqlcon);

                    sqlcon.Close();

                }

            }

            catch(Exception e)

            {

                Dts.Events.FireError(0, "ErrorDescription",e.Message.ToString(), String.Empty, 0);

            }

        }

        static void CopyData(DataTable sourceTable, SqlConnection destConnection)

        {

        

                // new method: SQLBulkCopy:

                using (SqlBulkCopy s = new SqlBulkCopy(destConnection))

                {

                    s.DestinationTableName = "InventorySample";

                    s.ColumnMappings.Add("itemid", "itemid");

                    s.ColumnMappings.Add("quantity", "quantity");

                    s.WriteToServer(sourceTable);

                    s.Close();

                }

            }

        

           

      

        #region ScriptResults declaration

        /// <summary>

        /// This enum provides a convenient shorthand within the scope of this class for setting the

        /// result of the script.

        ///

        /// This code was generated automatically.

        /// </summary>

        enum ScriptResults

        {

            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,

            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

        };

        #endregion

               }

}

Who's the watcher?

First Hurricane hit Florida since the last 10 years.

After a long deployment today setting a new package to migrate our customer data for S3, we took off running from work @6pm. "actually got kit-out" now I'm listening to the anger, and cries of the wind.

Any upgrade = disaster waiting to happen

Ah this was a intense week at work, after major upgrade of our ms sql databases most of the week after that was, and still is fixing sql agent jobs, ssis, configurations you name it we probably did it. 
Tomorrow is another day and what can't be finish today it would be there waiting tomorrow, so it is what it is and it can't be help. I have some nice coding to show but that would be a surprise. Finally, after months of trying my local Cassandra instance just die without any warnings it was working great upgrade the virtual box to the latest version and everything when down into a death spiral after that, so now there are more important things to care about and life will continue with or without us " Roaches and Fish will be the new domain species", so just relax and decompress.

StreamReading StreamWriting Using SSIS Script Task

How many times you need to stream reading from a file and writing into another well here it is...Here we are using special character char(30) and char(31) as delimited so a quick replace using regex did it. It's Simple,Quick, and Dirty :-)
Enjoy.
/*
   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.Text.RegularExpressions;
using System.IO;

namespace ST_2b1bee4bed9c4b0b8499c0b8215df02d.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()
        {
            String filePath = Dts.Variables["User::strTempFilePath"].Value.ToString();
            //char US = (char) 31;
            //char RS = (char)30;
            //File.WriteAllText(filePath, Regex.Replace(File.ReadAllText(filePath), "_x001F_",US.ToString()));
            //File.WriteAllText(filePath, Regex.Replace(File.ReadAllText(filePath), "_x001E_", RS.ToString()));
            ReplaceFile(filePath, filePath.Replace(".cHold", ".txt"));
            // TODO: Add your code here
            Dts.TaskResult = (int)ScriptResults.Success;
        }
        protected void ReplaceFile(String ImportFilePath, String ExportFilePath)
        {
           // H201606301639RowCount:50000
                //User::intCntCustomer,User::strTempFilePath,User::YYYYMMDDHHMMSS
            
            char US = (char)31;
            char RS = (char)30;
            String Header = "H"+Dts.Variables["User::YYYYMMDDHHMMSS"].Value.ToString()+"Rowcount:"+Dts.Variables["User::intCntCustomer"].Value.ToString();
            using (StreamReader vReader = new StreamReader(ImportFilePath))
            {
                using (StreamWriter vWriter = new StreamWriter(ExportFilePath))
                {
                    vWriter.WriteLine(Header);
                    while (!vReader.EndOfStream)
                    {
                        String vLine = Regex.Replace(Regex.Replace(vReader.ReadLine(), "_x001F_", US.ToString()), "_x001E_", RS.ToString());
                        vWriter.WriteLine(vLine);


                    }
                }
            }
        }

    }
}

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
;

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

    }


}