Make it simple (recovery mode)

This is one of those posts that is probably only useful for me. At work we have some VMs set up for general purpose development. Each dev has one assigned to them except mine is no longer mine. Hazards of leaving a job I suppose. So on my return, I've been camping out on other, less used VMs when I need to have a long running process. One of the things I notice though is that these boxes are nearly out of space.

Why? Because databases are all in FULL recovery mode. This setting makes sense in production environments but as no one is taking backups in this dev environment, this setting causes the database log files to grow and grow and grow and poof, the machines are out of disk space. 

The cure, the following query will generate a row for every database in the instance in full recovery mode. I then copy and paste the results back into SSMS/sqlcmd and voila, things are fixed.

SELECT

    'ALTER DATABASE ' + quotename(DB.name) + ' SET RECOVERY SIMPLE WITH NO_WAIT' AS cmd

FROM

    sys.databases DB 

WHERE

    DB.recovery_model_desc = 'full';

 

I then shrink the log but that's ok because it never should have grown that large.

Row Count with SSIS EzAPI

Haha, this is a trick. There is no EzAPI component for Row Count Transformation. Until now!

    [CompID("{150E6007-7C6A-4CC3-8FF3-FC73783A972E}")]
    public class EzRowCountTransform : EzComponent
    {
        public EzRowCountTransform(EzDataFlow dataFlow) : base(dataFlow) { }
        public EzRowCountTransform(EzDataFlow parent, IDTSComponentMetaData100 meta) : base(parent, meta) { }

        public string VariableName
        {
            get { return (string)Meta.CustomPropertyCollection["VariableName"].Value; }
            set { Comp.SetComponentProperty("VariableName", value); }
        }
    }

EzAPI 2012 referenced assembly issue

A brief post on using the current source build of EzAPI. If you try to compile it, you will receive this error

Assembly 'Microsoft.SqlServer.TxScript, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' uses 'Microsoft.SqlServer.VSTAScriptingLib, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' which has a higher version than referenced assembly 'Microsoft.SqlServer.VSTAScriptingLib, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'

No problem you say. You expand your references and see that the project found the 2008 DLL and you just need to change the path from "C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.VSTAScriptingLib.dll" to "C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.VSTAScriptingLib.dll"

The only problem is, there isn't a "c:\Program Files\Microsoft SQL Server\110\DTS\PipelineComponents\Microsoft.SqlServer.TxScript.dll"

I have no idea why that DLL isn't installed in that location, or any location in the Program Files|Program Files (x86) location but whatever. The assembly is on the machine as SSIS works, you'll simply need to get it out of the GAC (Global Assembly Cache). I'm lazy and just repointed my reference into the GAC but the other option is to copy it into the expected location.

copy C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.SqlServer.VSTAScriptingLib\v4.0_11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.VSTAScriptingLib.dll "c:\Program Files (x86)\Microsoft SQL Server\110\DTS\PipelineComponents"