/// <summary>
/// Constructor (creating an SCD table)
/// </summary>
/// <param name="properties">SSIS components properites</param>
/// <param name="columnConfigList">Column config list</param>
/// <param name="con">Sql connection</param>
public frmCreateTable(IsagCustomProperties properties, BindingList <ColumnConfig> columnConfigList, SqlConnection con)
{
InitializeComponent();
btnOk.Enabled = false;
this.Text = "Create SCD Table";
SCDList scdList = new SCDList(columnConfigList, properties.DestinationTable);
tbSql.Text = scdList.GetCreateScdTables();
_con = con;
}
/// <summary>
/// Checks if SCD configuration is correct and returns Isag events if problems are found
/// </summary>
/// <param name="events">Isag events</param>
private void WarnIfScdIsNotValid(IsagEvents events)
{
SCDList scdList = new SCDList(ColumnConfigList, DestinationTable);
string message = "";
if (!scdList.IsValid(ref message))
{
events.Fire(IsagEvents.IsagEventType.Warning, message);
}
bool isValid = true;
foreach (ColumnConfig config in ColumnConfigList)
{
if (config.IsScdColumn && config.IsScdValidFrom)
{
isValid = false;
}
}
if (!isValid)
{
events.Fire(IsagEvents.IsagEventType.Warning, @"You have to choose ""SCD Column"" OR ""SCD ValidFrom"" for one column but not both!");
}
isValid = true;
foreach (ColumnConfig config in ColumnConfigList)
{
if (string.IsNullOrEmpty(config.ScdTable) && (config.IsScdColumn || config.IsScdValidFrom))
{
isValid = false;
}
}
if (!isValid)
{
events.Fire(IsagEvents.IsagEventType.Warning, @"If choosing ""SCD Column"" or ""SCD ValidFrom"" you also have to fill out ""SCD Table"".");
}
isValid = true;
foreach (ColumnConfig config in ColumnConfigList)
{
if (!string.IsNullOrEmpty(config.ScdTable) && !config.IsScdColumn && !config.IsScdValidFrom)
{
isValid = false;
}
}
if (!isValid)
{
events.Fire(IsagEvents.IsagEventType.Warning, @"If filling out ""SCD Table"" you have to choose ""SCD Column"" or ""SCD ValidFrom"".");
}
}
/// <summary>
/// Creates SQL merge command (only SQL Server 2008 and above)
/// </summary>
/// <param name="properties">componets custom properties</param>
/// <param name="tempTableName">temporary table name</param>
/// <param name="overrideCustomMergeCommand">If true, only custom merge template is returned (used for preview) </param>
/// <returns>sql merge command</returns>
public static string GetSqlMerge(IsagCustomProperties properties, string tempTableName, bool overrideCustomMergeCommand)
{
if (properties.UseCustomMergeCommand && !overrideCustomMergeCommand)
{
return(properties.CustomMergeCommand);
}
else
{
string destTable = properties.DestinationTable;
string tempTable = tempTableName;
string result = "merge ";
string sqlInto = "into " + Brackets(destTable); // +" with (tablockx) as dest ";
if (!properties.DisableTablock)
{
sqlInto += " with (tablockx)";
}
sqlInto += " as dest ";
string sqlUsing = "using " + Brackets(tempTable) + " as src ";
string sqlOn = "";
string sqlUpdate = "";
string sqlInsert = "";
string sqlValues = "";
foreach (ColumnConfig config in properties.ColumnConfigList)
{
//Update
if (config.Update)
{
if (sqlUpdate != "")
{
sqlUpdate += ", ";
}
sqlUpdate += "dest." + Brackets(config.OutputColumnName) + " = ";
if (config.HasFunction)
{
sqlUpdate += config.Function + " ";
}
else
{
sqlUpdate += "src." + Brackets(config.BulkColumnName) + " ";
}
}
//Insert
if (config.Insert)
{
if (sqlInsert != "")
{
sqlInsert += ", ";
}
if (sqlValues != "")
{
sqlValues += ", ";
}
sqlInsert += Brackets(config.OutputColumnName);
//Default Values for Insert: isnull(<columnname>, <defaultValue>)
sqlValues += config.GetColumnExpression();
}
//Join
if (config.Key)
{
if (sqlOn != "")
{
sqlOn += " and ";
}
sqlOn += config.GetColumnExpression() + " = " + "dest." + Brackets(config.OutputColumnName) + " "; //"src." + Brackets(config.BulkColumnName)
}
}
sqlOn = "ON " + sqlOn;
if (sqlUpdate != "")
{
sqlUpdate = "WHEN MATCHED THEN UPDATE SET " + sqlUpdate;
}
if (sqlInsert != "")
{
sqlInsert = "WHEN NOT MATCHED BY TARGET THEN INSERT (" + sqlInsert + ") ";
sqlValues = "VALUES (" + sqlValues + ")";
}
sqlValues += ";";
result += sqlInto + Environment.NewLine + sqlUsing + Environment.NewLine + sqlOn + Environment.NewLine +
sqlUpdate + Environment.NewLine + sqlInsert + Environment.NewLine + sqlValues;
result = ReplacePlaceHolderInputColumn(result, "src.");
if (properties.HasScd)
{
SCDList scd = new SCDList(properties.ColumnConfigList, properties.DestinationTable);
result = scd.InsertIntoMergeStatement(result, properties, tempTableName.Replace("#", "SCD_"), properties.EnableIndexOnSCD);
}
return(result);
}
}
