April 22, 2016

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

April 12, 2016

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

Contact Form

Name

Email *

Message *