A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Monday, April 11, 2011

Log SSIS Version number

Tony: Check it. Make sure it's the right thing.
Léon: I trust you.
Tony: One thing has nothin' to do with the other - remember that Léon.
The Professional

Where I work, security is such that I can't tell you what version of SSIS packages are actually deployed in production. I have implementation memos directing what should be out there but beyond having my DBAs run queries against msdb.dbo.sysdtspackages, I don't know what is actually out there. Furthermore, it might be handy to have a record of what version of a package at a point in time.

It's a trivial script task to add into every package that echos out the VersionBuild of the package. On my todo list, was to turn that simple task into a component so developers can simply drag a task onto the canvas and be done. This would give me the ability to look through our log history and attempt to correlate changes to specific gains and losses in performance.

Scott Stauffer (blog|twitter) recently proposed this #SSIS Tip - expose build version in a package design window by making the main business logic container's name an expression! handy?. He's thinking design-time whereas I'm thinking run-time but still, the need is there in both environments to know what's the frequency (of change) Kenneth? Until his followup tweet, I had never thought about using an expression on a task name. I mean, who cares what a task is called as long as it's not "Data Flow Task 1?" His tweet pointed out that you can use an expression to set the name. Brilliant!

The implementation is dead simple. Expression is

"Version - " +  (DT_WSTR, 10)  @[System::VersionBuild]
        public void Main()
            int versionBuild = -1;
            versionBuild = (Int32)Dts.Variables["System::VersionBuild"].Value;
            int informationCode = -1;
            string subComponent = string.Empty;
            string description = string.Empty;
            string helpFile = string.Empty;
            int helpContext = -1;
            bool fireAgain = true;

            subComponent = "C# Script task";
            description = string.Format("Version => {0}", versionBuild);
            Dts.Events.FireInformation(informationCode, subComponent, description, helpFile, helpContext, ref fireAgain);

            Dts.TaskResult = (int)ScriptResults.Success;

And here's the proof

Interested in playing? LogVersion.dtsx is available on my google site

No comments: