Note: All examples in this article are made using the Stack overflow 2013 database, SQL Server 2019, Visual Studio 2019, SQL Server Integration Services projects extension version 3.4.Īs mentioned in the SSIS toolbox, SSIS Merge transformation “combines rows from multiple sorted data flows into one sorted data flow”.įigure 1 – Merge Transformation description in the SSIS toolbox.
This article is the fourteenth of the SSIS features face-to-face series that aims to compare similar SSIS components in order to remove any confusion for the data integration developers that use this technology.
This article explains both transformations and illustrates when and how to use each one of them. The format is always -.SSIS Merge Join and Merge transformations are two similar components used to combine two input data sources into one output. Instead of using integer/identity fields or GUIDs for ID's, we use strings, and something similar to the Hi-Lo algorithm.
We can then perform queries that join against this table, but application code can still load/save objects as a single JSON document. EventRelatedDocument (RelatedDocumentId, EventId)Ĭross apply (RelatedDocumentIds) EventRelatedDocument where EventId in (SELECT Id from INSERTED) Each time the row is written, we split the values, and use the MERGE statement to keep a join table up to date: ALTER TRIGGER.
We know it results in a full table scan, but these queries are rare, the tables are small, and it's fast enough for us.įor tables that grow into the millions of records (audit events, where each event references many related documents), we use a trigger. For tables that we'd expect to only get a few dozen, no more than a thousand results, we can simply query them using LIKE '%|' + + '|%'. When we do need to query against a relationship, for one-to-many relationship, the property is stored in a single column as you'd expect:įor a many-to-many, we do something a bit naughty: we store the values with | characters between each value:
For relationships that we don't query against, the property or array is simply serialized in JSON. We model relationships in different ways. We use a custom JSON.NET Contract Resolver to ensure that properties that are already saved in other columns of the table aren't duplicated in the JSON. The JSON column contains all of the other properties of the document, including any nested objects. We often need to perform queries like, "what deployments belong to this release", or "what is the most recent deployment for this project/environment", so these are fields on the table: Avoid the need for joins as much as possibleĮach type of entity/aggregate root that we deal with (Machines, Environments, Releases, Tasks) gets its own table, which follow a similar pattern:įor example, here's the Deployment table.Plain old ACID, no eventual consistency to worry about.Load/save entire aggregate roots into a single table.Even though we decided to switch to SQL Server, we wanted to keep many of the aspects that we had grown used to when using a document store.
When using Raven, we had found that the document database model made it so much easier to work with our data than we ever could have with a relational model. Octopus 3.x has been in production for a while now, so in this post, I want to provide an update, and go into some detail about how we use SQL Server as a document store. We followed it with a post on how we planned to use SQL Server. That post explained the problems we had and the reasons we decided to switch. 18 months ago I blogged about how we decided to switch from RavenDB to SQL Server in Octopus 3.0.