Sadly there hasn’t been much love shown to Data Quality Services (DQS) in the last few releases of SQL Server, and I don’t think there will be in the coming SQL Server vNext release.

Sometimes developing a solution from the clients database(s) is like putting together a jigsaw, which is picture side down, while in a pitch black room, wearing a blindfold and boxing gloves.

Right not a problem, I just set up an SSIS For Each loop container click on ‘Traverse subfolders’ option and then set out the data connections.

No, sadly the ‘Traverse sub folder doesn’t work like that. Nuts, so after a quick search I found this link at Joost van Rossum’s blog that uses a bit of C# to get the folder list and generate an XML schema with the folder list in it. You can then use the ‘Foreach NodeList Enumerator’ in the Foreach Loop to get the files.

Well sort of, the code on that website only gets the folder structure, not the full path of the file. It was however a good starting point, and looked like it could be adapted to get the full file list, that could be passed on to the data flow logic in the Foreach Container. Now I’m TSQL through and through, my C# is poor, but slowly getting better, however I did mange to hack the code so it got the file list. So here it is, please use, improve and share.

#region Namespaces
using System;
using System.Data;
using System.IO;
using System.Xml;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Collections.Generic;
#endregion

namespace ST_b87894259c434eeca3da339009a06fdf
{
    /// 

    /// ScriptMain is the entry point class of the script.  Do not change the name, attributes,
    /// or parent of this class.
    /// 

 
 // Use this for SQL Server 2012 and above
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
 // Use the below for SQL Server 2008, comment out the above
 //  [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

        // Variables for the xml string
        private XmlDocument xmldoc;
        private XmlElement xmlRootElem;

        public void Main()
        {
            // Inialize XMLdoc
            xmldoc = new XmlDocument();

            // Add the root element: 
            xmlRootElem = xmldoc.CreateElement("", "ROOT", "");

            // Add Subfolders as Child elements to the root element
            GetSubFolders(Dts.Variables["User::FeedsFilePath"].Value.ToString());

            // Add root element to XMLdoc
            xmldoc.AppendChild(xmlRootElem);

            // Fill SSIS variable with XMLdoc
            Dts.Variables["xmldoc"].Value = xmldoc.InnerXml.ToString();

            Dts.TaskResult = (int)ScriptResults.Success;
        }

        // Recursive method that loops through subfolders
        private void GetSubFolders(String parentFolder)
        {
            // Get subfolders of the parent folder
            string[] subFolders = Directory.GetDirectories(parentFolder);

            var allfiles = DirSearch(parentFolder);

            foreach (var filePath in allfiles)
            {
                XmlElement xmlChildElem;
                XmlText xmltext;

              //  var directoryInfo = new DirectoryInfo(Path.GetDirectoryName(filePath));

                // Create child element "Folder":
                // d:\foreachfoldertest\subfolder1\
                xmlChildElem = xmldoc.CreateElement("", "File", "");
                xmltext = xmldoc.CreateTextNode(filePath);
                xmlChildElem.AppendChild(xmltext);

                // Add child element to root element
                xmlRootElem.AppendChild(xmlChildElem);
            }
   
        }
  // This bit gets the file list and adds it to the subfolders
        private List DirSearch(string sDir)
        {
            List files = new List();
          
                foreach (string f in Directory.GetFiles(sDir))
                {
                    files.Add(f);
                }
                foreach (string d in Directory.GetDirectories(sDir))
                {
                    files.AddRange(DirSearch(d));
                }
            return files;
        }
    }
}

I had a few days between projects, so spent the time getting a bit more familiar with Power Query. I have used crime data to animate a Thermal Map in Power Maps before Here. But I was playing around with Azure Data Market to access the freely available data sets, one of which was the UK Met office weather data to look at animating weather using Power Map. Then i had a bit of a brain wave, I had two data sets, that matched time and space and had a look to see if the weather affected crime. The above graph indicates that the number of Anti Social Behaviour incidents follows the temperature. This isn’t a deep statistical analysis, just a quick eyeball of the flow of the graphs but it is suggestive that something is going on for 2012 at least.

