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

Read Transaction logs for delete and drop records/objects

Someone is having fun deleting records and dropping objects on our development database, so, after adding some DDL triggers and also researching on the logs we found our culprit. So here is what was done on the logs...

----- Delete records
SELECT 
    [Transaction ID],
    Operation,
    Context,
    AllocUnitName
    
FROM 
    fn_dblog(NULL, NULL) 
WHERE 
    Operation = 'LOP_DELETE_ROWS'

go
--- Get transaction info
SELECT
    Operation,
    [Transaction ID],
    [Begin Time],
    [Transaction Name],
    [Transaction SID]
FROM
    fn_dblog(NULL, NULL)
WHERE
    [Transaction ID] = '0000:2a9ebb7c' --- TransactionId 
AND
    [Operation] = 'LOP_BEGIN_XACT'

go
Use Master;
SELECT SUSER_SNAME(0xABCC8713F5E4F04F95873B61C41D3CDA)--TransactionSid

go
----Drop table statement
SELECT 
Operation,
[Transaction Id],
[Transaction SID],
[Transaction Name],
 [Begin Time],
   [SPID],
   Description
FROM fn_dblog (NULL, NULL)
WHERE [Transaction Name] = 'DROPOBJ'
--
Use Master;
SELECT SUSER_SNAME(0xABCC8713F5E4F04F95873B61C41D3CDA)--TransactionSid

PostgresSql Inherit table Generator

Working in many projects and learning new technologies, now everything is about postgresSql and non-sql databases...fun.fun.fun.

So after most debate about creating partitions we decided to used inherit tables. 

So here is a function that will do that :-)

Cheers...

-- Function: SchemaName.batch_gen_complete(integer, character varying)

-- DROP FUNCTION SchemaName.batch_gen_complete(integer, character varying);

CREATE OR REPLACE FUNCTION generate_inherid_child(

    _batch_stage integer,

    _modified_by character varying)

  RETURNS void AS

$BODY$

DECLARE

               _count int;

               _gen_count int;

               _active_count int;

               _is_clean_unique bool;

               _is_clean_duplicate bool;

               _tablename text;

               _exist bool;

BEGIN

    --- Get count of records from stage

              

               SELECT count INTO _count FROM batch_stage WHERE promo_batch_stage = _promo_batch_stage;

               SELECT generated_count, is_clean_unique, is_clean_duplicate INTO _gen_count, _is_clean_unique, _is_clean_duplicate

               FROM batch_generator

               WHERE promo_batch_stage_id = _promo_batch_stage_id;

               IF _gen_count < _count Then

                              RAISE EXCEPTION 'Not enough promo codes available to be able to complete batch: %', _promo_batch_stage;

                              return;

               End If;

               IF _is_clean_unique = false Then

                              RAISE EXCEPTION 'Batch is not marked as clean for unique values, so unable to complete: %', _promo_batch_stage;

                              return;

               End If;

               IF _is_clean_duplicate = false Then

                              RAISE EXCEPTION 'Batch is not marked as clean for duplicate values against active promotions, so unable to complete: %', _promo_batch_stage;

                              return;

               End If;

               SELECT count(*) INTO _active_count FROM generator_parent WHERE promo_batch_stage_id = _promo_batch_stage_id;

               IF _active_count >= _count Then

                              --RAISE EXCEPTION 'Batch already has entries in promotion table, so unable to complete: %', _promo_batch_stage;

                              --return;

                              RAISE INFO '% - %', 'Batch already has entries in promotion table, marking batch complete.', now();

               Else

                              If _count > 9999 Then

                                            

                                             -- Put large batches into an inherited table

                                             _tablename := '_batch_' || _promo_batch_stage;

                                             EXECUTE 'SELECT EXISTS (SELECT 1 FROM pg_tables WHERE schemaname=''SchemaName'' and tablename=''' || quote_ident(_tablename) || ''')' INTO _exist;

                                             IF _exist Then

                                                            -- Delete data from a previous run

                                                            EXECUTE 'TRUNCATE SchemaName.' || quote_ident(_tablename);

                                            

                                             Else

                                            

                                                            -- Create inherited table for large batches

                                                            EXECUTE 'CREATE TABLE SchemaName.' || quote_ident(_tablename) || ' (CHECK ( batch_stage = ' || _batch_stage || ' )) INHERITS (generator_parent)';

                                             End If;

                                            

                                             -- Copy data from temp table to inherited table

                                             EXECUTE format('Insert Into SchemaName.%s (promo_batch_stage, promo_number, is_active, created_by, created_date)       

                                                            Select promo_batch_stage, promo_number, 1::boolean, %L, now()

                                                            From generator_parent_temp

                                                            Where promo_batch_stage = %L

                                                            LIMIT %L'

                                                            , _tablename, _modified_by, _promo_batch_stage, _count);

                                             IF _exist = false Then

                                                            -- Assign owner of of inherited table

                                                            EXECUTE format('ALTER TABLE SchemaName.%s OWNER TO db_owner', _tablename);

                                                            -- Add primary key

                                                            EXECUTE format('ALTER TABLE SchemaName.%s ADD PRIMARY KEY(child_id)', _tablename, _tablename);

                                                           

                                                            --Add FK promo_batch_stage

                                                            EXECUTE format('ALTER TABLE SchemaName.%s ADD CONSTRAINT FK_%s FOREIGN KEY(batch_stage) REFERENCES SchemaName.promo_batch (batch_stage)  MATCH FULL',_tablename, _tablename);

                                                            -- Need to define indexes for inherited tables

                                                            EXECUTE format('CREATE INDEX ix_%s_promo_batch_stage ON SchemaName.%s USING btree ("batch_stage")', _tablename, _tablename);

                                                            //EXECUTE format('CREATE UNIQUE INDEX ix_%s_promo_number ON SchemaName.%s ("")', _tablename, _tablename);

                                             End If;

                                            

                              Else

                                             -- Put smaller batches into general table

                                             Insert Into generator_parent_general (promo_batch_stage, promo_number, is_active, created_by, created_date)

                                             Select batch_stage_id, batch_number, 1::boolean, _modified_by, now()

                                             From generator_parent_temp

                                             Where batch_stage_id = _batch_stage_id

                                             LIMIT _count;

                                            

               End If;

               End If;

               -- Remove codes from staging table

               Delete From generator_parent_temp

               Where batch_stage = _batch_stage;

                             

               -- Mark any incomplete log entries as complete

              

END      

$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100;

ALTER FUNCTION SchemaName.batch_gen_complete(integer, character varying)

  OWNER TO db_owner;

GRANT EXECUTE ON FUNCTION SchemaName.batch_gen_complete(integer, character varying) TO public;

GRANT EXECUTE ON FUNCTION SchemaName.batch_gen_complete(integer, character varying) TO db_executor;

REVOKE ALL ON FUNCTION SchemaName.batch_gen_complete(integer, character varying) FROM db_owner;