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.