/// <summary> /// PreExecute phase: Gather all needed informations /// </summary> public override void PreExecute() { base.PreExecute(); InitializeProperties(); bool hasKey = (_isagCustomProperties.UseMatchParameter); /* Hashtable / Get Lookup Table */ DataTable dt = new DataTable(); SqlConnection con = _mainConn; SqlCommand cmd = con.CreateCommand(); cmd.CommandText = _isagCustomProperties.GetSqlLookupQuery(ComponentMetaDataTools.GetTableExpressionFromTemplate(_isagCustomProperties.LU_Table, VariableDispenser, ComponentMetaData)); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(dt); _hashLookupTable = new Hashtable(); List <object> newLine = new List <object>(); object key = null; //match parameter value if (!hasKey) { key = 0; //if no match parameter is used -> the dictionaries has a single key with value 0 } foreach (DataRow row in dt.Rows) { if (hasKey) //match parameter is used, so multiple keys will be used for the hashtable { //key/match column value: null (first row of dataTable) or has changed if (key == null || !key.Equals(row[Constants.ALIAS_ID])) { //add new entry (old match column value + gathered row data) to hashtable if (key != null) { _hashLookupTable.Add(key, newLine.ToArray()); } // clear stored rows newLine.Clear(); //get new key / match column value if (hasKey) { key = row[Constants.ALIAS_ID]; } //store row data newLine.Add(row.ItemArray); } else { //store row data newLine.Add(row.ItemArray); } } else //match parameter is not used, so a single key will be used for the hashtable { newLine.Add(row.ItemArray); } } //add hashtable entry at the end of the row list if (newLine.Count > 0) { _hashLookupTable.Add(key, newLine.ToArray()); } /* Input Mapping */ IDTSInput100 input = this.ComponentMetaData.InputCollection[Constants.INPUT_NAME]; _colInfo = new ColumnInfo(); _colInfo.UseMatchColumn = _isagCustomProperties.UseMatchParameter; _colInfo.IsInclusiveUpperBound = _isagCustomProperties.LU2_IsInclusiveUpperBound; _colInfo.BufferIndexValidFromToColumn = this.BufferManager.FindColumnByLineageID(input.Buffer, input.InputColumnCollection[_isagCustomProperties.LU2_Validparameter].LineageID); if (hasKey) { _colInfo.BufferIndexMatchColumn = this.BufferManager.FindColumnByLineageID(input.Buffer, input.InputColumnCollection[_isagCustomProperties.LU_Matchparameter].LineageID); } else { _colInfo.BufferIndexMatchColumn = -1; } /* Output Mapping*/ _colInfo.BufferIndexOoutputColumns = new int[_isagCustomProperties.OutputConfigList.Count]; for (int i = 0; i < _isagCustomProperties.OutputConfigList.Count; i++) { _colInfo.BufferIndexOoutputColumns[i] = this.BufferManager.FindColumnByLineageID(input.Buffer, input.InputColumnCollection[_isagCustomProperties.OutputConfigList[i].DftColumn].LineageID); } }
/// <summary> /// Is this component configuration valid? /// </summary> /// <param name="componentMetaData">the components metadata</param> /// <param name="conn">the componenten sql connection</param> /// <param name="variableDispenser">SSIS variables</param> /// <returns>Is this component configuration valid?</returns> public bool IsValid(IDTSComponentMetaData100 componentMetaData, SqlConnection conn, Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSVariableDispenser100 variableDispenser) { bool result = true; //SQL Statement auf die Lookup-Tabelle prüfen try { SqlCommand sqlCom = conn.CreateCommand(); sqlCom.CommandText = GetSqlLookupQuery(1, ComponentMetaDataTools.GetTableExpressionFromTemplate(LU_Table, variableDispenser, componentMetaData)); SqlDataAdapter adapter = new SqlDataAdapter(sqlCom); DataTable dt = new DataTable(); adapter.Fill(dt); conn.Close(); } catch (Exception) { Events.Fire(componentMetaData, Events.Type.Error, "The generated SQL statement is incorrect!"); result = false; } //Match Parameter prüfen if (UseMatchParameter) { try { IDTSInputColumn100 inputCol = componentMetaData.InputCollection[0].InputColumnCollection.GetObjectByID(LU_MatchparameterId); if (inputCol.Name != LU_Matchparameter) { LU_Matchparameter = inputCol.Name; Save(componentMetaData); } } catch (Exception) { Events.Fire(componentMetaData, Events.Type.Error, "LU_Matchparameter: The column " + LU_Matchparameter + "(" + LU_MatchparameterId.ToString() + ") is not part of the InputColumnCollection."); result = false; } } //Valid Parameter prüfen try { IDTSInputColumn100 inputCol = componentMetaData.InputCollection[0].InputColumnCollection.GetObjectByID(LU2_ValidparameterId); if (inputCol.Name != LU2_Validparameter) { LU2_Validparameter = inputCol.Name; Save(componentMetaData); } } catch (Exception) { Events.Fire(componentMetaData, Events.Type.Error, "LU2_Validparameter: The column " + LU2_Validparameter + "(" + LU2_ValidparameterId.ToString() + ") is not part of the InputColumnCollection."); result = false; } string sql = ""; //Output DFT Columns prüfen und SQL Statement zum prüfen der Output Table Columns erzeugen foreach (OutputConfig outConfig in OutputConfigList) { try { if (sql != "") { sql += ","; } sql += outConfig.SqlColumn; IDTSInputColumn100 inputCol = componentMetaData.InputCollection[0].InputColumnCollection.GetObjectByID(outConfig.DftColumnId); if (inputCol.Name != outConfig.DftColumn) { outConfig.DftColumn = inputCol.Name; Save(componentMetaData); } } catch (Exception) { Events.Fire(componentMetaData, Events.Type.Error, "Output columns: The column " + outConfig.DftColumn + "(" + outConfig.DftColumnId.ToString() + ") is not part of the OutputColumnCollection and will be removed."); sql = ""; result = false; } } //Output Table Columns prüfen if (sql != "") { try { SqlCommand sqlCom = conn.CreateCommand(); sqlCom.CommandText = "select top 1 " + sql + " from " + ComponentMetaDataTools.GetTableExpressionFromTemplate(LU_Table, variableDispenser, componentMetaData); SqlDataAdapter adapter = new SqlDataAdapter(sqlCom); DataTable dt = new DataTable(); adapter.Fill(dt); conn.Close(); } catch (Exception) { Events.Fire(componentMetaData, Events.Type.Error, "One or more output SQL columns are not part of the database table."); result = false; } } return(result); }