Category / Development

by Pascal Janse van Vuuren

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 →

by Pascal Janse van Vuuren

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 →