//Methode de récupération d'une ligne de la Table Azure puis de création de colonnes SSIS Typées
public void InitializeOutput()
{
//Suppression des colonnes existantes pour rechargement
ComponentMetaData.OutputCollection[0].OutputColumnCollection.RemoveAll();
//Récupération de la chaine de connexion au stockage blob Azure
string storageConnectionString = (string)this.ComponentMetaData.CustomPropertyCollection["StorageConnectionString"].Value;
string tableName = (string)this.ComponentMetaData.CustomPropertyCollection["TableName"].Value;
//Connexion au stockage
CloudStorageAccount cloudStorageAccount = CloudStorageAccount.Parse(storageConnectionString);
CloudTableClient tableClient = cloudStorageAccount.CreateCloudTableClient();
CloudTable table = tableClient.GetTableReference(tableName);
//Récupération de la première ligne
TableQuery <DynamicTableEntity> query = new TableQuery <DynamicTableEntity>();
query.TakeCount = 1;
var row = table.ExecuteQuery(query).FirstOrDefault();
if (row != null)
{
var properties = row.Properties;
foreach (var prop in properties)
{
//Création d'une colonne dans le composant source
IDTSOutputColumn100 newOutputCol = ComponentMetaData.OutputCollection[0].OutputColumnCollection.New();
//Le nom de la colonne est identique à la source
newOutputCol.Name = prop.Key;
//Le Type de données de la colonne est convertie en type SSIS puis attribué à la colonne du composant SSIS
newOutputCol.SetDataTypeProperties(this.GetSsisType(prop.Value).Key, this.GetSsisType(prop.Value).Value, 0, 0, 0);
}
}
}
/// <summary>
/// Initalizes a new instance of the RowHashTransformation.
/// </summary>
public override void ProvideComponentProperties()
{
base.ProvideComponentProperties();
// Name the input.
IDTSInput100 input = ComponentMetaData.InputCollection[0];
input.Name = INPUT_NAME;
// Create one synchronized output, and marks it as a built-in output, which cannot be removed.
IDTSCustomProperty100 isInternal;
IDTSOutput100 output = ComponentMetaData.OutputCollection[0];
output.Name = OUTPUT_NAME;
output.SynchronousInputID = ComponentMetaData.InputCollection[INPUT_NAME].ID;
isInternal = output.CustomPropertyCollection.New();
isInternal.Name = IS_INTERNAL_CUSTOM_PROPERTY_NAME;
isInternal.State = DTSPersistState.PS_PERSISTASDEFAULT;
isInternal.TypeConverter = typeof(Boolean).AssemblyQualifiedName;
isInternal.Value = true;
// Create the output column to hold the hash value, and marks it as a built-in output column, which cannot be removed.
IDTSOutputColumn100 rowHashColumn = output.OutputColumnCollection.New();
rowHashColumn.Name = ROW_HASH_COLUMN_NAME;
// The size of a SHA1 hash value is 20 bytes.
rowHashColumn.SetDataTypeProperties(DataType.DT_BYTES, 20, 0, 0, 0);
isInternal = rowHashColumn.CustomPropertyCollection.New();
isInternal.Name = IS_INTERNAL_CUSTOM_PROPERTY_NAME;
isInternal.State = DTSPersistState.PS_PERSISTASDEFAULT;
isInternal.TypeConverter = typeof(Boolean).AssemblyQualifiedName;
isInternal.Value = true;
}
/// <summary>
/// Processes the only supported input.
/// </summary>
/// <param name="inputID"></param>
/// <param name="buffer"></param>
public override void ProcessInput(int inputID, PipelineBuffer buffer)
{
IDTSInput100 input = ComponentMetaData.InputCollection.GetObjectByID(inputID);
IDTSOutput100 output = ComponentMetaData.OutputCollection[OUTPUT_NAME];
IDTSOutputColumn100 rowHashColumn = output.OutputColumnCollection[ROW_HASH_COLUMN_NAME];
Int32 rowHashColumnIndex = BufferManager.FindColumnByLineageID(input.Buffer, rowHashColumn.LineageID);
// Processes each incoming pipeline row.
while (buffer.NextRow())
{
List <Byte[]> columnBytesCollection = new List <Byte[]>();
// Version 2: In the order of the input columns specified by the Ordinal custom property ...
foreach (IDTSInputColumn100 inputColumn in _columnCollection.Values)
{
Int32 inputColumnIndex = input.InputColumnCollection.GetObjectIndexByID(inputColumn.ID);
// ... retrieves the binary representation of the column value.
columnBytesCollection.Add(this.GetColumnValueBytes(buffer, inputColumn, inputColumnIndex));
}
// Writes the current row hash value to the built-in output column.
buffer.SetBytes(rowHashColumnIndex, this.ComputeHash(columnBytesCollection));
}
}
public void AssociateWithOutputColumn(string outputColumnName)
{
ISOutputColumn outCol = new ISOutputColumn(ParentComponent, Output.Name, outputColumnName, RowDisposition.RD_FailComponent, RowDisposition.RD_FailComponent);
outCol.SetDataTypeProperties(DataType, Length, Precision, Scale, CodePage);
outCol.OutputColumn.ExternalMetadataColumnID = ID;
IDTSOutput100 errorOutput = null;
for (int o = 0; o < ParentComponent.ComponentMetaData.OutputCollection.Count; o++)
{
if (ParentComponent.ComponentMetaData.OutputCollection[o].IsErrorOut == true)
{
errorOutput = ParentComponent.ComponentMetaData.OutputCollection[o];
}
}
bool errorColExists = false;
int errorColCount = errorOutput.OutputColumnCollection.Count;
for (int e = 0; e < errorColCount; e++)
{
if (errorOutput.OutputColumnCollection[e].Name == outputColumnName)
{
errorColExists = true;
}
}
if (!(errorColExists))
{
IDTSOutputColumn100 o = errorOutput.OutputColumnCollection.NewAt(errorColCount - 2);
o.Name = outputColumnName;
}
}
public IDTSOutputColumn100 ModifyComp_DerivedCol_AddCol(IDTSComponentMetaData100 Comp,
string DCName,
string DCExpression,
DataType DTDataType,
int DCDTLength = 0,
int DCDTPrecision = 0,
int DCDTScale = 0,
int DCDTCodePage = 0
)
{
IDTSOutputColumn100 Col = Comp.OutputCollection[0].OutputColumnCollection.New();
Col.Name = DCName;
Col.SetDataTypeProperties(DTDataType, DCDTLength, DCDTPrecision, DCDTScale, DCDTCodePage);
Col.ExternalMetadataColumnID = 0;
Col.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent;
Col.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent;
IDTSCustomProperty100 propExp = Col.CustomPropertyCollection.New();
propExp.Name = "Expression";
propExp.Value = DCExpression;
IDTSCustomProperty100 propFrExp = Col.CustomPropertyCollection.New();
propFrExp.Name = "FriendlyExpression";
propFrExp.Value = DCExpression;
return(Col);
}
/// <summary>
/// Constructs an output column and adds it to the provided output, using the column metadata from the provided BSON element.
/// </summary>
/// <param name="output"></param>
/// <param name="bsonElement"></param>
/// <returns></returns>
private IDTSOutputColumn100 BuildOutputColumn(IDTSOutput100 output, BsonElement bsonElement)
{
IDTSOutputColumn100 outColumn = output.OutputColumnCollection.New();
// Set the properties of the output column.
outColumn.Name = bsonElement.Name;
DataType dt = GetColumnDataType(bsonElement.Value);
int length = 0;
int codepage = 0;
if (dt == DataType.DT_STR)
{
length = 256;
codepage = 1252;
}
outColumn.SetDataTypeProperties(dt, length, 0, 0, codepage);
// Setting these values enables the end-user to configure the error behavior on a per-column basis
if (!output.IsErrorOut)
{
outColumn.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent;
outColumn.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent;
}
return(outColumn);
}
public override IDTSInputColumn100 SetUsageType(int inputID, IDTSVirtualInput100 virtualInput, int lineageID, DTSUsageType usageType)
{
IDTSInputColumn100 inputColumn = null;
IDTSOutput100 output = this.ComponentMetaData.OutputCollection[0];
IDTSOutputColumnCollection100 outputColumnCollection = output.OutputColumnCollection;
IDTSInput100 input = this.ComponentMetaData.InputCollection[0];
IDTSInputColumnCollection100 inputColumnCollection = input.InputColumnCollection;
if (usageType == DTSUsageType.UT_IGNORED)
{
inputColumn = inputColumnCollection.GetInputColumnByLineageID(lineageID);
IDTSOutputColumn100 outputColumn = outputColumnCollection.FindObjectByID(inputColumn.MappedColumnID);
if (outputColumn != null)
{
outputColumnCollection.RemoveObjectByID(outputColumn.ID);
}
inputColumn = base.SetUsageType(inputID, virtualInput, lineageID, usageType);
}
else
{
inputColumn = base.SetUsageType(inputID, virtualInput, lineageID, usageType);
IDTSOutputColumn100 outputColumn = outputColumnCollection.New();
outputColumn.Name = inputColumn.Name;
outputColumn.SetDataTypeProperties(inputColumn.DataType, inputColumn.Length, inputColumn.Precision, inputColumn.Scale, inputColumn.CodePage);
outputColumn.MappedColumnID = inputColumn.LineageID;
inputColumn.MappedColumnID = outputColumn.LineageID;
AddInputColumnProperty(inputColumn, GeoColumnPropName, "True if the column holds geometry objects to be joined together.", false);
AddInputColumnProperty(inputColumn, GroupByColumnPropName, "True if the column holds group by values.", false);
}
return(inputColumn);
}
public void TestOutputColumn_Timecode_Missing()
{
Microsoft.SqlServer.Dts.Runtime.Package package = new Microsoft.SqlServer.Dts.Runtime.Package();
Executable exec = package.Executables.Add("STOCK:PipelineTask");
Microsoft.SqlServer.Dts.Runtime.TaskHost thMainPipe = exec as Microsoft.SqlServer.Dts.Runtime.TaskHost;
MainPipe dataFlowTask = thMainPipe.InnerObject as MainPipe;
ComponentEventHandler events = new ComponentEventHandler();
dataFlowTask.Events = DtsConvert.GetExtendedInterface(events as IDTSComponentEvents);
IDTSComponentMetaData100 speechToText = dataFlowTask.ComponentMetaDataCollection.New();
speechToText.ComponentClassID = typeof(Martin.SQLServer.Dts.SSISSpeechToText).AssemblyQualifiedName;
CManagedComponentWrapper speechToTextInstance = speechToText.Instantiate();
speechToTextInstance.ProvideComponentProperties();
speechToText.CustomPropertyCollection[Utility.SubscriptionKeyPropName].Value = "NotTheDefault";
// Before this is default setup for a clean component
speechToText.OutputCollection[0].OutputColumnCollection.RemoveObjectByID(speechToText.OutputCollection[0].OutputColumnCollection[Utility.OutputTimecodeColumnName].ID);
IDTSOutputColumn100 tempCol = speechToText.OutputCollection[0].OutputColumnCollection.New();
tempCol.Name = "TempCol";
tempCol.SetDataTypeProperties(DataType.DT_STR, 10, 0, 0, 1252);
DTSValidationStatus actual = speechToTextInstance.Validate();
DTSValidationStatus expected = DTSValidationStatus.VS_ISCORRUPT;
Assert.AreEqual(expected, actual);
Assert.AreEqual("[Error] SSIS Speech To Text: Required Output Column Timecode is missing.", events.errorMessages[0]);
}
/// <summary>
/// Called prior to ProcessInput, the buffer column index, index of the character to change, and the operation
/// for each column in the input collection is read, and stored.
/// </summary>
public override void PreExecute()
{
//bool flag = false;
IDTSInput100 input = base.ComponentMetaData.InputCollection[0];
inputColumnInfos = new ColumnInfo[input.InputColumnCollection.Count];
for (var i = 0; i < input.InputColumnCollection.Count; i++)
{
IDTSInputColumn100 column = input.InputColumnCollection[i];
inputColumnInfos[i] = new ColumnInfo
{
bufferColumnIndex = BufferManager.FindColumnByLineageID(input.Buffer, column.LineageID),
columnDisposition = column.ErrorRowDisposition,
lineageID = column.LineageID,
dataType = column.DataType.ToString()
};
}
IDTSOutput100 output = base.ComponentMetaData.OutputCollection[0];
outputColumnInfos = new ColumnInfo[output.OutputColumnCollection.Count];
for (var j = 0; j < output.OutputColumnCollection.Count; j++)
{
IDTSOutputColumn100 column2 = output.OutputColumnCollection[j];
outputColumnInfos[j] = new ColumnInfo
{
bufferColumnIndex = BufferManager.FindColumnByLineageID(input.Buffer, column2.LineageID),
columnDisposition = column2.ErrorRowDisposition,
lineageID = column2.LineageID,
dataType = column2.DataType.ToString()
};
}
//IDTSCustomProperty100 HashDualSHA1Algorithm = ComponentMetaData.CustomPropertyCollection["HashDual"];
}
/// <summary>
/// The add xml column.
/// </summary>
/// <param name="output">
/// The output.
/// </param>
private void AddXmlColumn(IDTSOutput100 output)
{
IDTSOutputColumn100 column = output.OutputColumnCollection.New();
column.Name = "Xml";
column.SetDataTypeProperties(DataType.DT_NTEXT, 0, 0, 0, 0);
}
public void TestSetOutputColumnDefaults()
{
Microsoft.SqlServer.Dts.Runtime.Package package = new Microsoft.SqlServer.Dts.Runtime.Package();
Executable exec = package.Executables.Add("STOCK:PipelineTask");
Microsoft.SqlServer.Dts.Runtime.TaskHost thMainPipe = exec as Microsoft.SqlServer.Dts.Runtime.TaskHost;
MainPipe dataFlowTask = thMainPipe.InnerObject as MainPipe;
IDTSComponentMetaData100 textFileSplitter = dataFlowTask.ComponentMetaDataCollection.New();
ComponentEventHandler events = new ComponentEventHandler();
dataFlowTask.Events = DtsConvert.GetExtendedInterface(events as IDTSComponentEvents);
textFileSplitter.Name = "Row Splitter Test";
textFileSplitter.ComponentClassID = typeof(Martin.SQLServer.Dts.TextFileSplitter).AssemblyQualifiedName;
CManagedComponentWrapper instance = textFileSplitter.Instantiate();
instance.ProvideComponentProperties();
IDTSOutput100 output = textFileSplitter.OutputCollection.New();
IDTSOutputColumn100 actual = output.OutputColumnCollection.New();
ManageColumns.SetOutputColumnDefaults(actual, 1252);
Assert.AreEqual(DataType.DT_STR, actual.DataType, "DataType is wrong");
Assert.AreEqual(255, actual.Length, "Length is wrong");
Assert.AreEqual(DTSRowDisposition.RD_NotUsed, actual.ErrorRowDisposition, "Row Disposition is wrong");
Assert.AreEqual(DTSRowDisposition.RD_NotUsed, actual.TruncationRowDisposition, "Truncate Disposition is wrong");
}
public override void SetOutputColumnDataTypeProperties(int iOutputID, int iOutputColumnID, DataType eDataType, int iLength, int iPrecision, int iScale, int iCodePage)
{
IDTSOutputCollection100 outputColl = ComponentMetaData.OutputCollection;
IDTSOutput100 output = outputColl.GetObjectByID(iOutputID);
if (output != null)
{
if (output.IsErrorOut)
{
this.PostErrorAndThrow(MessageStrings.CantChangeErrorOutputProperties);
}
else
{
IDTSOutputColumnCollection100 columnColl = output.OutputColumnCollection;
IDTSOutputColumn100 column = columnColl.GetObjectByID(iOutputColumnID);
if (column != null)
{
if (ValidateSupportedDataTypes(eDataType) == DTSValidationStatus.VS_ISVALID)
{
column.SetDataTypeProperties(eDataType, iLength, iPrecision, iScale, iCodePage);
}
else
{
throw new COMException(string.Empty, E_FAIL);
}
}
}
}
}
//=================================================================================================
private bool AreOutputColumnsValid()
{
// Get the output and the WQL schema.
IDTSOutput100 output = ComponentMetaData.OutputCollection[0];
PropertyDataCollection wmiColumns = GetWmiColumns();
// Output columns should be subset of external columns
if (output.OutputColumnCollection.Count > wmiColumns.Count)
{
return(false);
}
// Otherwise, walk the WQL columns and validate each column
for (int x = 0; x < output.OutputColumnCollection.Count; x++)
{
IDTSOutputColumn100 column = output.OutputColumnCollection[x];
PropertyData wmiCol = GetWmiColumnByName(wmiColumns, column.Name);
// Does the column exist, by name, in the WQL schema?
if (wmiCol == null || !WmiColumnMatchesOutputColumn(wmiCol, column))
{
// Fire a warning before returning
ComponentMetaData.FireWarning(0, ComponentMetaData.Name, "The output column " + column.IdentificationString + " does not match the data source.", "", 0);
return(false);
}
}
// If this code is reached, all of the columns are valid.
return(true);
}
/// <summary>
/// カスタムプロパティであるInputLineageIDを取得する
/// </summary>
/// <param name="outputColumn"></param>
/// <returns></returns>
private int GetInputLineageID(IDTSOutputColumn100 outputColumn)
{
return(outputColumn.CustomPropertyCollection
.Cast <IDTSCustomProperty100>()
.Where(x => x.Name == "InputLineageID")
.First()
.Value);
}
private static void AddColumn(IDTSDesigntimeComponent100 designtimeComponent, IDTSConnectionManagerFlatFileColumn100 column, int outputID, int columnIndex, int codePage)
{
IDTSName100 nameID = column as IDTSName100;
IDTSOutputColumn100 outputColumn = designtimeComponent.InsertOutputColumnAt(outputID, columnIndex, nameID.Name, nameID.Description);
int codePageValue = (column.DataType == DataType.DT_STR || column.DataType == DataType.DT_TEXT) ? codePage : 0;
designtimeComponent.SetOutputColumnDataTypeProperties(outputID, outputColumn.ID, column.DataType, column.MaximumWidth, column.DataPrecision, column.DataScale, codePageValue);
}
public override void ProvideComponentProperties()
{
base.ProvideComponentProperties();
// Data Profile File Name
IDTSCustomProperty100 dataProfileFileName = ComponentMetaData.CustomPropertyCollection.New();
dataProfileFileName.Name = DATA_PROFILE_FILE_NAME_PROPERTY_NAME;
dataProfileFileName.Description = DATA_PROFILE_FILE_NAME_PROPERTY_DESCRIPTION;
dataProfileFileName.State = DTSPersistState.PS_PERSISTASCDATA;
dataProfileFileName.TypeConverter = typeof(String).AssemblyQualifiedName;
dataProfileFileName.Value = String.Empty;
// Data Profile Column Name
IDTSCustomProperty100 dataProfileColumnName = ComponentMetaData.CustomPropertyCollection.New();
dataProfileColumnName.Name = DATA_PROFILE_COLUMN_NAME_PROPERTY_NAME;
dataProfileColumnName.Description = DATA_PROFILE_COLUMN_NAME_PROPERTY_DESCRIPTION;
dataProfileColumnName.State = DTSPersistState.PS_DEFAULT;
dataProfileColumnName.TypeConverter = typeof(String).AssemblyQualifiedName;
dataProfileColumnName.Value = String.Empty;
// Input
IDTSInput100 input = ComponentMetaData.InputCollection[0];
input.Name = INPUT_NAME;
// Input Column Name
IDTSCustomProperty100 inputColumnName = input.CustomPropertyCollection.New();
inputColumnName.Name = INPUT_COLUMN_NAME;
inputColumnName.Description = INPUT_COLUMN_DESCRIPTION;
inputColumnName.State = DTSPersistState.PS_DEFAULT;
inputColumnName.TypeConverter = typeof(String).AssemblyQualifiedName;
inputColumnName.Value = String.Empty;
IDTSCustomProperty100 isInternal;
// Synchronous Output
IDTSOutput100 output = ComponentMetaData.OutputCollection[0];
output.Name = OUTPUT_NAME;
output.SynchronousInputID = ComponentMetaData.InputCollection[0].ID;
isInternal = output.CustomPropertyCollection.New();
isInternal.Name = IS_INTERNAL_OBJECT_PROPERTY_NAME;
isInternal.State = DTSPersistState.PS_DEFAULT;
isInternal.TypeConverter = typeof(Boolean).AssemblyQualifiedName;
isInternal.Value = true;
// Output column
IDTSOutputColumn100 isVaildEmailColumn = output.OutputColumnCollection.New();
isVaildEmailColumn.Name = IS_VALID_COLUMN_NAME;
isVaildEmailColumn.Description = IS_VALID_COLUMN_DESCRIPTION;
isVaildEmailColumn.SetDataTypeProperties(DataType.DT_BOOL, 0, 0, 0, 0);
isInternal = isVaildEmailColumn.CustomPropertyCollection.New();
isInternal.Name = IS_INTERNAL_OBJECT_PROPERTY_NAME;
isInternal.State = DTSPersistState.PS_DEFAULT;
isInternal.TypeConverter = typeof(Boolean).AssemblyQualifiedName;
isInternal.Value = true;
}
public override void SetOutputColumnDataTypeProperties(int iOutputID, int iOutputColumnID, DataType eDataType, int iLength, int iPrecision, int iScale, int iCodePage)
{
IDTSOutputCollection100 outputColl = ComponentMetaData.OutputCollection;
IDTSOutput100 output = outputColl.GetObjectByID(iOutputID);
IDTSOutputColumnCollection100 columnColl = output.OutputColumnCollection;
IDTSOutputColumn100 column = columnColl.GetObjectByID(iOutputColumnID);
column.SetDataTypeProperties(eDataType, iLength, iPrecision, iScale, iCodePage);
}
/// <summary>
/// Add external output columns for offline support
/// </summary>
/// <param name="output"></param>
/// <param name="outputColumn"></param>
private void CreateExternalMetaDataColumn(IDTSOutput100 output, IDTSOutputColumn100 outputColumn)
{
IDTSExternalMetadataColumn100 externalColumn = output.ExternalMetadataColumnCollection.New();
externalColumn.Name = outputColumn.Name;
externalColumn.Precision = outputColumn.Precision;
externalColumn.Length = outputColumn.Length;
externalColumn.DataType = outputColumn.DataType;
externalColumn.Scale = outputColumn.Scale;
outputColumn.ExternalMetadataColumnID = externalColumn.ID;
}
private void AddOutputColumns(IEnumerable <IOMapEntry> IoMap, IEnumerable <string> copyColNames)
{
// Reconfigure outputs:
_md.OutputCollection[0].Name = "JSON Source Output";
_md.OutputCollection[0].OutputColumnCollection.RemoveAll();
var input = _md.InputCollection[ComponentConstants.NAME_INPUT_LANE_PARAMS];
// Add simple copy columns
foreach (var colname in copyColNames)
{
// Only add them if the inputlan is connected.
if (!input.IsAttached)
{
_md.FireWarning(0, _md.Name, string.Format("Cannot add outputcolumn for input {0} because input lane is not attached.", colname), null, 0);
return;
}
// Copy that column
var incol = input.InputColumnCollection[colname];
IDTSOutputColumn100 output = _md.OutputCollection[0].OutputColumnCollection.New();
output.SetDataTypeProperties(incol.DataType, incol.Length, incol.Precision, incol.Scale, incol.CodePage);
output.MappedColumnID = incol.LineageID;
output.Name = incol.Name;
}
// For each espected outputcolumn json derived, add the equivalent.
foreach (IOMapEntry e in IoMap)
{
if (e.InputFieldLen < 0)
{
// FIXME TODO: this must be done directly within the UI
_md.FireWarning(0, _md.Name, "A row of the IO configuration presents a negative value, which is forbidden.", null, 0);
}
IDTSOutputColumn100 col = _md.OutputCollection[0].OutputColumnCollection.New();
col.Name = e.OutputColName;
// There might be some possible errors regarding data lenght. We try to correct them here.
// If length > 4000 and type is string, put TEXT datatype.
if (e.OutputColumnType == Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_WSTR && (e.InputFieldLen > 4000 || e.InputFieldLen == 0))
{
// FIXME TODO: this must be done directly within the UI
// e.OutputColumnType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_TEXT;
_md.FireWarning(0, _md.Name, string.Format("Column {0} is supposed to be longer than 4000 chars, so DT_WSTR is not a suitable column type. Instead, DT_TEXT has been selected.", e.OutputColName), null, 0);
// TODO: parametrize the codepage
col.SetDataTypeProperties(Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_TEXT, 0, 0, 0, 1252);
}
else
{
col.SetDataTypeProperties(e.OutputColumnType, e.InputFieldLen, 0, 0, 0);
}
}
}
private void PropagateInputColumns(Dictionary <string, MyColumn> exColumns)
{
IDTSComponentMetaData100 comp = this.MetadataCollection;
CManagedComponentWrapper dcomp = comp.Instantiate();
IDTSInput100 input = comp.InputCollection[0];
IDTSVirtualInput100 vInput = input.GetVirtualInput();
IDTSVirtualInputColumnCollection100 vColumns = vInput.VirtualInputColumnCollection;
IDTSOutput100 output = comp.OutputCollection[0];
output.TruncationRowDisposition = DTSRowDisposition.RD_NotUsed;
output.ErrorRowDisposition = DTSRowDisposition.RD_NotUsed;
//create input columns for destination external
foreach (KeyValuePair <string, MyColumn> exColumn in exColumns)
{
int vColumnID = FindVirtualInputColumnId(vColumns, exColumn.Key);
if (vColumnID != 0)
{
//do type conversion
IDTSVirtualInputColumn100 vColumn = vInput.VirtualInputColumnCollection.GetVirtualInputColumnByLineageID(vColumnID);
if (vColumn.DataType != exColumn.Value.DataType)
{
dcomp.SetUsageType(input.ID, vInput, vColumnID, DTSUsageType.UT_READONLY);
IDTSOutputColumn100 oColumn = output.OutputColumnCollection.New();
oColumn.Name = exColumn.Key;
oColumn.SetDataTypeProperties(exColumn.Value.DataType, exColumn.Value.Length, exColumn.Value.Precision, exColumn.Value.Scale, exColumn.Value.CodePage);
oColumn.ExternalMetadataColumnID = 0;
oColumn.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent;
oColumn.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent;
IDTSCustomProperty100 property = oColumn.CustomPropertyCollection.New();
property.Name = "SourceInputColumnLineageID";
property.Value = vColumnID;
property = oColumn.CustomPropertyCollection.New();
property.Name = "FastParse";
property.Value = false;
//set of derived columns
m_converted.Add(oColumn.Name.ToLower(), oColumn.LineageID);
}
else
{
m_converted.Add(exColumn.Key.ToLower(), vColumnID);
}
}
else
{
//the column wasn't found if the Id is 0, so we'll print out a message and skip this row.
//PrintOutput.PrintToOutput("Converter: Could not map external column " + exColumn.Key + ". Skipping column.");
}
}
}
private static bool DoesColumnMetaDataMatch(IDTSOutputColumn100 column, IDTSExternalMetadataColumn100 exCol)
{
if (column.DataType == exCol.DataType &&
column.Precision == exCol.Precision &&
column.Length == exCol.Length &&
column.Scale == exCol.Scale)
{
return(true);
}
return(false);
}
private void AddHashColumn()
{
IDTSOutputColumn100 column = base.ComponentMetaData.OutputCollection[0].OutputColumnCollection.New();
column.Name = "Hash";
column.SetDataTypeProperties(DataType.DT_STR, 40, 0, 0, 1252);
IDTSCustomProperty100 property = column.CustomPropertyCollection.New();
property.Name = "Hash";
property.Description = "Hash SHA1 transformation result column indicator.";
property.Value = "Hash";
}
/// <summary>
/// Creates the new custom property to hold the output type.
/// </summary>
/// <param name="outputColumn">The output column to add the property to.</param>
/// <param name="outputType">provides the type of the output column</param>
static private void AddOutputTypeProperty(IDTSOutputColumn100 outputColumn, OutputTypeEnum outputType)
{
// Add the Output Type property
IDTSCustomProperty100 outputTypeProperty = outputColumn.CustomPropertyCollection.New();
outputTypeProperty.Description = "Stores the type of the output column";
outputTypeProperty.Name = Utility.OutputColumnOutputTypePropName;
outputTypeProperty.ContainsID = false;
outputTypeProperty.EncryptionRequired = false;
outputTypeProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NONE;
outputTypeProperty.TypeConverter = typeof(OutputTypeEnum).AssemblyQualifiedName;
outputTypeProperty.Value = outputType;
}
public override void SetOutputColumnDataTypeProperties(int outputID, int outputColumnID, Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType dataType, int length, int precision, int scale, int codePage)
{
IDTSOutputCollection100 outputColl = this.ComponentMetaData.OutputCollection;
IDTSOutput100 output = outputColl.GetObjectByID(outputID);
IDTSOutputColumnCollection100 columnColl = output.OutputColumnCollection;
IDTSOutputColumn100 column = columnColl.GetObjectByID(outputColumnID);
column.SetDataTypeProperties(dataType, length, precision, scale, codePage);
}
/// <summary>
/// Sets up all the base properties for the component to utilise
/// </summary>
public override void ProvideComponentProperties()
{
// Support resettability.
this.RemoveAllInputsOutputsAndCustomProperties();
// Let the base component add the input and output.
base.ProvideComponentProperties();
// Add Contact information etc.
ComponentMetaData.ContactInfo = "https://github.com/keif888/SSISSpeechToText/";
// Add all the required properties.
SSISSpeechToText.AddSubscriptionKeyProperty(this.ComponentMetaData);
SSISSpeechToText.AddOperationModeProperty(this.ComponentMetaData);
SSISSpeechToText.AddLanguageProperty(this.ComponentMetaData);
SSISSpeechToText.AddChannelSeparationProperty(this.ComponentMetaData);
SSISSpeechToText.AddShortPhraseUrlProperty(this.ComponentMetaData);
SSISSpeechToText.AddLongPhraseUrlProperty(this.ComponentMetaData);
// Name the input and output, and make the output asynchronous.
ComponentMetaData.InputCollection[0].Name = "Input";
IDTSOutput100 output = ComponentMetaData.OutputCollection[0];
output.Name = "SpeechOutput";
output.Description = "Speech analysis rows are directed to this output.";
output.SynchronousInputID = 0;
// Add the output channel column
IDTSOutputColumn100 outputChannelColumn = output.OutputColumnCollection.New();
outputChannelColumn.Name = Utility.OutputChannelColumnName;
outputChannelColumn.Description = "Contains which channel this speech came from";
outputChannelColumn.SetDataTypeProperties(DataType.DT_STR, 30, 0, 0, 1252);
AddOutputTypeProperty(outputChannelColumn, OutputTypeEnum.Channel);
// Add the output speech column
IDTSOutputColumn100 outputSpeechColumn = output.OutputColumnCollection.New();
outputSpeechColumn.Name = Utility.OutputSpeechColumnName;
outputSpeechColumn.Description = "Contains which channel this speech came from";
outputSpeechColumn.SetDataTypeProperties(DataType.DT_TEXT, 0, 0, 0, 1252);
AddOutputTypeProperty(outputSpeechColumn, OutputTypeEnum.Speech);
//Add the output timecodes column
IDTSOutputColumn100 outputTimecodesColumn = output.OutputColumnCollection.New();
outputTimecodesColumn.Name = Utility.OutputTimecodeColumnName;
outputTimecodesColumn.Description = "Contains the time that this fragment of speech started at";
outputTimecodesColumn.SetDataTypeProperties(DataType.DT_DBTIME2, 0, 0, 7, 0);
AddOutputTypeProperty(outputTimecodesColumn, OutputTypeEnum.Timecode);
}
protected override void ProcessBindingMappings(SsisEmitterContext context, MappedBinding mappedBinding, IDTSPath100 path)
{
if (mappedBinding != null && path != null)
{
foreach (AstDataflowColumnMappingNode map in mappedBinding.Mappings)
{
int lineageId;
var matchedOutput = TransformationUtility.FindOutputColumnByName(map.SourceName, path.StartPoint, true);
if (matchedOutput == null)
{
var matchedInput = TransformationUtility.FindVirtualInputColumnByName(map.SourceName, path.EndPoint, true);
if (matchedInput == null)
{
MessageEngine.Trace(_astUnionAllNode, Severity.Error, "V0145", "Could not find input column {0} for transformation {1}", map.SourceName, _astUnionAllNode.Name);
}
lineageId = matchedInput.LineageID;
}
else
{
lineageId = matchedOutput.LineageID;
}
IDTSInputColumn100 ic = TransformationUtility.FindInputColumnByName(map.SourceName, path.EndPoint, true);
if (ic == null)
{
ic = path.EndPoint.InputColumnCollection.New();
ic.Name = map.SourceName;
ic.LineageID = lineageId;
}
IDTSOutputColumn100 oc = TransformationUtility.FindOutputColumnByName(map.TargetName, OutputPath, true);
if (oc == null)
{
oc = OutputPath.OutputColumnCollection.New();
oc.Name = map.TargetName;
oc.SetDataTypeProperties(ic.DataType, ic.Length, ic.Precision, ic.Scale, ic.CodePage);
}
IDTSCustomProperty100 cp = TransformationUtility.FindCustomPropertyByName("OutputColumnLineageID", ic.CustomPropertyCollection, true);
if (cp == null)
{
cp = ic.CustomPropertyCollection.New();
cp.Name = "OutputColumnLineageID";
}
cp.Value = oc.LineageID;
}
}
}
//Run Time - Pre Execute identifying the input columns in this component from Buffer Manager
public override void PreExecute()
{
IDTSInput100 input = ComponentMetaData.InputCollection[0];
inputBufferColumnIndex = new int[input.InputColumnCollection.Count];
_phneNumberColumnInfo = new PhonenumberColumnInfo();
_phneNumberColumnInfo.UsesIsoAsString = _phoneNumberIsoLinage == default;
_phneNumberColumnInfo.PhoneNumberIsoString = _phoneNumberIsoCode;
for (int x = 0; x < input.InputColumnCollection.Count; x++)
{
IDTSInputColumn100 column = input.InputColumnCollection[x];
inputBufferColumnIndex[x] = BufferManager.FindColumnByLineageID(input.Buffer, column.LineageID);
if (column.LineageID == _phonenumberLinage)
{
_phneNumberColumnInfo.PhoneNumberBufferIndex = x;
}
else if (column.LineageID == _phoneNumberIsoLinage)
{
_phneNumberColumnInfo.PhoneNumberIsoBufferIndex = x;
}
}
IDTSOutput100 output = ComponentMetaData.OutputCollection[0];
outputBufferColumnIndex = new int[output.OutputColumnCollection.Count];
for (int x = 0; x < output.OutputColumnCollection.Count; x++)
{
IDTSOutputColumn100 outcol = output.OutputColumnCollection[x];
var colreference = _outputColumnList.FirstOrDefault(c => c.ColumnName == outcol.Name);
if (colreference != null)
{
colreference.ColumnIndex = BufferManager.FindColumnByLineageID(input.Buffer, outcol.LineageID);
}
//This is the key - synchronous output does not appear in output buffer, but in input buffer
outputBufferColumnIndex[x] = BufferManager.FindColumnByLineageID(input.Buffer, outcol.LineageID);
}
_outputId = output.ID;
foreach (IDTSOutput100 outputcoll in ComponentMetaData.OutputCollection)
{
if (outputcoll.IsErrorOut)
{
_errorOutId = outputcoll.ID;
break;
}
}
}
public override void OnInputPathAttached(int inputID)
{
IDTSOutput100 defaultOutput = ComponentMetaData.OutputCollection[0];
IDTSInput100 input = ComponentMetaData.InputCollection.GetObjectByID(inputID);
IDTSVirtualInput100 vInput = input.GetVirtualInput();
foreach (IDTSVirtualInputColumn100 vCol in vInput.VirtualInputColumnCollection)
{
IDTSOutputColumn100 outCol = defaultOutput.OutputColumnCollection.New();
outCol.Name = input.Name + "." + vCol.Name;
outCol.SetDataTypeProperties(vCol.DataType, vCol.Length, vCol.Precision, vCol.Scale, vCol.CodePage);
SetUsageType(inputID, vInput, vCol.LineageID, DTSUsageType.UT_READONLY);
}
}
public static string GetTooltipString(object dataflowColumn)
{
if (dataflowColumn is IDTSVirtualInputColumn100)
{
IDTSVirtualInputColumn100 column = dataflowColumn as IDTSVirtualInputColumn100;
return(FormatTooltipText(
column.Name,
column.DataType.ToString(),
column.Length.ToString(CultureInfo.CurrentCulture), // Changed from CurrentUICulture
column.Scale.ToString(CultureInfo.CurrentCulture),
column.Precision.ToString(CultureInfo.CurrentCulture),
column.CodePage.ToString(CultureInfo.CurrentCulture),
column.SourceComponent));
}
else if (dataflowColumn is IDTSInputColumn100)
{
IDTSInputColumn100 column = dataflowColumn as IDTSInputColumn100;
return(FormatTooltipText(
column.Name,
column.DataType.ToString(),
column.Length.ToString(CultureInfo.CurrentCulture),
column.Scale.ToString(CultureInfo.CurrentCulture),
column.Precision.ToString(CultureInfo.CurrentCulture),
column.CodePage.ToString(CultureInfo.CurrentCulture)));
}
else if (dataflowColumn is IDTSOutputColumn100)
{
IDTSOutputColumn100 column = dataflowColumn as IDTSOutputColumn100;
return(FormatTooltipText(
column.Name,
column.DataType.ToString(),
column.Length.ToString(CultureInfo.CurrentCulture),
column.Scale.ToString(CultureInfo.CurrentCulture),
column.Precision.ToString(CultureInfo.CurrentCulture),
column.CodePage.ToString(CultureInfo.CurrentCulture)));
}
else if (dataflowColumn is IDTSExternalMetadataColumn100)
{
IDTSExternalMetadataColumn100 column = dataflowColumn as IDTSExternalMetadataColumn100;
return(FormatTooltipText(
column.Name,
column.DataType.ToString(),
column.Length.ToString(CultureInfo.CurrentCulture),
column.Scale.ToString(CultureInfo.CurrentCulture),
column.Precision.ToString(CultureInfo.CurrentCulture),
column.CodePage.ToString(CultureInfo.CurrentCulture)));
}
return(string.Empty);
}
public static IDTSOutputColumn100 AddKeyOutputColumns(IDTSOutput100 keyRecords)
{
IDTSOutputColumnCollection100 outputColumnCollection = keyRecords.OutputColumnCollection;
IDTSOutputColumn100 outputColumn = outputColumnCollection.NewAt(0);
outputColumn.Name = MessageStrings.KeyRecordKeyColumnName;
outputColumn.SetDataTypeProperties(DataType.DT_GUID, 0, 0, 0, 0);
outputColumn.Description = MessageStrings.KeyRecordKeyColumnDescription;
ManageProperties.AddOutputColumnProperties(outputColumn.CustomPropertyCollection);
ManageProperties.SetPropertyValue(outputColumn.CustomPropertyCollection, ManageProperties.usageOfColumn, Utilities.usageOfColumnEnum.Key);
ManageProperties.SetPropertyValue(outputColumn.CustomPropertyCollection, ManageProperties.keyOutputColumnID, -2);
return(outputColumn);
}
private DTSValidationStatus ValidateOutputColumn(IDTSOutputColumn100 outputColumn, DTSValidationStatus returnStatus)
{
returnStatus = propertiesManager.ValidateProperties(outputColumn.CustomPropertyCollection, returnStatus);
if (returnStatus != DTSValidationStatus.VS_ISCORRUPT)
{
DTSValidationStatus newValidationStatus = ValidateSupportedDataTypes(outputColumn.DataType);
returnStatus = CommonUtils.CompareValidationValues(returnStatus, newValidationStatus);
}
return returnStatus;
}
//=================================================================================================
private bool WmiColumnMatchesOutputColumn( PropertyData wmiCol, IDTSOutputColumn100 column )
{
DataType dt;
int length;
int scale;
int precision;
int codePage;
GetWmiColumnProperties( wmiCol, out dt, out length, out scale, out precision, out codePage);
if (dt == column.DataType && length == column.Length && scale == column.Scale && codePage == column.CodePage)
{
return true;
}
return false;
}
private static bool DoesColumnMetaDataMatch(IDTSOutputColumn100 column, IDTSExternalMetadataColumn100 exCol)
{
if (column.DataType == exCol.DataType
&& column.Precision == exCol.Precision
&& column.Length == exCol.Length
&& column.Scale == exCol.Scale)
{
return true;
}
return false;
}
private void CreateExternalMetaDataColumn(IDTSExternalMetadataColumnCollection100 output, IDTSOutputColumn100 outputColumn)
{
// Set the properties of the external metadata column.
IDTSExternalMetadataColumn100 externalColumn = output.New();
externalColumn.Name = outputColumn.Name;
externalColumn.Precision = outputColumn.Precision;
externalColumn.Length = outputColumn.Length;
externalColumn.DataType = outputColumn.DataType;
externalColumn.Scale = outputColumn.Scale;
// Map the external column to the output column.
outputColumn.ExternalMetadataColumnID = externalColumn.ID;
}
private void PopulateExternalMetadataColumn(IDTSExternalMetadataColumn100 externalColumnToPopulate, IDTSOutputColumn100 outputColumn)
{
externalColumnToPopulate.Name = outputColumn.Name;
externalColumnToPopulate.Precision = outputColumn.Precision;
externalColumnToPopulate.Length = outputColumn.Length;
externalColumnToPopulate.DataType = outputColumn.DataType;
externalColumnToPopulate.Scale = outputColumn.Scale;
}
/// <summary>
/// Create an external metadata column for each output. Map the two
/// by setting the ExternalMetaDataColumnID property of the output to the external metadata column.
/// </summary>
/// <param name="output">The output the columns are added to.</param>
private static void CreateExternalMetaDataColumn(IDTSExternalMetadataColumnCollection100 externalCollection, IDTSOutputColumn100 column)
{
// For each output column create an external meta data columns.
IDTSExternalMetadataColumn100 eColumn = externalCollection.New();
eColumn.Name = column.Name;
eColumn.DataType = column.DataType;
eColumn.Precision = column.Precision;
eColumn.Length = column.Length;
eColumn.Scale = column.Scale;
column.ExternalMetadataColumnID = eColumn.ID;
}
protected void SetColumnDataType(IDTSOutputColumn100 column, string dataType, decimal length)
{
switch (dataType)
{
case "Char":
column.SetDataTypeProperties(DataType.DT_WSTR, (int)length, 0, 0, 0);
break;
case "Integer":
column.SetDataTypeProperties(DataType.DT_I4, 0, 0, 0, 0);
break;
case "Decimal":
column.SetDataTypeProperties(DataType.DT_DECIMAL, 0, 0, 0, 0);
break;
case "Date":
column.SetDataTypeProperties(DataType.DT_DATE, 0, 0, 0, 0);
break;
default:
throw new NotImplementedException();
}
}
public DataFlowOutputColumnNodeFactory(IDTSOutputColumn100 outputColumn)
{
_outputColumn = outputColumn;
}
internal BipsProxyIDTSOutputColumn100(IDTSOutputColumn100 innerObject)
{
_innerObject = innerObject;
}
/// <summary>
/// Constructs an external metadata column and adds it to the provided collection, using the column metadata from the provided output column.
/// </summary>
/// <param name="externalMetadataColumnCollection"></param>
/// <param name="outputColumn"></param>
/// <returns></returns>
private IDTSExternalMetadataColumn100 BuildExternalMetadataColumn(IDTSExternalMetadataColumnCollection100 externalMetadataColumnCollection, IDTSOutputColumn100 outputColumn)
{
IDTSExternalMetadataColumn100 externalColumnToPopulate = externalMetadataColumnCollection.New();
externalColumnToPopulate.Name = outputColumn.Name;
externalColumnToPopulate.Precision = outputColumn.Precision;
externalColumnToPopulate.Length = outputColumn.Length;
externalColumnToPopulate.DataType = outputColumn.DataType;
externalColumnToPopulate.Scale = outputColumn.Scale;
externalColumnToPopulate.CodePage = outputColumn.CodePage;
return externalColumnToPopulate;
}
/// <summary>
/// カスタムプロパティであるInputLineageIDを取得する
/// </summary>
/// <param name="outputColumn"></param>
/// <returns></returns>
private int GetInputLineageID(IDTSOutputColumn100 outputColumn)
{
return outputColumn.CustomPropertyCollection
.Cast<IDTSCustomProperty100>()
.Where(x => x.Name == "InputLineageID")
.First()
.Value;
}
