/* Microsoft SQL Server Integration Services Script Component
*  Write scripts using Microsoft Visual C# 2008.
*  ScriptMain is the entry point class of the script.*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

using System.Data.SqlClient;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{

    IDTSConnectionManager100 connMgr;
    SqlConnection sqlConn;
    SqlCommand sqlCmd, sqlErrCmd;
    SqlParameter sqlParam;

    public override void AcquireConnections(object Transaction)
    {

        connMgr = this.Connections.MyADONETConnectionManager;
        sqlConn = (SqlConnection)connMgr.AcquireConnection(null);

    }

    public override void PreExecute()
    {

        sqlCmd = new SqlCommand("INSERT INTO Person.AddressClone(AddressID, City) " +
            "VALUES(@addressid, @city)", sqlConn);
        sqlParam = new SqlParameter("@addressid", SqlDbType.Int);
        sqlCmd.Parameters.Add(sqlParam);
        sqlParam = new SqlParameter("@city", SqlDbType.NVarChar, 30);
        sqlCmd.Parameters.Add(sqlParam);

    }


    public override void PostExecute()
    {
        base.PostExecute();

        
    }

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        try
        {
        
            
            sqlCmd.Parameters["@addressid"].Value = Row.AddressID;
            sqlCmd.Parameters["@city"].Value = Row.City;
            sqlCmd.ExecuteNonQuery();
        
            
        }
        catch (SqlException e)
        {
            //Remove single quote in the error message as it can create issues while inserting message into error table
            string ErrorMessage = e.Message.ToString().Replace("'", "");
            string ErrorRow = "<AddressID>" + Row.AddressID.ToString() + "</AddressID><City>" + Row.City.ToString() + "</City>";
            string ErrorIssuingPackage = "Address Load Package";
            string ErrorSourceTable = "Adventureworks Address Table";
            string ErrorDestinationTable = "Adventureworks AddressClone Table";
            string ErrCmd = @"INSERT INTO ErrorLog Values ('" + ErrorRow + "','" + ErrorMessage + "','" + ErrorIssuingPackage + "','" + ErrorSourceTable + "','" + ErrorDestinationTable + "',getdate())";

            sqlErrCmd = new SqlCommand(ErrCmd, sqlConn);
            sqlErrCmd.ExecuteNonQuery();
        }
    }

    public override void ReleaseConnections()
    {
        
        connMgr.ReleaseConnection(sqlConn);

    }


}