Database references for system objects

A database project configured in Visual Studio will have database references to external and internal databases it requires. While I would strongly advise against crossing database boundaries using this mechanism, there are legacy systems that require this. Part of bringing those legacy databases into standard development and deployment patterns means setting up database projects and publish profiles.

For your own databases that is a trivial task, but what do you do when using the system metdata. Examples of these areĀ sys.objects, sys. tables and more. These objects raise errors in the pattern “Object contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects”. Fortunately these are easy to resolve. Continue reading →

Derived Columns in BIML

Today I was working with the Derived Column BIML transformation. The larger task is a CDC based load pattern from a metadata framework database using BIML to generate the packages. Because I need this to be applicable to clients of any size, I’m trying to avoid the cost of the richer set of tools. Here in New Zealand it is not as easy to justify the price tag for an engagement that may only last a week or two.

This does mean there is a certain amount of hand-crafting happening here. But that is okay, because when your hands are covered in bits and bytes as you’re sweating over code you’re building that foundational knowledge and understanding of the layers. As a result you’re able to to pick up any problems long before they occur.

The example BIML snippet for today deals with Derived Columns, which is a fairly common task in any SSIS package. Continue reading →

What’s in your toolkit?

I was lucky enough to visit the Data Insights Conference in Seattle earlier this year and the hunger for data and love thereof was apparent from all the speakers and how they engaged with their subjects. There were a few standouts, but one of the concepts that resonated with me personally was deprecating the boring, static, repetitive bits and to focus on where we really add value. This is valuable advice for any profession, but what does it mean for those of us that roll up our sleeves and dig into data all day long? Continue reading →

Auto fill in Excel

A good tool is one that is not only unobtrusive, but useful. Today, while working on documenting test cases I found that Excel once again surprised me with a hidden gem. While typing repetitive lists that differ only slightly it appears to have picked up the pattern and suggested in dimmed text what the rest of the table could be populated with. Pressing Enter at that point populated the table and perfectly so.

This is Excel 2016 from my Office 365 subscription. To me, this is a good tool that supports the work I’m doing. It’s something to keep in mind when building our own tools. Ensure they offer that background support to the tasks that users do most often. That’s when they add value. For more information on similar functionality have a read here.