// In the PreExecute phase we will be caching the column indexes looked up by the LineageID public override void PreExecute() { // Initialize the cache columnIndexes = new Dictionary <string, Dictionary <string, int> >(); // Get the model JSONDataModel model = getModel(); // For each table definition foreach (DataTable table in model.Tables) { // Find the ouput IDTSOutput100 output = ComponentMetaData.OutputCollection[table.TableName]; // ... and initialize the corresponding cache index columnIndexes[table.TableName] = new Dictionary <string, int>(); // For every column foreach (IDTSOutputColumn100 col in output.OutputColumnCollection) { // We wrap this in a try-catch without exception handling so that // we allow for a subset of the outputs to be used. If an output is not // connected, the columns defined in that output will not be in the Buffer try { // Cache the buffer column index columnIndexes[table.TableName][col.Name] = BufferManager.FindColumnByLineageID(output.Buffer, col.LineageID); } catch { // Do nothing for now } } } }
public CouchbaseSourceAdapterUIForm(Connections cons, Variables vars, IDTSComponentMetaData100 md) { InitializeComponent(); variables = vars; connections = cons; metaData = md; this.Text = metaData.Name + " Configuration"; if (designTimeInstance == null) { designTimeInstance = metaData.Instantiate(); } txtURL.Text = metaData.CustomPropertyCollection["url"].Value.ToString(); txtBucket.Text = metaData.CustomPropertyCollection["bucket"].Value.ToString(); txtPassword.Text = metaData.CustomPropertyCollection["password"].Value.ToString(); txtDesignDoc.Text = metaData.CustomPropertyCollection["designDoc"].Value.ToString(); txtView.Text = metaData.CustomPropertyCollection["view"].Value.ToString(); cbForceReindex.Checked = metaData.CustomPropertyCollection["forceReindex"].Value; cbDescending.Checked = metaData.CustomPropertyCollection["descending"].Value; txtStartKey.Text = metaData.CustomPropertyCollection["startKey"].Value.ToString(); txtEndKey.Text = metaData.CustomPropertyCollection["endKey"].Value.ToString(); string modelXML = metaData.CustomPropertyCollection["modelXML"].Value.ToString(); model = new JSONDataModel(); if (!modelXML.Trim().Equals("")) { model = new JSONDataModel(modelXML); } }
private void btnClearModel_Click(object sender, EventArgs e) { if (MessageBox.Show("Are you sure?", "Clear Model", MessageBoxButtons.YesNo) == System.Windows.Forms.DialogResult.Yes) { currentModel = new JSONDataModel(); } }
/// <summary> /// Write a json document to the output buffers /// </summary> /// <param name="id">The document key</param> /// <param name="json">The JSON representation of the document</param> /// <param name="model">A model used to decompose the document</param> /// <param name="outputIDs">Indexes of the output buffers</param> /// <param name="buffers">The Pipline Buffers</param> private void writeDocToBuffers(string id, string json, JSONDataModel model, int[] outputIDs, PipelineBuffer[] buffers) { // Deserialize the document JsonObject jp = new JsonObject(json); // For each model table foreach (DataTable modelTable in model.Tables) { // Obtain the output IDTSOutput100 output = ComponentMetaData.OutputCollection[modelTable.TableName]; // Continue if output is not connected if (Array.IndexOf(outputIDs, output.ID) == -1) { continue; } // Obtain the buffer tho write to PipelineBuffer buffer = buffers[Array.IndexOf(outputIDs, output.ID)]; // If we are processing a table that has indexes on the second column // this must be a child table if (modelTable.Rows.Count > 2 && modelTable.Rows[1]["xpath"].ToString().StartsWith("#")) { // Go and process the related table processChildTable(id, jp, model, modelTable.TableName, outputIDs, buffers); } else { // Add a new row to the buffer buffer.AddRow(); // For every column definition in teh model foreach (DataRow row in modelTable.Rows) { // Find the column index that the buffer will recognize int columnIndex = columnIndexes[modelTable.TableName][row["shortColumn"].ToString()]; // Get the model datatype Type dataType = Type.GetType(row["datatype"].ToString()); // And XPath string xpath = row["xpath"].ToString(); // Handle special case of DOC_ID if (xpath.Equals("#ID")) { buffer.SetString(columnIndex, id); } else { // Write all other fields as usual writeDataToBuffer(jp, buffer, columnIndex, dataType, xpath); } } } } }
private void btnMapping_Click(object sender, EventArgs e) { // Create and display the form for the user interface. DataMappingForm dataMappingForm = new DataMappingForm(metaData, designTimeInstance, currentView); DialogResult result = dataMappingForm.ShowDialog(); if (result == DialogResult.OK) { model = dataMappingForm.currentModel; } }
/// <summary> /// Performs a deep merge between two model datasets by comparing column definition rows by /// their first element, and enforcing type precedence over the second elements /// </summary> /// <param name="ds1">First Dataset</param> /// <param name="model">Model to merge with</param> /// <returns>A merged vercion of both datasets. The merged version prefers the first dataset's entries /// for column indexes greater than 1</returns> public JSONDataModel mergeWith(DataSet model) { // The result will begin as a copy of the first dataset JSONDataModel result = this.Copy(); // Compare tables foreach (DataTable table2 in model.Tables) { // Add the table if missing if (!result.Tables.Contains(table2.TableName)) { result.Tables.Add(table2.TableName); foreach (DataColumn col2 in table2.Columns) { DataColumn col1 = new DataColumn(col2.ColumnName); col1.DataType = col2.DataType; result.Tables[table2.TableName].Columns.Add(col1); } } // Cache the table from the first dataset DataTable table1 = result.Tables[table2.TableName]; // Compare the rows and perform the merge foreach (DataRow row2 in table2.Rows) { // Find the row by comparing the first column which holds the column name of the output int rowIndex = -1; for (int i = 0; i < table1.Rows.Count; i++) { if (row2[0].Equals(table1.Rows[i][0])) { rowIndex = i; break; } } // If the row exists, merge the datatype stored in the second column if (rowIndex >= 0) { table1.Rows[rowIndex][1] = getTypePrecedence(table1.Rows[rowIndex][1].ToString(), row2[1].ToString()); } // otherwise copy the row else { table1.ImportRow(row2); } } } return(result); }
/// <summary> /// Writes out a child table. Uses an iterative approach. Would have been much more /// readable if this was recursive, but we really should not be using it. /// </summary> /// <param name="id">The document key</param> /// <param name="jp">A deserialized object holding the document</param> /// <param name="model">A model used to decompose the document</param> /// <param name="tableName">The name of the child table</param> /// <param name="outputIDs">Indexes of the output buffers</param> /// <param name="buffers">The Pipline Buffers</param> void processChildTable(string id, JsonObject jp, JSONDataModel model, string tableName, int[] outputIDs, PipelineBuffer[] buffers) { // Obtain references to the model table, output and buffer DataTable modelTable = model.Tables[tableName]; IDTSOutput100 output = ComponentMetaData.OutputCollection[modelTable.TableName]; PipelineBuffer buffer = buffers[Array.IndexOf(outputIDs, output.ID)]; // The counters will track how many levels of nesting we have int counters = 0; // We will first represent all records that need to be written out as XPaths List <List <string> > xPathRows = new List <List <string> >(); // List <string> xPathRow = new List <string>(); // Collect counters and initial row XPaths foreach (DataRow row in modelTable.Rows) { string xPath = row["xpath"].ToString(); if (xPath.Equals("#ID")) { // Nothing for now } else if (xPath.StartsWith("#")) { counters++; } xPathRow.Add(xPath); } // Add the initial Row xPathRows.Add(xPathRow); // Iteratively expand each row based on the indexes found for (int i = 0; i < counters; i++) { xPathRows = expandRows(jp, i, xPathRows, modelTable); } // Iterate over the expanded rows. They should contain // full XPaths to be used to obtain the values for each cell foreach (List <string> row in xPathRows) { // Add a row to the buffer buffer.AddRow(); // Keep track of which column we are looking at int colIdx = 0; // For each column definition in the model foreach (DataRow modelCol in modelTable.Rows) { // Get the name and the index string col = modelCol["shortColumn"].ToString(); int columnIndex = columnIndexes[modelTable.TableName][col]; // Handle special case of DOC_ID if (row[colIdx].Equals("#ID")) { buffer.SetString(columnIndex, id); } // Handle special case of child table indexes else if (row[colIdx].StartsWith("#")) { // Obtain the nesting index from the XPath by looking at the first index found int counterIndex = int.Parse(row[colIdx].Substring(1)); // Extract the current value of the index Match m = Regex.Match(row[row.Count - 1], "\\[([0-9]+)\\]"); long index = long.Parse(m.Groups[counterIndex + 1].Value); // and write it out to the buffer buffer.SetInt64(columnIndex, index); } else { // Write all other fields as usual Type dataType = Type.GetType(modelCol["datatype"].ToString()); string xpath = row[colIdx]; writeDataToBuffer(jp, buffer, columnIndex, dataType, xpath); } // Increment to look at the next column colIdx++; } } }
// The main data flow function public override void PrimeOutput(int outputs, int[] outputIDs, PipelineBuffer[] buffers) { // Get teh model JSONDataModel model = getModel(); // Initialize Couchbase Client CouchbaseClientConfiguration config = new CouchbaseClientConfiguration(); config.Urls.Add(new Uri(ComponentMetaData.CustomPropertyCollection["url"].Value.ToString().TrimEnd('/') + "/pools/")); config.Bucket = ComponentMetaData.CustomPropertyCollection["bucket"].Value.ToString(); config.BucketPassword = ComponentMetaData.CustomPropertyCollection["password"].Value.ToString(); CouchbaseClient client = new CouchbaseClient(config); // Extract the parameters string designDoc = ComponentMetaData.CustomPropertyCollection["designDoc"].Value.ToString(); string viewName = ComponentMetaData.CustomPropertyCollection["view"].Value.ToString(); bool forceReindex = (bool)ComponentMetaData.CustomPropertyCollection["forceReindex"].Value; bool descending = (bool)ComponentMetaData.CustomPropertyCollection["descending"].Value; // Define the view to be executed IView <IViewRow> view = ((IView <IViewRow>)client.GetView(designDoc, viewName)) .Stale(forceReindex ? StaleMode.False : StaleMode.AllowStale) .Descending(descending); // Extract the variables from the package IDTSVariables100 variables = null; // StartKey can be set from another task prior of running this task string startKey = ComponentMetaData.CustomPropertyCollection["startKey"].Value; if (startKey != null && startKey.StartsWith("@")) { VariableDispenser.LockOneForRead(startKey.Substring(1), ref variables); startKey = variables[0].Value.ToString(); variables.Unlock(); ComponentMetaData.PostLogMessage("Couchbase", ComponentMetaData.Name, "Found a variable StartKey. Using " + startKey + " as value.", DateTime.Now, DateTime.Now, 0, null); } // EndKey can be set from another task prior of running this task string endKey = ComponentMetaData.CustomPropertyCollection["endKey"].Value; if (endKey != null && endKey.StartsWith("@")) { VariableDispenser.LockOneForRead(endKey.Substring(1), ref variables); endKey = variables[0].Value.ToString(); variables.Unlock(); ComponentMetaData.PostLogMessage("Couchbase", ComponentMetaData.Name, "Found a variable EndKey. Using " + endKey + " as value.", DateTime.Now, DateTime.Now, 0, null); } // Apply variables to the view if necessary if (startKey != null && !startKey.Equals("")) { view = view.StartKey <string>(startKey); } if (endKey != null && !endKey.Equals("")) { view = view.EndKey <string>(endKey); } // Iterate over each document returned by the view foreach (IViewRow row in view) { // Say that we have read it ComponentMetaData.IncrementPipelinePerfCounter(101, 1); // Write it out to the outputs writeDocToBuffers(row.ItemId, row.GetItem().ToString(), model, outputIDs, buffers); // Say that we wrote it ComponentMetaData.IncrementPipelinePerfCounter(103, 1); } // Flush out all buffers and get outta here foreach (PipelineBuffer buffer in buffers) { /// Notify the data flow task that no more rows are coming. buffer.SetEndOfRowset(); } }
// Rebuild the component outputs based on the model public override void ReinitializeMetaData() { // Clean out the outputs ComponentMetaData.OutputCollection.RemoveAll(); // Get the model JSONDataModel model = getModel(); // For every output table model crete the SSIS outputs foreach (DataTable table in model.Tables) { // Define a new output IDTSOutput100 output = ComponentMetaData.OutputCollection.New(); output.Name = table.TableName; // For every column definition in the model, create the corresponding output column foreach (DataRow row in table.Rows) { // Create a new Output Column IDTSOutputColumn100 outColumn = output.OutputColumnCollection.New(); // Set column data type properties. bool isLong = false; // Assume string for missing datatypes Type dataType = row["datatype"].Equals("") ? typeof(string) : Type.GetType(row["datatype"].ToString()); // Translate the datatype into the SSIS intermediate type DataType dt = DataRecordTypeToBufferType(dataType); dt = ConvertBufferDataTypeToFitManaged(dt, ref isLong); // Enforce numeric datatypes switch (dt) { case DataType.DT_R4: case DataType.DT_R8: case DataType.DT_DECIMAL: dt = DataType.DT_NUMERIC; break; case DataType.DT_BOOL: case DataType.DT_I1: case DataType.DT_I2: case DataType.DT_I4: dt = DataType.DT_I4; break; case DataType.DT_UI1: case DataType.DT_UI2: case DataType.DT_UI4: dt = DataType.DT_UI4; break; case DataType.DT_DBTIMESTAMP: case DataType.DT_DBTIMESTAMP2: case DataType.DT_DBDATE: case DataType.DT_DATE: case DataType.DT_FILETIME: dt = DataType.DT_DBTIMESTAMP; break; } // Assume defaults and limits int length = 0; int precision = 2000; int scale = 0; int codepage = table.Locale.TextInfo.ANSICodePage; // Handle the datatype cases switch (dt) { // The length cannot be zero, and the code page property must contain a valid code page. case DataType.DT_STR: case DataType.DT_TEXT: length = precision; precision = 0; scale = 0; break; case DataType.DT_WSTR: length = precision; codepage = 0; scale = 0; precision = 0; break; case DataType.DT_NUMERIC: length = 0; codepage = 0; precision = 24; scale = 6; break; default: length = 0; precision = 0; codepage = 0; scale = 0; break; } // Set the properties of the output column. outColumn.Name = (string)row["shortColumn"]; outColumn.Description = (string)row["xpath"]; outColumn.SetDataTypeProperties(dt, length, precision, scale, codepage); // Set the properties of the metadata column to facilitate automatic binding IDTSExternalMetadataColumn100 extColumn = output.ExternalMetadataColumnCollection.New(); extColumn.Name = (string)row["shortColumn"]; extColumn.Description = (string)row["xpath"]; extColumn.DataType = dt; extColumn.Length = length; extColumn.Precision = precision; extColumn.Scale = scale; extColumn.CodePage = codepage; extColumn.MappedColumnID = outColumn.ID; } } }
// The Validate phase will be checking the model against its outputs // and will ensure that all properties are set correctly public override DTSValidationStatus Validate() { bool pbCancel = false; // Validate that the url custom property is set. if (ComponentMetaData.CustomPropertyCollection["url"].Value == null || ((string)ComponentMetaData.CustomPropertyCollection["url"].Value).Length == 0) { ComponentMetaData.FireError(0, ComponentMetaData.Name, "The URL property must be set.", "", 0, out pbCancel); return(DTSValidationStatus.VS_ISBROKEN); } // Validate that the bucket custom property is set. if (ComponentMetaData.CustomPropertyCollection["bucket"].Value == null || ((string)ComponentMetaData.CustomPropertyCollection["bucket"].Value).Length == 0) { ComponentMetaData.FireError(0, ComponentMetaData.Name, "The Bucket property must be set.", "", 0, out pbCancel); return(DTSValidationStatus.VS_ISBROKEN); } // Validate that the password custom property is set. if (ComponentMetaData.CustomPropertyCollection["password"].Value == null || ((string)ComponentMetaData.CustomPropertyCollection["password"].Value).Length == 0) { ComponentMetaData.FireError(0, ComponentMetaData.Name, "The Password property must be set.", "", 0, out pbCancel); return(DTSValidationStatus.VS_ISBROKEN); } // Validate that the designDoc custom property is set. if (ComponentMetaData.CustomPropertyCollection["designDoc"].Value == null || ((string)ComponentMetaData.CustomPropertyCollection["designDoc"].Value).Length == 0) { ComponentMetaData.FireError(0, ComponentMetaData.Name, "The Design Document property must be set.", "", 0, out pbCancel); return(DTSValidationStatus.VS_ISBROKEN); } // Validate that the view custom property is set. if (ComponentMetaData.CustomPropertyCollection["view"].Value == null || ((string)ComponentMetaData.CustomPropertyCollection["view"].Value).Length == 0) { ComponentMetaData.FireError(0, ComponentMetaData.Name, "The View property must be set.", "", 0, out pbCancel); return(DTSValidationStatus.VS_ISBROKEN); } // Validate outputs against the model JSONDataModel model = getModel(); // If the output count does not match, regenerate it if (ComponentMetaData.OutputCollection.Count != model.Tables.Count) { ComponentMetaData.FireError(0, ComponentMetaData.Name, "Number of outputs must match the number of tables in the model", "", 0, out pbCancel); return(DTSValidationStatus.VS_NEEDSNEWMETADATA); } // Inpect every table foreach (DataTable table in model.Tables) { string name = table.TableName; IDTSOutput100 output = ComponentMetaData.OutputCollection[name]; // If the column count does not match, or the specific output is not found, regenerate it if (output == null || output.OutputColumnCollection.Count != table.Rows.Count) { return(DTSValidationStatus.VS_NEEDSNEWMETADATA); } // If the columns do not match, regenerate it foreach (DataRow row in table.Rows) { string colName = row["shortColumn"].ToString(); IDTSOutputColumn100 outputColumn = output.OutputColumnCollection[colName]; // If the specific column is not found, regenerate it if (outputColumn == null) { return(DTSValidationStatus.VS_NEEDSNEWMETADATA); } } } // Let the base class verify that the input column reflects the output // of the upstream component. return(base.Validate()); }
private void btnGenerateModel_Click(object sender, EventArgs e) { // Start with an empty model //currentModel = new JSONDataModel(); // Clear the logs txtLog.Text = ""; if (cbRepresentative.Checked) { if (File.Exists(txtModelFile.Text)) { // Initialize the logs string logMessages; // Create the model of this document JSONDataModel model = new JSONDataModel(File.ReadAllText(txtModelFile.Text), txtRootXPath.Text, txtTableNameXPath.Text, out logMessages); // Merge it with the current model currentModel = currentModel.mergeWith(model); // Append the logs txtLog.Text += logMessages; } else { MessageBox.Show("File does not exists. Please select a valid representative file."); } } if (cbSample.Checked) { // Sample documents and union the results foreach (var row in currentView.Limit((int)nudDocumentsToSample.Value)) { // Initialize the logs string logMessages; // Create the model of this document JSONDataModel model = new JSONDataModel(row.GetItem().ToString(), txtRootXPath.Text, txtTableNameXPath.Text, out logMessages); // Merge it with the current model currentModel = currentModel.mergeWith(model); // Append the logs txtLog.Text += logMessages; } } // Draw the model comboModelTables.Items.Clear(); foreach (DataTable table in currentModel.Tables) { comboModelTables.Items.Add(table.TableName); } if (comboModelTables.Items.Count > 0) { comboModelTables.SelectedIndex = 0; } }