/// <summary> /// Create SQL statement for creating temporary table (if merge command is used, an index is created) /// </summary> /// <returns>sql create temporary table command</returns> public static string GetCreateTempTable(IsagCustomProperties properties, string tempTableName) { string returnValue = ""; string indexColumns = ""; foreach (ColumnConfig config in properties.BulkCopyColumnConfigLIst) { if (returnValue != "") { returnValue += ", "; } returnValue += Brackets(config.BulkColumnName) + " "; returnValue += config.BulkDataType + Environment.NewLine; if (properties.UseMerge && config.Key) { if (indexColumns != "") { indexColumns += ", "; } indexColumns += Brackets(config.BulkColumnName) + " ASC"; } } returnValue = "CREATE TABLE " + tempTableName + Environment.NewLine + "( " + returnValue + " )"; if (properties.UseMerge) { returnValue += ";" + Environment.NewLine + Constants.CREATE_INDEX.Replace("<table>", tempTableName).Replace("<columns>", indexColumns); } return(returnValue); }
/// <summary> /// Create SQL statement for creating destination table /// </summary> /// <param name="properties">componets custom properties</param> /// <returns>sql statement for creating destination table</returns> public static string GetCreateDestinationTable(IsagCustomProperties properties) { StringBuilder result = new StringBuilder("CREATE TABLE [TableLoader Destination] (" + Environment.NewLine); bool appendComma = false; foreach (ColumnConfig config in properties.ColumnConfigList) { if (config.HasInput) { if (appendComma) { result.Append("," + Environment.NewLine); } appendComma = true; result.Append(" "); result.Append(Brackets(config.InputColumnName)); result.Append(" "); result.Append(config.DataTypeInput); } } result.Append(Environment.NewLine); result.Append(")"); return(result.ToString()); }
/// <summary> /// Apply standard configuration to custom properties /// </summary> /// <param name="isagCustomProperties">component custom porperties</param> public void SetStandardConfiguration(ref IsagCustomProperties isagCustomProperties) { if (!isagCustomProperties.AutoUpdateStandardConfiguration) { return; } if (!HasConnection) { throw new Exception("The Connection Manager for the Standard Configuration is missing."); } else { DataTable dt = GetStandardConfigurationAsDataTable(); if (dt.Rows.Count == 0) { throw new Exception("The Configuration Table is empty or the Database connection is not valid."); } Dictionary <string, DataRow> cfgList = GetStandardConfigurationAsDictionary(); if (!cfgList.ContainsKey(isagCustomProperties.StandarConfiguration)) { throw new Exception("The Standard Configuration \"" + isagCustomProperties.StandarConfiguration + "\" could not be found in the configuration table."); } SetStandardConfiguration(ref isagCustomProperties, cfgList[isagCustomProperties.StandarConfiguration]); } }
/// <summary> /// Create SQL statement for altering destination table /// </summary> /// <param name="properties">componets custom properties</param> /// <param name="tempTableName">temporary table name</param> /// <returns>sql statement for altering destination table</returns> public static string GetAlterDestinationTable(IsagCustomProperties properties, SqlColumnList sqlColumns) { string result = ""; if (properties.HasDestinationTable) { foreach (ColumnConfig config in properties.ColumnConfigList) { string outputColumnName = sqlColumns.GetMatchingColumnname(config.InputColumnName, properties.PrefixInput, properties.PrefixOutput); if (config.HasInput && outputColumnName == "") { if (result == "") { result += " ADD "; } else { result += "," + Environment.NewLine + " "; } result += Brackets(config.InputColumnName) + " " + config.DataTypeInput; } } } return("ALTER TABLE " + Brackets(properties.DestinationTable) + Environment.NewLine + result); }
/// <summary> /// Apply standard configuration to custom properties /// </summary> /// <param name="isagCustomProperties">component custom porperties</param> /// <param name="row">data row</param> public void SetStandardConfiguration(ref IsagCustomProperties isagCustomProperties, DataRow row) { try { isagCustomProperties.ChunckSizeBulk = (long)row["ChunkSizeBulk"]; isagCustomProperties.ChunkSizeDbCommand = (long)row["ChunkSizeDbCommand"]; isagCustomProperties.TimeOutDb = (int)row["DbTimeout"]; isagCustomProperties.MaxThreadCount = (long)row["MaxThreadCount"]; isagCustomProperties.PrefixInput = row["PreFixInput"].ToString(); isagCustomProperties.PrefixOutput = row["PreFixOutput"].ToString(); IsagCustomProperties.TableLoaderType tableLoaderType = (IsagCustomProperties.TableLoaderType)Enum.Parse(typeof(IsagCustomProperties.TableLoaderType), row["TableLoaderType"].ToString()); IsagCustomProperties.DbCommandType dbCommand = (IsagCustomProperties.DbCommandType)Enum.Parse(typeof(IsagCustomProperties.DbCommandType), row["DbCommand"].ToString()); IsagCustomProperties.TransactionType transaction = (IsagCustomProperties.TransactionType)Enum.Parse(typeof(IsagCustomProperties.TransactionType), row["TransactionType"].ToString()); isagCustomProperties.TlType = tableLoaderType; isagCustomProperties.DbCommand = dbCommand; isagCustomProperties.Transaction = transaction; } catch (Exception ex) { throw new Exception(ex.Message); } }
/// <summary> /// constructor /// </summary> /// <param name="isagCustomProperties">custom properties for the component</param> /// <param name="events">Isag events</param> /// <param name="componentMetaData">SSIS metadata for the component</param> /// <param name="variableDispenser">SSIS variable dispenser</param> public TlDbCommand(IsagCustomProperties isagCustomProperties, IsagEvents events, IDTSComponentMetaData100 componentMetaData, IDTSVariableDispenser100 variableDispenser) { _IsagCustomProperties = isagCustomProperties; _events = events; _componentMetaData = componentMetaData; _variableDispenser = variableDispenser; }
/// <summary> /// Constructor (altering a table) /// </summary> /// <param name="properties">SSIS components properites</param> /// <param name="sqlColumns">Sql column list</param> /// <param name="con">Sql connection</param> public frmCreateTable(IsagCustomProperties properties, SqlColumnList sqlColumns, SqlConnection con) { InitializeComponent(); this.Text = "Alter Table"; tbSql.Text = SqlCreator.GetAlterDestinationTable(properties, sqlColumns); _con = con; }
/// <summary> /// Constructor (creating a table) /// </summary> /// <param name="properties">SSIS components properites</param> /// <param name="con">Sql connection</param> public frmCreateTable(IsagCustomProperties properties, SqlConnection con) { InitializeComponent(); this.Text = "Create Table"; tbSql.Text = SqlCreator.GetCreateDestinationTable(properties); _con = con; }
/// <summary> /// load this properties from an xml string /// </summary> /// <param name="xml">xml string</param> /// <returns>instance of IsagCustomProperties</returns> public static IsagCustomProperties LoadFromXml(string xml) { XmlSerializer serializer = new XmlSerializer(typeof(IsagCustomProperties)); StringReader reader = new StringReader(xml); IsagCustomProperties result = (IsagCustomProperties)serializer.Deserialize(reader); return(result); }
/// <summary> /// constructor /// </summary> /// <param name="events">Isag events</param> /// <param name="conn">Main sql connection</param> /// <param name="isagCustomProperties">components custom properties</param> /// <param name="dbCommand">Database command type</param> /// <param name="bulkConn">Bulk csql connection</param> /// <param name="componentMetaData">SSIS component metadata</param> public TxAll(IsagEvents events, SqlConnection conn, IsagCustomProperties isagCustomProperties, TlDbCommand dbCommand, SqlConnection bulkConn, IDTSComponentMetaData100 componentMetaData) { _events = events; _conn = conn; _IsagCustomProperties = isagCustomProperties; _dbCommand = dbCommand; _bulkConn = bulkConn; _componentMetaData = componentMetaData; }
/// <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> /// Creates SQL Update command /// <param name="properties">componets custom properties</param> /// <param name="tempTableName">temporary table name</param> /// <returns>sql update command</returns> public static string GetSqlUpdate(IsagCustomProperties properties, string tempTableName) { string destTable = Brackets(properties.DestinationTable); string tempTable = Brackets(tempTableName); string result = "update " + destTable; if (!properties.DisableTablock) { result += " WITH (tablockx)"; } result += Environment.NewLine + "set "; string sqlSet = ""; string sqlfrom = "from " + tempTable; string sqlWhere = ""; foreach (ColumnConfig config in properties.ColumnConfigList) { if (config.Update) { if (sqlSet != "") { sqlSet += "," + Environment.NewLine + " "; } sqlSet += destTable + "." + Brackets(config.OutputColumnName) + "="; if (config.HasFunction) { sqlSet += config.Function + " "; } else { sqlSet += tempTable + "." + Brackets(config.BulkColumnName); } } //create where clause if (config.Key) { if (sqlWhere != "") { sqlWhere += " and "; } sqlWhere += tempTable + "." + Brackets(config.BulkColumnName) + "=" + destTable + "." + Brackets(config.OutputColumnName); } } result += sqlSet + Environment.NewLine + sqlfrom + Environment.NewLine + "where " + sqlWhere; return(ReplacePlaceHolderInputColumn(result, tempTable + ".")); }
/// <summary> /// Initializes custom properties /// </summary> /// <param name="needsStandardConfiguration">Is standard configuration needed?</param> private void InitProperties(bool needsStandardConfiguration) { try { _IsagCustomProperties = IsagCustomProperties.Load(ComponentMetaData, needsStandardConfiguration); } catch (Exception ex) { _events.FireError(new string[] { "InitProperties", "Load", ex.Message }); } _events = new IsagEvents(ComponentMetaData, VariableDispenser, _IsagCustomProperties.DestinationTable, _IsagCustomProperties.CustumLoggingTemplate, _IsagCustomProperties.LogLevel); Logging.Events = _events; }
/// <summary> /// constructor /// </summary> /// <param name="input">SSIS input</param> /// <param name="isagCustomProperties">Components custom properties</param> /// <param name="cstr">Conectionststring</param> /// <param name="conn">Sql connection</param> /// <param name="events">Isag events</param> /// <param name="dbCommandEventType">Database command event type</param> /// <param name="dbCommandTemplate">Database command template</param> /// <param name="status"></param> public ThreadHandler(IDTSInput100 input, IsagCustomProperties isagCustomProperties, string cstr, SqlConnection conn, IsagEvents events, IsagEvents.IsagEventType dbCommandEventType, string[] dbCommandTemplate, Status status) { _conn = conn; if (dbCommandTemplate != null) { _dbCmdThread = new ThreadDbCommand(conn, dbCommandEventType, isagCustomProperties.TimeOutDb, isagCustomProperties.Reattempts, dbCommandTemplate); } _maxAllowdThreads = isagCustomProperties.MaxThreadCount; _input = input; _isagCustomProperties = isagCustomProperties; _timeoutDb = isagCustomProperties.TimeOutDb; _reattempts = isagCustomProperties.Reattempts; _cstr = cstr; _events = events; _status = status; }
/// <summary> /// Provides the component properties /// </summary> public override void ProvideComponentProperties() { base.ProvideComponentProperties(); _IsagCustomProperties = new IsagCustomProperties(); _IsagCustomProperties.SetDefaultValues(); ComponentMetaDataTools.UpdateVersion(this, ComponentMetaData); //Set metadata version to DLL-Version DtsPipelineComponentAttribute componentAttr = (DtsPipelineComponentAttribute)Attribute.GetCustomAttribute(this.GetType(), typeof(DtsPipelineComponentAttribute), false); int binaryVersion = componentAttr.CurrentVersion; ComponentMetaData.Version = binaryVersion; //Clear out base implmentation this.ComponentMetaData.RuntimeConnectionCollection.RemoveAll(); this.ComponentMetaData.InputCollection.RemoveAll(); this.ComponentMetaData.OutputCollection.RemoveAll(); //Input IDTSInput100 input = this.ComponentMetaData.InputCollection.New(); input.Name = Constants.INPUT_NAME; input.Description = Constants.INPUT_NAME; input.HasSideEffects = true; //New connection managers IDTSRuntimeConnection100 conn = this.ComponentMetaData.RuntimeConnectionCollection.New(); conn.Name = Constants.CONNECTION_MANAGER_NAME_MAIN; conn.Description = "Main Connection to SQL Server"; //Custom Properties hinzufügen IDTSCustomProperty100 prop = ComponentMetaData.CustomPropertyCollection.New(); prop.Name = Constants.PROP_CONFIG; _IsagCustomProperties.Save(ComponentMetaData); }
/// <summary> /// load this properties from an xml string (taken from component metadatas custom properties /// and loads standard configuration if needed /// </summary> /// <param name="componentMetaData">the components metddata</param> /// <param name="needsStandardConfiguration">Is standard configuration needed?</param> /// <returns>instance of IsagCustomProperties</returns> public static IsagCustomProperties Load(IDTSComponentMetaData100 componentMetaData, bool needsStandardConfiguration) { IsagCustomProperties properties; try { properties = LoadFromXml(componentMetaData.CustomPropertyCollection[Constants.PROP_CONFIG].Value.ToString()); } catch (Exception ex) { if (!needsStandardConfiguration) { properties = new IsagCustomProperties(); properties.SetDefaultValues(); } else { throw new Exception("Cannot load the Configuration: " + ex.Message); } } try { StandardConfiguration stdConfiguration = new StandardConfiguration(componentMetaData.RuntimeConnectionCollection, properties.AutoUpdateStandardConfiguration); stdConfiguration.SetStandardConfiguration(ref properties); } catch (Exception ex) { if (needsStandardConfiguration) { throw new Exception("Cannot load Standard Configuration: " + ex.Message); } } return(properties); }
/// <summary> /// Creates SQL insert command /// </summary> /// <param name="properties">componets custom properties</param> /// <param name="tempTableName">temporary table name</param> /// <returns>sql insert command</returns> public static string GetSqlInsert(IsagCustomProperties properties, string tempTableName) { return(GetSqlInsert(properties, tempTableName, false)); }
/// <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); } }
/// <summary> /// Creates SQL insert command /// </summary> /// <param name="properties">componets custom properties</param> /// <param name="tempTableName">temporary table name</param> /// <param name="overrideCustomMergeCommand">If true, only custom sql template is returned (used for preview) </param> /// <returns></returns> public static string GetSqlInsert(IsagCustomProperties properties, string tempTableName, bool overrideCustomMergeCommand) { if (properties.UseCustomMergeCommand && !overrideCustomMergeCommand) { return(properties.CustomMergeCommand); } else { string insert = ""; string insertValues = ""; string insertDefault; string insertValue; string placeholder; foreach (ColumnConfig config in properties.ColumnConfigList) { if (config.Insert) { if (insert != "") { insert += ", "; } if (insertValues != "") { insertValues += ", "; } insert += Brackets(config.OutputColumnName); insertValue = Brackets(config.BulkColumnName); if (config.HasDefault) { insertDefault = "isnull(cast(" + insertValue + " as " + config.DataTypeOutput + ") , " + config.Default + ")"; if (config.HasFunction) { placeholder = "@(" + config.BulkColumnName + ")"; insertValues += config.Function.Replace(placeholder, insertDefault); } else { insertValues += insertDefault; } } else if (config.HasFunction) { placeholder = "@(" + config.BulkColumnName + ")"; insertValues += config.Function.Replace(placeholder, config.BulkColumnName); } else { insertValues += insertValue; } } } string insertTmp = "INSERT INTO " + Brackets(properties.DestinationTable); if (!properties.DisableTablock) { insertTmp += " WITH (tablockx)"; } insertTmp += Environment.NewLine + " (" + insert + ")" + Environment.NewLine + "SELECT " + insertValues + Environment.NewLine + "FROM " + Brackets(tempTableName); insert = insertTmp; return(ReplacePlaceHolderInputColumn(insert, "")); } }
/// <summary> /// Creates pseudo SQL merge command /// (merge statement is not available for SQL Server < 2008) /// </summary> /// <param name="properties">componets custom properties</param> /// <param name="tempTableName">temporary table name</param> /// <param name="overrideCustomMergeCommand">If true, only the updated_id tablename is replaced (used for preview) </param> /// <returns>pseudo sql merge command</returns> public static string GetSqlMerge2005(IsagCustomProperties properties, string tempTableName, bool overrideCustomMergeCommand) { if (properties.UseCustomMergeCommand && !overrideCustomMergeCommand) { return(properties.CustomMergeCommand .Replace(Constants.TEMP_UPD_TABLE_PLACEHOLDER, "[#updated_ids_" + Guid.NewGuid().ToString() + "]")); } else { string varUpdated_ids; if (properties.UseCustomMergeCommand) { varUpdated_ids = Constants.TEMP_UPD_TABLE_PLACEHOLDER; } else { varUpdated_ids = "[#updated_ids_" + Guid.NewGuid().ToString() + "]"; } string destTable = properties.DestinationTable; string declare = ""; //update string sqlUpdate = ""; string sqlUpdateOutput = ""; string sqlUpdateWhere = ""; //insert string sqlInsert = ""; string sqlInsertValues = ""; string sqlInsertLeftJoinOn = ""; string sqlInsertLeftJoinWhere = ""; string sqlInsertValuesDefinition = ""; foreach (ColumnConfig config in properties.ColumnConfigList) { //Keys if (config.Key) { //Declare if (declare != "") { declare += ", "; } declare += config.OutputColumnName + " " + config.DataTypeOutput; //Output if (sqlUpdateOutput != "") { sqlUpdateOutput += ", "; } sqlUpdateOutput += "inserted." + Brackets(config.OutputColumnName); //where (Update) if (sqlUpdateWhere != "") { sqlUpdateWhere += " AND "; } sqlUpdateWhere += "src." + Brackets(config.BulkColumnName) + " = " + "dest." + Brackets(config.OutputColumnName); //where (insert: Left Join On) if (sqlInsertLeftJoinOn != "") { sqlInsertLeftJoinOn += " AND "; } sqlInsertLeftJoinOn += "upd." + Brackets(config.OutputColumnName) + " = " + "src." + Brackets(config.BulkColumnName); //where (insert: Left Join WHere) if (sqlInsertLeftJoinWhere != "") { sqlInsertLeftJoinWhere += " AND "; } sqlInsertLeftJoinWhere += "upd." + Brackets(config.OutputColumnName) + " IS NULL"; } //Update if (config.Update) { if (sqlUpdate != "") { sqlUpdate += ", "; } sqlUpdate += Brackets(config.OutputColumnName) + " = "; if (config.HasFunction) { sqlUpdate += config.Function + " "; } else { sqlUpdate += "src." + Brackets(config.BulkColumnName) + " "; } } //Insert if (config.Insert) { if (sqlInsertValuesDefinition != "") { sqlInsertValuesDefinition += ", "; } if (sqlInsertValues != "") { sqlInsertValues += ", "; } sqlInsertValuesDefinition += Brackets(config.OutputColumnName); if (config.HasDefault) //Default Values for Insert: isnull(<columnname>, <defaultValue>) { sqlInsertValues += " isnull(cast(src." + Brackets(config.BulkColumnName) + " as " + config.DataTypeOutput + ") ," + config.Default + ")"; } else if (config.HasFunction) { sqlInsertValues += config.Function + " "; } else { //no Default Value or function sqlInsertValues += "src." + Brackets(config.BulkColumnName) + " "; } } } if (sqlUpdate != "") { declare = "CREATE TABLE " + varUpdated_ids + " (" + declare + ");" + Environment.NewLine; } else { declare = ""; } //update if (sqlUpdate != "") { string sqlUpdateTmp = "UPDATE " + destTable + Environment.NewLine; if (!properties.DisableTablock) { sqlUpdateTmp += "WITH (Tablockx)" + Environment.NewLine; } sqlUpdateTmp += "SET " + sqlUpdate + Environment.NewLine + "OUTPUT " + sqlUpdateOutput + " INTO " + varUpdated_ids + Environment.NewLine + "FROM " + destTable + " dest, " + tempTableName + " src" + Environment.NewLine + "WHERE " + sqlUpdateWhere + ";" + Environment.NewLine; sqlUpdate = sqlUpdateTmp; } //insert sqlInsert = "INSERT INTO " + destTable; if (!properties.DisableTablock) { sqlInsert += " WITH (Tablockx)"; } sqlInsert += Environment.NewLine + "(" + sqlInsertValuesDefinition + ")" + Environment.NewLine + "SELECT " + sqlInsertValues + " FROM " + tempTableName + " src" + Environment.NewLine; if (sqlUpdate != "") { sqlInsert += "LEFT JOIN " + varUpdated_ids + " upd" + Environment.NewLine + "ON " + sqlInsertLeftJoinOn + Environment.NewLine + "WHERE " + sqlInsertLeftJoinWhere + Environment.NewLine; } sqlInsert += ";"; return(ReplacePlaceHolderInputColumn(declare + sqlUpdate + sqlInsert, "src.")); } }
/// <summary> /// Creates SQL insert (using stored procedure) command /// </summary> /// <param name="properties">componets custom properties</param> /// <param name="tempTableName">temporary table name</param> /// <returns>sql insert (using stored procedure) command</returns> public static string GetSqlInsertSP(IsagCustomProperties properties, string tempTableName) { string result = ""; string spStart = "CREATE PROCEDURE " + "<SPName>" + Environment.NewLine + "as" + Environment.NewLine + "BEGIN" + Environment.NewLine + " set nocount on" + Environment.NewLine + Environment.NewLine; string spEnd = Environment.NewLine + " Close myInsertCursor" + Environment.NewLine + " Deallocate myInsertCursor" + Environment.NewLine + " set nocount off" + Environment.NewLine + "END" + Environment.NewLine; string initVar = ""; string fetch = ""; string initCursor = ""; string initCursorLeftJoin = ""; string initCursorWhere = ""; string insert = ""; string insertValues = ""; string insertDefault; string insertValue; foreach (ColumnConfig config in properties.ColumnConfigList) { if (config.Insert) { if (insert != "") { insert += ", "; } if (insertValues != "") { insertValues += ", "; } insert += config.OutputColumnName; insertValue = "@(" + config.BulkColumnName + ")"; if (config.HasDefault) { insertDefault = "isnull(cast(" + insertValue + " as " + config.DataTypeOutput + ") , " + config.Default + ")"; if (config.HasFunction) { insertValues += config.Function.Replace(insertValue, insertDefault); } else { insertValues += insertDefault; } } else if (config.HasFunction) { insertValues += config.Function; } else { insertValues += insertValue; } if (initCursor != "") { initCursor += ", "; } initCursor += Brackets(tempTableName) + "." + Brackets(config.BulkColumnName); initVar += " Declare @" + config.BulkColumnName + " as " + config.BulkDataType + Environment.NewLine; if (fetch != "") { fetch += ", "; } fetch += " @" + config.BulkColumnName; } if (config.Key) { if (initCursorLeftJoin != "") { initCursorLeftJoin += " and "; } initCursorLeftJoin += Brackets(tempTableName) + "." + Brackets(config.BulkColumnName) + " = " + Brackets(properties.DestinationTable) + "." + Brackets(config.OutputColumnName); if (initCursorWhere == "") { initCursorWhere = Brackets(properties.DestinationTable) + "." + Brackets(config.OutputColumnName) + " Is Null"; } } } insert = " insert into " + Brackets(properties.DestinationTable) + Environment.NewLine + " (" + insert + ")" + Environment.NewLine + " Values (" + insertValues + ")"; initCursor = Environment.NewLine + " Declare myInsertCursor cursor FORWARD_ONLY for" + Environment.NewLine + " Select " + initCursor + Environment.NewLine + " from " + Brackets(tempTableName) + Environment.NewLine + " Left Join " + Brackets(properties.DestinationTable) + Environment.NewLine + " On " + initCursorLeftJoin + Environment.NewLine + " Where " + initCursorWhere + Environment.NewLine + Environment.NewLine; fetch = Environment.NewLine + Environment.NewLine + " FETCH NEXT from myInsertCursor into" + Environment.NewLine + fetch + Environment.NewLine; result = spStart + initVar + initCursor + " Open myInsertCursor" + fetch + Environment.NewLine + " While @@fetch_status = 0 begin" + Environment.NewLine + Environment.NewLine + insert + fetch.Replace(Environment.NewLine, Environment.NewLine + " ") + Environment.NewLine + " END --while Insert" + Environment.NewLine + spEnd; return(ReplacePlaceHolderInputColumn(result, "@")); }