/// <summary>
/// Pre an dpost sql statements may contain placeholder for variables.
/// Those placeholder will be replaced with variable values.
/// </summary>
/// <param name="templateStatement">sql statement</param>
/// <returns>sql statement without placeholder</returns>
public string GetExecuteStatementFromTemplate(string templateStatement)
{
string result = templateStatement;
string varName = "";
try
{
if (result != "")
{
while (result.Contains("@("))
{
IDTSVariables100 var = null;
int start = result.IndexOf("@(", 0);
int end = result.IndexOf(")", start);
varName = result.Substring(start + 2, end - start - 2);
_variableDispenser.LockOneForRead(varName, ref var);
result = result.Replace("@(" + varName + ")", var[varName].Value.ToString());
var.Unlock();
}
}
}
catch (Exception ex)
{
_events.Fire(IsagEvents.IsagEventType.ErrorVariableNotFound, "[{0}]: Variable not found: {1}",
new string[] { "Pre-/PostSql", ex.Message });
}
return(result);
}
private void ResolveComponentCustomProperties()
{
_dataProfileFileName = ComponentMetaData.CustomPropertyCollection[DATA_PROFILE_FILE_NAME_PROPERTY_NAME].Value.ToString();
if (VariableDispenser.Contains(_dataProfileFileName))
{
IDTSVariables100 variables = null;
VariableDispenser.LockOneForRead(_dataProfileFileName, ref variables);
_dataProfileFileName = (String)variables[0].Value;
}
_dataProfileColumnName = ComponentMetaData.CustomPropertyCollection[DATA_PROFILE_COLUMN_NAME_PROPERTY_NAME].Value.ToString();
if (VariableDispenser.Contains(_dataProfileColumnName))
{
IDTSVariables100 variables = null;
VariableDispenser.LockOneForRead(_dataProfileColumnName, ref variables);
_dataProfileColumnName = (String)variables[0].Value;
}
_regexPatterns.Clear();
_regexPatterns = this.LoadRegularExpressions(_dataProfileFileName, _dataProfileColumnName);
_emailAddressInputColumnName = ComponentMetaData.InputCollection[INPUT_NAME].CustomPropertyCollection[INPUT_COLUMN_NAME].Value.ToString();
// v2
_regexOptionsNumber = (Int64)(ComponentMetaData.CustomPropertyCollection[REGEX_OPTIONS_PROPERTY_NAME].Value);
}
/// <summary>
/// Return value of SSIS variable
/// </summary>
/// <param name="variableDispenser">SSIS variable dispenser</param>
/// <param name="variableName">variable name</param>
/// <returns>variable value</returns>
public static string GetValueFromVariable(IDTSVariableDispenser100 variableDispenser, string variableName)
{
string result;
IDTSVariables100 var = null;
variableDispenser.LockOneForRead(variableName, ref var);
result = var[variableName].Value.ToString();
var.Unlock();
return(result);
}
public DataTable GetDataTableWithInputVar(String filePathVar, String sheetName)
{
IDTSVariables100 vars = null;
VariableDispenser.LockForRead(filePathVar);
VariableDispenser.GetVariables(out vars);
string filePath = (String)vars[filePathVar].Value;
vars.Unlock();
DataTable dt = ExcelService.ReadExcel(filePath, sheetName, 1);
return(dt);
}
public static object GetVariable(IDTSVariableDispenser100 vd, string varname, out DataType vartype)
{
object o = null;
IDTSVariables100 vars = null;
try
{
vd.LockOneForRead(varname, ref vars);
o = vars[varname].Value;
vartype = (DataType)vars[varname].DataType;
return(o);
}
finally
{
if (vars != null)
{
vars.Unlock();
}
}
}
/// <summary>
/// Replace placeholders in sql statments with variable values
/// </summary>
/// <param name="templateTableName">sql statement template</param>
/// <returnsexecutable sql statementtatement</returns>
public static string GetTableExpressionFromTemplate(string templateTableName, IDTSVariableDispenser100 variableDispenser, IDTSComponentMetaData100 componentMetaData)
{
if (templateTableName == "")
{
return("");
}
string result = templateTableName;
try
{
if (result != "")
{
while (result.Contains("@("))
{
string varName = "";
IDTSVariables100 var = null;
int start = result.IndexOf("@(", 0);
int end = result.IndexOf(")", start);
varName = result.Substring(start + 2, end - start - 2);
variableDispenser.LockOneForRead(varName, ref var);
result = result.Replace("@(" + varName + ")", var[varName].Value.ToString());
var.Unlock();
}
}
}
catch (Exception ex)
{
Events.Fire(componentMetaData, Events.Type.Error, string.Format("[{0}]: Variable not found: {1}", "Pre-/PostSql", ex.Message));
}
return(result);
}
//public override void AcquireConnections(object transaction)
//{
// IDTSRuntimeConnection100 item;
// object obj;
// bool flag, flag1, flag2, flag3;
// try
// {
// item = base.ComponentMetaData.RuntimeConnectionCollection[CONNNAME];
// }
// catch (Exception ex)
// {
// Microsoft.SqlServer.Dts.ManagedMsg.ErrorSupport errorSupport = base.ErrorSupport;
// object[] objArray = new object[] { CONNNAME };
// errorSupport.FireErrorWithArgs(-1071611878, out flag1, objArray);
// throw new PipelineComponentHResultException(-1071611878);
// }
// IDTSConnectionManager100 connectionManager = item.ConnectionManager;
// if (connectionManager == null)
// {
// Microsoft.SqlServer.Dts.ManagedMsg.ErrorSupport errorSupport1 = base.ErrorSupport;
// object[] objArray1 = new object[] { CONNNAME };
// errorSupport1.FireErrorWithArgs(-1071611851, out flag2, objArray1);
// throw new PipelineComponentHResultException(-1071611851);
// }
// try
// {
// obj = connectionManager.AcquireConnection(transaction);
// }
// catch (Exception ex2)
// {
// Exception ex1 = ex2;
// var errorSupport2 = base.ErrorSupport;
// object[] connectionManagerId = new object[] { item.ConnectionManagerID, ex1.Message };
// errorSupport2.FireErrorWithArgs(-1071610798, out flag3, connectionManagerId);
// throw new PipelineComponentHResultException(ex1.Message, -1071610798);
// }
// var httpConnection = obj as HttpClientConnection100;
// //base.AcquireConnections(transaction);
//}
public override void PreExecute()
{
IDTSInput100 input = ComponentMetaData.InputCollection[0];
bufferIdxs = new int[input.InputColumnCollection.Count];
columnDictionary = new BufferNameMap();
for (int x = 0; x < input.InputColumnCollection.Count; x++)
{
IDTSInputColumn100 column = input.InputColumnCollection[x];
bufferIdxs[x] = BufferManager.FindColumnByLineageID(input.Buffer, column.LineageID);
columnDictionary.Add(column.Name, bufferIdxs[x]);
}
//base.PreExecute();
IDTSVariables100 variables = null;
VariableDispenser.LockForRead(ADMINKEY);
VariableDispenser.LockForRead(APPID);
VariableDispenser.LockForRead(ZUMONAME);
VariableDispenser.GetVariables(out variables);
object adminKeyObj = variables[0].Value;
object appIdObj = variables[1].Value;
object zumoNameObj = variables[2].Value;
this.adminKey = adminKeyObj.ToString();
this.appId = appIdObj.ToString();
this.zumoName = zumoNameObj.ToString();
//this.dataTable = new DataTable()
//{
// Locale = CultureInfo.InvariantCulture
//};
//IDTSInput100 dTSInput = base.ComponentMetaData.InputCollection[0];
//IDTSExternalMetadataColumnCollection100 externalColumnCollection = dTSInput.ExternalMetadataColumnCollection;
//IDTSInputColumnCollection100 inputColumnCollection = dTSInput.InputColumnCollection;
//int count = inputColumnCollection.Count;
//this.tableColumns = new DataColumn[count];
//IDTSExternalMetadataColumn100[] externalMetadataColumnArray = new IDTSExternalMetadataColumn100[count];
//this.bufferIdxs = new int[count];
//for (int i = 0; i < count; i++)
//{
// IDTSInputColumn100 inputColumn = inputColumnCollection[i];
// IDTSExternalMetadataColumn100 externalMetadataColumn = externalColumnCollection.FindObjectByID(inputColumn.ExternalMetadataColumnID);
// externalMetadataColumnArray[i] = externalMetadataColumn;
// DataType dataType = inputColumn.DataType;
// bool flag5 = false;
// dataType = PipelineComponent.ConvertBufferDataTypeToFitManaged(dataType, ref flag5);
// Type dataRecordType = PipelineComponent.BufferTypeToDataRecordType(dataType);
// this.tableColumns[i] = new DataColumn(externalMetadataColumn.Name, dataRecordType);
// int lineageId = inputColumn.LineageID;
// try
// {
// this.bufferIdxs[i] = base.BufferManager.FindColumnByLineageID(dTSInput.Buffer, lineageId);
// }
// catch (Exception ex)
// {
// Microsoft.SqlServer.Dts.ManagedMsg.ErrorSupport errorSupport2 = base.ErrorSupport;
// object[] name = new object[] { lineageId, externalMetadataColumn.Name };
// errorSupport2.FireErrorWithArgs(-1071610795, out flag4, name);
// throw new PipelineComponentHResultException(-1071610795);
// }
//}
//this.dataTable.Columns.AddRange(this.tableColumns);
IDTSCustomProperty100 item = base.ComponentMetaData.CustomPropertyCollection["TableName"];
string str;
if (item.Value == null)
{
str = null;
}
else
{
str = item.Value.ToString().Trim();
}
this.tableName = str;
switch (this.tableName)
{
case "charges":
chargesServiceList = GetAllCharges();
ComponentMetaData.FireInformation(0, ComponentMetaData.Name, string.Format("Got {0} charges from the online service.", chargesServiceList.Count), "", 0, ref pbCancel);
chargesToSave = new List <Charge>();
break;
case "holds":
holdsServiceList = GetAllHolds();
ComponentMetaData.FireInformation(0, ComponentMetaData.Name, string.Format("Got {0} holds from the online service.", holdsServiceList.Count), "", 0, ref pbCancel);
holdsToSave = new List <Hold>();
break;
case "inmates":
default:
try
{
inmatesServiceList = GetAllInmates();
}
catch (Exception)
{
RetryGetAllInmates();
}
ComponentMetaData.FireInformation(0, ComponentMetaData.Name, string.Format("Got {0} inmates from the online service.", inmatesServiceList.Count), "", 0, ref pbCancel);
inmatesToSave = new List <Inmate>();
break;
}
}
// 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();
}
}
public override void PreExecute()
{
bool debugging = false;
IDTSVariables100 vars = null;
try
{
VariableDispenser.LockOneForRead(JSON_SOURCE_DEBUG_VAR, ref vars);
object o = vars[JSON_SOURCE_DEBUG_VAR].Value;
if (o != null)
{
if ((bool)o)
{
debugging = true;
}
}
}
catch (Exception e) {
//Do nothing
bool fireAgain = false;
ComponentMetaData.FireInformation(0, ComponentMetaData.Name, "wk_debug variable cannot be found. I won't stop to let debug attachment.", null, 0, ref fireAgain);
}
finally
{
if (vars != null)
{
vars.Unlock();
}
}
if (debugging)
{
MessageBox.Show("Start Debugger");
}
TransformationModel m = GetModel();
_opt = new ParallelOptions();
_opt.MaxDegreeOfParallelism = 4;
bool cancel = false;
// Carico i dettagli dal model
try{
m = GetModel();
}catch (ModelNotFoundException ex) {
ComponentMetaData.FireError(RUNTIME_ERROR_MODEL_INVALID, ComponentMetaData.Name, "Invalid Metadata for this component.", null, 0, out cancel);
return;
}
// Salva il mapping in un array locale
_iomap = m.IoMap.ToArray <IOMapEntry>();
// Salva una copia locale del percorso cui attingere l'array
_pathToArray = m.JsonObjectRelativePath;
// Genera un dizionario ad accesso veloce per il nome della colonna per i dati json: mappo nome colonna - Indice della colonna nella riga.
// Questo dizionario è usato solo per il JSON, mentre per gli input standard non facciamo il lookup, ma usiamo l'indice del buffer.
_startOfJsonColIndex = ComponentMetaData.InputCollection[0].InputColumnCollection.Count;
_outColsMaps = new Dictionary <string, int>();
foreach (IOMapEntry e in _iomap)
{
bool found = false;
for (var i = 0; i < _iomap.Count(); i++)
{
var col = ComponentMetaData.OutputCollection[0].OutputColumnCollection[_startOfJsonColIndex + i];
if (col.Name == e.OutputColName)
{
found = true;
int colIndex = BufferManager.FindColumnByLineageID(ComponentMetaData.OutputCollection[0].Buffer, col.LineageID);
_outColsMaps.Add(e.OutputColName, colIndex);
break;
}
}
if (!found)
{
// Una colonna del model non ha trovato il corrispettivo nel componente attuale
ComponentMetaData.FireError(RUNTIME_ERROR_MODEL_INVALID, ComponentMetaData.Name, "The component is unable to locate the column named " + e.OutputColName + " inside the component metadata. Please review the component.", null, 0, out cancel);
return;
}
}
_inputColIndex = BufferManager.FindColumnByLineageID(ComponentMetaData.InputCollection[0].Buffer, ComponentMetaData.InputCollection[0].InputColumnCollection[GetModel().InputColumnName].LineageID);
// Check if ww should take care of date parsing
if (!m.ParseDates)
{
_dateParsePolicy = DateParseHandling.None;
}
}
