Directory Validation Script Using SSIS

In continuation of adding more small code, here is something that it can used to validate the physical location of the file directories, when processing files.
/*
   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
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    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;
        }
    }
}

Some old Scripts from a far far galaxy.

/* Script to set database to single user mode and close all user connections
  Note that this command will kill all user connections used the RollBack After
  command to wait for users to log out.
 */
Use Master
go
Alter Database WriteDatabaseNameHere
Set Single_User With Rollback Immediate
go

---Now let said you want to rename the db
Alter database WriteDatabaseNameHere modify Name = WriteNewDatabaseNameHere
go
--Finally set database back to multi_user mode
Alter Database WriteDatabasenameHere
set Multi_user
go
USE DATABASENAME
select
         tb.name as TableName
         ,max (p.rows) as RecordCount
  from sys.tables tb
 inner join sys.indexes i
 on tb.object_id = i.object_id
 and tb.type = 'U'
inner join sys.partitions p
on p.object_id = i.object_id
and p.index_id= i.index_id
group by tb.name
order by RecordCount
go
--------------4. Restore Relationships. ON delete NO action to be
research ------------

/* Restore relationships for delete set to no action; for update
set to no action as on

   original reverse eng. model

 */



declare @ChildTable varchar (max) -- Child table Name

declare @ChildColumn varchar(max)-- Child column Name

declare @MasterTable varchar (max) -- Master TAble

declare @MasterColumn varchar (max) -- Master Column reference

declare @sqlcmd varchar (max) -- Sql Command

declare @ConstraintName varchar(max) -- Constraint Name

declare ADD_Constraint cursor

fast_forward for

select distinct ConstraintName,ChildTable,ChildColumn,MasterTable,MasterColumn

from [CoDE].[dbo].t_FK_Xref order
by ConstraintName

open ADD_Constraint


fetch next from ADD_Constraint

into @ConstraintName,

       @ChildTable,

       @ChildColumn,

       @MasterTable,

     @MasterColumn

     

while @@Fetch_Status = 0

begin

begin try

 select @sqlcmd = 'alter
table '+@ChildTable+' with nocheck add constraint '+@ConstraintName+' foreign key '+'('+@ChildColumn+')'+' references '+@MasterTable+' ('+@MasterColumn+')'+' on delete no action on update no action'

exec (@sqlcmd)

 fetch next from ADD_Constraint

into @ConstraintName,

       @ChildTable,

       @ChildColumn,

       @MasterTable,

     @MasterColumn

end try

begin catch

print @sqlcmd+'
***** Error checkpoint '

fetch next from ADD_Constraint

into @ConstraintName,

       @ChildTable,

       @ChildColumn,

       @MasterTable,

     @MasterColumn

end catch

end

close ADD_Constraint

Deallocate ADD_Constraint

go

---------------5. Restore CHECK Constraints---------------

 -- Now enable referential integrity again

--EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'



print 'Constraints Restored'

SQLSaturday Tampa #371

Lately I have been busy working with the ssis script task component, however, today I'm starting to work on my presentation from scratch again. So, lets the fun begin, one more week before traveling to Tampa.

As always, here is some code to remove special characters in Fly.

/* 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 System.Text.RegularExpressions;

using Microsoft.SqlServer.Dts.Pipeline;

using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]

public class ScriptMain : UserComponent

{

    public override void PreExecute()

    {

        base.PreExecute();

        /*

          Add your code here for preprocessing or remove if not needed

        */

    }

    public override void PostExecute()

    {

        base.PostExecute();

        /*

          Add your code here for postprocessing or remove if not needed

          You can set read/write variables here, for example:

          Variables.MyIntVar = 100

        */

    }

    public override void Input0_ProcessInputRow(Input0Buffer Row)

    {

       

    }

    public override void ProcessInput(int InputID, PipelineBuffer Buffer)

    {

        string pattern = "[^ -~]*";

        Regex reg_exp = new Regex(pattern);

        //bool fireAgain = false;

        //ComponentMetaData.FireInformation(0, "",

        //  Buffer.ColumnCount.ToString() + " columns",

        //  "", 0, ref fireAgain);

        int index = 0;

        String DataBackup = "";

        while (Buffer.NextRow())

        {

            try

            {

                for (int columnIndex = 0;

                  columnIndex < Buffer.ColumnCount;

                  columnIndex++)

                {

                    index = columnIndex;

                    string columnData = null;

                    if (Buffer.IsNull(columnIndex))

                    {

                        columnData = "is NULL";

                    }

                    else

                    {

                        BufferColumn columnInfo = Buffer.GetColumnInfo(columnIndex);

                        switch (columnInfo.DataType)

                        {

                            case DataType.DT_WSTR:

                            case DataType.DT_STR:

                                columnData += Buffer.GetString(columnIndex);

                                DataBackup = columnData;//Save backup

                                columnData = reg_exp.Replace(columnData, "");

                                Buffer.SetString(columnIndex, columnData);

                                index = 0; //Reset index;

                                DataBackup = ""; //Reset data

                                break;

                            // add code to support more data types here

                            default:

                                columnData = "";

                                index = 0; //Reset index;

                                DataBackup = ""; //Reset data

                                break;

                        }

                    }

                    //ComponentMetaData.FireInformation(0, "",

                    //  "Column " + columnIndex.ToString() + ": " + columnData,

                    //  "", 0, ref fireAgain);

                }

            }

            catch

            {

                Buffer.SetString(index, DataBackup);

                index = 0;

                DataBackup = "";

            }

        }

        base.ProcessInput(InputID, Buffer);

    }

}