public static IEnumerable<AstPackageNode> ProcessTableStaticSource(AstTableNode astTableNode)
{
var packageList = new List<AstPackageNode>();
foreach (var source in astTableNode.Sources)
{
var staticSource = source as AstTableStaticSourceNode;
if (staticSource != null && staticSource.Rows.Count > 0)
{
var table = staticSource.ParentItem as AstTableNode;
if (table != null && staticSource.EmitMergePackage)
{
if (table.PreferredKey == null)
{
MessageEngine.Trace(table, Severity.Error, "L0110", "Table {0} does not contain a primary key and therefore cannot use Static Source Merging. Add a primary key or set EmitMergePackage to false.", table.Name);
}
var package = new AstPackageNode(table.ParentItem)
{
Emit = table.Emit,
Log = false,
Name = staticSource.Name,
PackageFolderSubpath = table.Name,
PackageType = GetPackageType(table)
};
// Staging Container
var staging = new AstStagingContainerTaskNode(package)
{
Log = false,
Name = Utility.NameCleanerAndUniqifier(source.Name + "_Stg"),
};
var stagingCloneTable = new AstTableCloneNode(staging)
{
Name =
String.Format(CultureInfo.InvariantCulture, "__{0}_Static", table.Name),
Connection = table.Connection,
Table = table
};
staging.Tables.Add(stagingCloneTable);
CloneTableLowerer.LowerCloneTable(stagingCloneTable);
staging.Tasks.Add(CreateInsertExecuteSql(staticSource, staging, stagingCloneTable));
staging.Tasks.Add(CreateMergeTask(table, staging, stagingCloneTable));
package.Tasks.Add(staging);
packageList.Add(package);
staticSource.LoweredPackage = package;
}
}
}
return packageList;
}
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;
}
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;
}