Ten years have now gone by, I can tell the story of how my IT Skills may have made people lose their jobs.

It’s about 2006/07 and I was working as a Customer Service Team Engineering Gatekeeper at what was then Finning Materials Handling. Basically it was a fancy job title for controlling the field engineer’s assigned forklifts and other equipment. The details got updated, I ran reports and generally tried to chat up temps, and anything with a XX chromosome really. Despite being in such a target rich environment I got a batting average of zero.
Not one.
Nothing.
Nada.
I also sat next to the most excellent Steve Rowlands, who together we had chats and great bant’s.
Anyway, I should stick to the point, as we had acquired another forklift company called Lex Harvey the previous year, we were running two IT systems. One for the Caterpillar equipment (Finning stuff) and one for the Lex Harvey kit (All sorts of tat). What the issue was is that they were separate systems, no communication between the two so it required a bit of a brain melting issue to log even a breakdown for a customer.
Reporting from it was a right pain as it normally took about a day and a half to produce stuff, however I had a few tricks up my sleeve, mainly to automate the process with use of a VBA type script that could record me doing the reports once. Then all I had to do was a search and replace on the dates every week to run the reports. The process then took me about 2 mins, I just left the script to run on both systems, and boom, leaving me more time to check out the temps, ‘Hi how you doing, would you like a coffee? No it’s no hassle, I like my women how I like my coffee… thrown over me’
So one of the days I had nothing much to do, and realised that I could work out the total equipment numbers of all the teams in the UK, and do some analytics around it. Total numbers, ratio of engineers to forklifts stuff like that.
So I exported all the data into Excel 2003 (Feels like old school stuff) and quickly built up some numbers, and sent it on to the Service Area Managers. It went down a treat. The two from the north, whose names I forget but one of the guys looked like the ‘Cigarette smoking man’ out of the X-Files, went nuts over it. The North West one had done something similar, the North East guy (Mulder knows too much) had also done something similar, but one for the Finning side and one for the Lex side. So kudos all round for Mr Lunn for displaying initiative, technical skill and being awesome. I’d been promoted 3 times in 3 years, maybe this would be the path to the next one. We all got together and sorted out a few issues with the numbers, there were a few items no on the systems which one of our customer owned but we sorted out the servicing.
They then presented it to some other people, then tried to take the credit. It didn’t work…. Hahahaha fuckers!
Any hoo… the report got kicked up to higher management, then even higher management, then to the Chiefs, COO, CIO, CFO, and the big man, the CEO.
They loved it, they went nuts for it, I had some senior people come up to me, and say stuff like ‘Great piece of work Jon’, pats on the back and handshakes all round.
It then got serious.
Really fucking serious.
They started asking questions about the fleet numbers, detailed reports on it, and how I came to those numbers. Sure OK, here’s how I did it, showed them my working out, more comments like ‘It’s a great driver for the business’ and ‘It’s really important that the number reflect the reality on the ground’. So I showed them everything. They all nodded and said stuff like ‘Great, we’re very happy with the numbers now’.
Behind the scenes some meetings started going on, serious meetings, with serious questions, about serious decisions.
They dropped the bombshell, they were cutting back the number of engineers in the field and making redundancies.
Shit.
Fuck.
Shit fuck.
I felt like a right massive C word. I’d worked running engineers in the south, the midlands, but mostly the north east. They liked me, I was chatty, funny and I used to drive a forklift so knew what drivers did to piss them off when it comes to repairing them. So when they all come down for the meetings and HR stuff, they came and saw me, handed me their paperwork, I shook their hands said the usual platitudes.
Felt like an even more super massive C.
Anyway, it turned out I wasn’t the trigger for it, little did I know is that Finning were in talks with another company called Briggs, which would then purchase the forklift division, and I understand is that Finning wanted to make the company a bit leaner and improve the books. I, however, to some degree had helped.
So the company got took over, I got promoted to IT and Technical Analyst – Business Intelligence. However slightly more careful about what reports I ran.