// TODO: Is this the right approach for events and precedence constraints? Should we have a utility method to handle them?
public static void ProcessMerge(AstMergeTaskNode mergeNode)
{
var executeSqlNode = new AstExecuteSqlTaskNode(mergeNode.ParentItem)
{
Name = mergeNode.Name,
ExecuteDuringDesignTime = false,
Connection = ((AstTableNode)mergeNode.TargetConstraint.ParentItem).Connection,
ResultSet = ExecuteSqlResultSet.None,
DelayValidation = mergeNode.DelayValidation,
IsolationLevel = mergeNode.IsolationLevel
};
executeSqlNode.Query = new AstExecuteSqlQueryNode(executeSqlNode) { QueryType = QueryType.Standard, Body = new MergeTSqlEmitter(mergeNode).Emit() };
executeSqlNode.PrecedenceConstraints = mergeNode.PrecedenceConstraints;
if (executeSqlNode.PrecedenceConstraints != null)
{
executeSqlNode.PrecedenceConstraints.ParentItem = executeSqlNode;
}
foreach (var eventHandler in mergeNode.Events)
{
executeSqlNode.Events.Add(eventHandler);
eventHandler.ParentItem = executeSqlNode;
}
var parentContainer = mergeNode.ParentItem as AstContainerTaskNode;
if (parentContainer != null)
{
parentContainer.Tasks.Replace(mergeNode, executeSqlNode);
}
}
private static AstMergeTaskNode CreateMergeTask(AstTableNode table, AstStagingContainerTaskNode staging, AstTableCloneNode stagingCloneTable)
{
var merge = new AstMergeTaskNode(staging)
{
Name = Utility.NameCleanerAndUniqifier(table + "_SSMerge"),
SourceTable = stagingCloneTable,
TargetConstraint = stagingCloneTable.Table.PreferredKey,
UnspecifiedColumnDefaultUsageType = MergeColumnUsage.CompareUpdateInsert
};
// Detect identity keys, if so add an insert- merge-column-usage for said key
foreach (var columnRef in stagingCloneTable.Table.PreferredKey.Columns)
{
if (columnRef.Column.IsIdentityColumn)
{
merge.Columns.Add(new AstMergeColumnNode(merge) { ColumnName = columnRef.Column.Name, ColumnUsage = MergeColumnUsage.Insert });
}
}
return merge;
}
public MergeTSqlEmitter(AST.Task.AstMergeTaskNode astNode)
{
_mergeTask = astNode;
}
public MergeTSqlEmitter(AST.Task.AstMergeTaskNode astNode)
{
_mergeTask = astNode;
}
private AstIR ProcessTableQuerySources(AstIR astIR)
{
List<AstTableNode> tables = new List<AstTableNode>();
tables.AddRange(astIR.AstRootNode.Dimensions.Cast<AstTableNode>());
tables.AddRange(astIR.AstRootNode.Facts.Cast<AstTableNode>());
tables.AddRange(astIR.AstRootNode.Tables);
foreach (AstTableNode table in tables)
{
foreach (AstTableQuerySourceNode querySource in table.Sources.OfType<AstTableQuerySourceNode>())
{
AstPackageNode package = new AstPackageNode();
package.ConstraintMode = ContainerConstraintMode.Linear;
package.DefaultPlatform = PlatformType.SSIS08;
package.Log = false;
package.Name = querySource.Name;
package.Type = "ETL";
AstStagingContainerTaskNode staging = new AstStagingContainerTaskNode();
staging.ConstraintMode = ContainerConstraintMode.Linear;
staging.Log = false;
staging.Name = querySource.Name;
staging.CreateAs = String.Format("__Staging_{0}_{1}", table.Name, querySource.Name);
staging.StagingConnection = table.Connection;
staging.Table = table;
AstETLRootNode etl = new AstETLRootNode();
etl.Name = String.Format("__ETL_Staging_{0}_{1}", table.Name, querySource.Name);
etl.DelayValidation = true;
AstQuerySourceNode source = new AstQuerySourceNode();
source.Connection = querySource.Connection;
source.Name = String.Format("__ETL_Staging_Source_{0}_{1}", table.Name, querySource.Name);
source.Query = querySource.Query;
etl.Transformations.Add(source);
AstDestinationNode destination = new AstDestinationNode();
destination.AccessMode = DestinationAccessModeFacet.TableFastLoad;
destination.CheckConstraints = true;
destination.TableLock = true;
destination.Connection = table.Connection;
destination.Name = String.Format("__ETL_Staging_Destination_{0}_{1}", table.Name, querySource.Name);
destination.TableName = staging.CreateAs;
destination.ValidateExternalMetadata = false;
etl.Transformations.Add(destination);
staging.Tasks.Add(etl);
AstMergeTaskNode merge = new AstMergeTaskNode();
merge.Connection = table.Connection;
merge.Name = String.Format("__Staging_Merge_{0}_{1}", table.Name, querySource.Name);
merge.SourceName = staging.CreateAs;
merge.TargetConstraint = table.PreferredKey;
staging.Tasks.Add(merge);
package.Tasks.Add(staging);
astIR.AstRootNode.Packages.Add(package);
}
}
return astIR;
}
public string Emit(AstMergeTaskNode mergeTask)
{
StringBuilder joinBuilder = new StringBuilder();
for (int i = 0; i < mergeTask.TargetConstraint.Columns.Count; i++)
{
joinBuilder.AppendFormat("TARGET.{0} = SOURCE.{0}", mergeTask.TargetConstraint.Columns[i].Column.Name);
if (i < mergeTask.TargetConstraint.Columns.Count - 1)
{
joinBuilder.AppendFormat("\nAND\n");
}
}
StringBuilder notEqualBuilder = new StringBuilder();
StringBuilder updateBuilder = new StringBuilder();
StringBuilder insertParamBuilder = new StringBuilder();
StringBuilder insertValueBuilder = new StringBuilder();
AstTableNode targetTable = AstWalker.FirstParent<AstTableNode>(mergeTask.TargetConstraint);
Hashtable columnUsageMapping = new Hashtable();
foreach (AstTableColumnBaseNode column in targetTable.Columns)
{
if (column.IsAssignable)
{
foreach (AstMergeColumnNode mergeColumn in mergeTask.Columns)
{
if (mergeColumn.ColumnName.ToUpperInvariant() == column.Name.ToUpperInvariant())
{
columnUsageMapping.Add(column.Name, mergeColumn.ColumnUsage);
break;
}
}
if (!columnUsageMapping.ContainsKey(column.Name))
{
columnUsageMapping.Add(column.Name, mergeTask.UnspecifiedColumnDefaultUsageType);
}
}
}
bool firstNotEqual = true;
bool firstUpdate = true;
bool firstInsert = true;
foreach (AstTableColumnBaseNode column in targetTable.Columns)
{
if (column.IsAssignable)
{
if (columnUsageMapping[column.Name].ToString().ToUpperInvariant().Contains("COMPARE"))
{
if (firstNotEqual)
{
firstNotEqual = false;
}
else
{
notEqualBuilder.AppendFormat("\nOR\n");
}
// Bug #3757, special handling for uniqueidentifier data type
if (column.CustomType != null && column.CustomType.ToLowerInvariant().CompareTo("uniqueidentifier") == 0)
{
notEqualBuilder.AppendFormat("COALESCE(TARGET.[{0}],CONVERT(uniqueidentifier,'00000000-0000-0000-0000-000000000000')) <> COALESCE(SOURCE.[{0}],CONVERT(uniqueidentifier,'00000000-0000-0000-0000-000000000000'))", column.Name);
}
else
{
notEqualBuilder.AppendFormat("COALESCE(TARGET.[{0}],'') <> COALESCE(SOURCE.[{0}],'')", column.Name);
}
}
if (columnUsageMapping[column.Name].ToString().ToUpperInvariant().Contains("UPDATE"))
{
if (firstUpdate)
{
firstUpdate = false;
}
else
{
updateBuilder.AppendFormat(",");
}
updateBuilder.AppendFormat("TARGET.[{0}] = SOURCE.[{0}]", column.Name);
}
if (firstInsert)
{
firstInsert = false;
}
else
{
insertParamBuilder.AppendFormat(",\n");
insertValueBuilder.AppendFormat(",\n");
}
insertParamBuilder.AppendFormat("[{0}]", column.Name);
insertValueBuilder.AppendFormat("SOURCE.[{0}]", column.Name);
}
}
TemplatePlatformEmitter te;
if (mergeTask.UpdateTargetTable)
{
te = new TemplatePlatformEmitter("Merge", mergeTask.SourceName, targetTable.Name, joinBuilder.ToString(), notEqualBuilder.ToString(), updateBuilder.ToString(), insertParamBuilder.ToString(), insertValueBuilder.ToString());
}
else
{
te = new TemplatePlatformEmitter("MergeWithoutUpdate", mergeTask.SourceName, targetTable.Name, joinBuilder.ToString(), insertParamBuilder.ToString(), insertValueBuilder.ToString());
}
return te.Emit(null);
/*
<Template Name="Merge">
<Map Source="Source" Index="0"/>
<Map Source="Target" Index="1"/>
<Map Source="Join" Index="2"/>
<Map Source="NotEqualCheck" Index="3"/>
<Map Source="Update" Index="4"/>
<Map Source="Insert" Index="5"/>
<TemplateData>
*/
}
