January 19, 2017

Script Component Variable Limitation Using Blob

Its' been awhile since my last post. I have been very busy working on new technologies and platforms such as Cassandra, PostgreSql, Elastic Search and Maven projects, not too mention Kafka, so when the opportunity came to generate a ragged right file format I said 'HELL YEA', so he is the problem concatenate a encrypted string into a single row and insert that into a table, after some quick searches and ingenuity I build the below code and the task got done and bury, but everything is not as simple as it seems, the output is inserted into a varchar(max) so initially I used dt_text but not error and not data inserted after much scratching my head "This should work" I change the the type to dt_image as the blob type get a binary array and BOOOMMMM data.
enjoy.
public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        char cquoute = (char)31;
        char cdelimite = (char)30;
        String quote = cquoute.ToString();
        String delimite = quote + cdelimite.ToString() + quote;

        StringBuilder RawData = new StringBuilder();

        RawData.Append(quote + (Row.customerAddressId.ToString()) + delimite);
        RawData.Append((!Row.AddressName_IsNull ? Row.AddressName : "NULL") + delimite);
        RawData.Append((!Row.CompanyName_IsNull ? Row.CompanyName : "NULL") + delimite);
        RawData.Append((!Row.Address1_IsNull ? Row.Address1 : "NULL") + delimite);
        RawData.Append((!Row.Address2_IsNull ? Row.Address2 : "NULL") + delimite);
        RawData.Append((!Row.City_IsNull ? Row.City : "NULL") + delimite);
        RawData.Append((!Row.StateName_IsNull ? Row.StateName : "NULL") + delimite);
        RawData.Append((!Row.Zip_IsNull ? Row.Zip : "NULL") + delimite);
        RawData.Append((!Row.CountryName_IsNull ? Row.CountryName : "NULL") + delimite);
        RawData.Append((!Row.FirstName_IsNull ? Row.FirstName : "NULL") + delimite);
        RawData.Append((!Row.LastName_IsNull ? Row.LastName : "NULL") + delimite);
        RawData.Append((!Row.Phone_IsNull ? Row.Phone : "NULL") + delimite);
        RawData.Append((!Row.IsDefaultBilling_IsNull ? Row.IsDefaultBilling.ToString() : "False") + delimite);
        RawData.Append((!Row.IsDefaultShipping_IsNull ? Row.IsDefaultShipping.ToString() : "False") + delimite);
        RawData.Append((!Row.IsAPO_IsNull ? Row.IsAPO.ToString() : "False") + delimite);
        RawData.Append((!Row.AddressUuid_IsNull ? Row.AddressUuid : "NULL") + delimite);
        RawData.Append((!Row.ModifiedDate_IsNull ? Row.ModifiedDate.ToString() : "NULL") + quote);

        Output0Buffer.AddRow();
        Output0Buffer.customerid = (int) Row.CustomerId;
        Output0Buffer.addressid = (int)Row.customerAddressId;
        Output0Buffer.RawData.AddBlobData(Encoding.UTF8.GetBytes(RawData.ToString()));

September 14, 2016

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

               }
}

Contact Form

Name

Email *

Message *