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);
}
public void Initialize(Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSComponentMetaData100 dtsComponentMetadata, IServiceProvider serviceProvider)
{
this.serviceProvider = serviceProvider;
this.metaData = dtsComponentMetadata;
this.connectionService = (IDtsConnectionService)serviceProvider.GetService(typeof(IDtsConnectionService));
}
private IDTSPath100 ConnectToAnotherPipelineComponent(IDTSComponentMetaData100 sourceComponent, int sourceComponentOutputIndex = 0, int inputCollectionIndex = 0)
{
bool pathHasStartPoint = false;
IDTSPath100 path = null;
foreach (IDTSPath100 dtsPath in ParentDataFlowTask.MainPipe.PathCollection)
{
IDTSOutput100 output = dtsPath.StartPoint;
if (dtsPath.StartPoint.Name == sourceComponent.OutputCollection[sourceComponentOutputIndex].Name &&
dtsPath.StartPoint.Component.Name == sourceComponent.Name
)
{
pathHasStartPoint = true;
path = dtsPath;
}
}
if (pathHasStartPoint)
{
ParentDataFlowTask.MainPipe.PathCollection.RemoveObjectByID(path.ID);
}
path = ParentDataFlowTask.MainPipe.PathCollection.New();
path.AttachPathAndPropagateNotifications(
sourceComponent.OutputCollection[sourceComponentOutputIndex],
ComponentMetaData.InputCollection[inputCollectionIndex]);
return(path);
}
public void TestChangeRowsProcessedOutputTypeToData()
{
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 textFileSplitter = dataFlowTask.ComponentMetaDataCollection.New();
textFileSplitter.Name = "Row Splitter Test";
textFileSplitter.ComponentClassID = typeof(Martin.SQLServer.Dts.TextFileSplitter).AssemblyQualifiedName;
CManagedComponentWrapper instance = textFileSplitter.Instantiate();
instance.ProvideComponentProperties();
Boolean exceptionThrown = false;
try
{
instance.SetOutputProperty(textFileSplitter.OutputCollection[3].ID, ManageProperties.typeOfOutput, Utilities.typeOfOutputEnum.DataRecords);
}
catch (COMException ex)
{
Assert.AreEqual(MessageStrings.CantChangeOutputProperties("RowsProcessed"), ex.Message, "Exception Message Wrong");
exceptionThrown = true;
}
Assert.IsTrue(exceptionThrown, "Exception Not Thrown");
}
public IDTSComponentMetaData100 AddComp_RowCount(string ComponentName,
string VarName,
IDTSOutput100 outCols
)
{
// Create
IDTSComponentMetaData100 Comp = dmp.ComponentMetaDataCollection.New();
Comp.ComponentClassID = "Microsoft.RowCount";
// Instantiate
CManagedComponentWrapper Inst = Comp.Instantiate();
Inst.ProvideComponentProperties();
Comp.Name = ComponentName;
Comp.Description = "zliczanie wierszy";
// Parametrize
Inst.SetComponentProperty("VariableName", VarName);
// Connect
IDTSPath100 pth = dmp.PathCollection.New();
pth.AttachPathAndPropagateNotifications(outCols, Comp.InputCollection[0]);
// Return
return(Comp);
}
public void Initialize(Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSComponentMetaData100 dtsComponentMetadata, IServiceProvider serviceProvider)
{
this.serviceProvider = serviceProvider;
this.metaData = dtsComponentMetadata;
this.connectionService = (IDtsConnectionService)serviceProvider.GetService(typeof(IDtsConnectionService));
}
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>
/// constructor of the GUI
/// </summary>
/// <param name="metadata">SSIS metadata for the component</param>
/// <param name="connections">availabe SSIS connectionmanagers</param>
/// <param name="serviceProvider">SSIS service provider</param>
/// <param name="variables">SSIS variables</param>
public frmLookupUI(IDTSComponentMetaData100 metadata, Connections connections, IServiceProvider serviceProvider, Variables variables)
{
_metadata = metadata;
_serviceProvider = serviceProvider;
_connections = connections;
_variables = variables;
LoadCustomProperties();
//initialize gui components & databindings
InitializeComponent();
PopulateDftColumnItemlists();
InitializeCustomComponents();
CreateBindings();
PopulateSqlTableItemList();
//all input columns are always marked as readwrite
IDTSVirtualInput100 virtualInput = _metadata.InputCollection[Constants.INPUT_NAME].GetVirtualInput();
for (int i = 0; i < virtualInput.VirtualInputColumnCollection.Count; i++)
{
virtualInput.SetUsageType(virtualInput.VirtualInputColumnCollection[i].LineageID, DTSUsageType.UT_READWRITE);
}
//set the window title
this.Text = this.Text + " " + _isagCustomProperties.Version;
}
public void TestOutputColumn_InvalidCustomProperty()
{
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
IDTSCustomProperty100 cp = speechToText.OutputCollection[0].OutputColumnCollection[0].CustomPropertyCollection.New();
cp.Name = "IAmInvalid";
cp.Value = "IAmInvalid";
DTSValidationStatus actual = speechToTextInstance.Validate();
DTSValidationStatus expected = DTSValidationStatus.VS_ISCORRUPT;
Assert.AreEqual(expected, actual);
Assert.AreEqual("[Error] SSIS Speech To Text: Output Column InputChannel has invalid property IAmInvalid.", events.errorMessages[0]);
}
public void TestValidateOK()
{
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";
DTSValidationStatus actual = speechToTextInstance.Validate();
DTSValidationStatus expected = DTSValidationStatus.VS_ISVALID;
Assert.AreEqual(expected, actual);
Assert.AreEqual(0, events.errorMessages.Count, "There are error messages");
}
/// <summary>
/// This method is ivoked once, when the user double clicks on it at design time.
/// </summary>
/// <param name="dtsComponentMetadata"></param>
/// <param name="serviceProvider"></param>
public void Initialize(IDTSComponentMetaData100 dtsComponentMetadata, IServiceProvider serviceProvider)
{
// Save a reference to the components metadata and service provider
_sp = serviceProvider;
_md = dtsComponentMetadata;
// Check model: if no model was specified, add it one now.
IDTSCustomProperty100 model = null;
try
{
model = dtsComponentMetadata.CustomPropertyCollection[ComponentConstants.PROPERTY_KEY_MODEL];
_model = JSONSourceComponentModel.LoadFromJson(model.Value.ToString());
}
catch (Exception e) {
// No model found. Add a new now.
_model = new JSONSourceComponentModel();
model = dtsComponentMetadata.CustomPropertyCollection.New();
model.Name = ComponentConstants.PROPERTY_KEY_MODEL;
model.Value = _model.ToJsonConfig();
}
if (_md == null)
{
_md = (IDTSComponentMetaData100)_md.Instantiate();
}
_virtualInputLane = dtsComponentMetadata.InputCollection[ComponentConstants.NAME_INPUT_LANE_PARAMS].GetVirtualInput();
}
public void Initialize(IDTSComponentMetaData100 dtsComponentMetadata, IServiceProvider serviceProvider)
{
this.serviceProvider = serviceProvider;
this.metaData = dtsComponentMetadata;
this.connectionService = (IDtsConnectionService)serviceProvider.GetService(typeof(IDtsConnectionService));
}
/// <summary>
/// fires an SSIS event
/// </summary>
/// <param name="ComponentMetaData">componets SSIS metadata</param>
/// <param name="eventType">event type</param>
/// <param name="sqlType">sql type (always</param>
/// <param name="description">SSIS event description</param>
private static void Fire(IDTSComponentMetaData100 ComponentMetaData, Type eventType, int sqlType, string description)
{
bool cancel = false;
switch (eventType)
{
case Type.Information:
ComponentMetaData.FireInformation(sqlType, ComponentMetaData.Name, description, "", 0, ref cancel);
break;
case Type.Progress:
throw new NotImplementedException("Progress messages are not implemented");
case Type.Warning:
ComponentMetaData.FireWarning(sqlType, ComponentMetaData.Name, description, string.Empty, 0);
break;
case Type.Error:
ComponentMetaData.FireError(sqlType, ComponentMetaData.Name, description, string.Empty, 0, out cancel);
break;
default:
ComponentMetaData.FireError(sqlType, ComponentMetaData.Name, description, string.Empty, 0, out cancel);
break;
}
}
public override IDTSComponentMetaData100 AddDestAdapter(IDTSPipeline100 pipeline, ConnectionManager destConnMgr, out IDTSDesigntimeComponent100 destDesignTimeComp)
{
if (String.IsNullOrEmpty(quotedTableName))
{
throw new ArgumentException("Destination table name is empty");
}
IDTSComponentMetaData100 destComp = pipeline.ComponentMetaDataCollection.New();
destComp.ComponentClassID = OLEDB_DEST_GUID;
destComp.ValidateExternalMetadata = true;
destDesignTimeComp = destComp.Instantiate();
destDesignTimeComp.ProvideComponentProperties();
destComp.Name = "OleDB Destination - Sql Server";
destDesignTimeComp.SetComponentProperty("AccessMode", 0);
destDesignTimeComp.SetComponentProperty("OpenRowset", quotedTableName);
// set connection
destComp.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(destConnMgr);
destComp.RuntimeConnectionCollection[0].ConnectionManagerID = destConnMgr.ID;
// get metadata
destDesignTimeComp.AcquireConnections(null);
destDesignTimeComp.ReinitializeMetaData();
destDesignTimeComp.ReleaseConnections();
extCols = destComp.InputCollection[0].ExternalMetadataColumnCollection;
return(destComp);
}
public override IDTSComponentMetaData100 Initialize()
{
// create the odbc source
IDTSComponentMetaData100 comp = base.Initialize();
//set connection properies
_cm.Name = "ODBC Source Connection Manager";
_cm.ConnectionString = _src.ConnectionString;
_cm.Description = _src.Description;
//do not require Qualifier
//cm.Qualifier = dbsrc.DBConnection.Qualifier;
CManagedComponentWrapper dcomp = comp.Instantiate();
//set Component Custom Properties
foreach (KeyValuePair <string, object> prop in _src.CustomProperties.CustomPropertyCollection.InnerArrayList)
{
dcomp.SetComponentProperty(prop.Key, prop.Value);
}
if (comp.RuntimeConnectionCollection.Count > 0)
{
comp.RuntimeConnectionCollection[0].ConnectionManagerID = _cm.ID;
comp.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(_cm);
}
// Finalize
Reinitialize(dcomp);
return(comp);
}
/// <summary>
/// Rebuilds mapping of input and output columns
/// (errors are corrected if possible)
/// </summary>
/// <param name="componentMetaData">SSIS components metadata</param>
public void RebuildMappings(IDTSComponentMetaData100 componentMetaData, IsagEvents events)
{
IDTSInput100 input = componentMetaData.InputCollection[Constants.INPUT_NAME];
IDTSVirtualInput100 vInput = input.GetVirtualInput();
Dictionary <int, ColumnConfig> mappingsInput = new Dictionary <int, ColumnConfig>();
List <ColumnConfig> mappingsWithoutInputs = new List <ColumnConfig>();
List <ColumnConfig> newMappings = new List <ColumnConfig>();
if (this.ContainsWrongUsageType(vInput.VirtualInputColumnCollection, events))
{
ComponentMetaDataTools.SetUsageTypeReadOnly(vInput);
}
//Writre existing mappings in 2 lists (one with input columns, one without)
foreach (ColumnConfig config in this.ColumnConfigList)
{
if (config.HasInput)
{
mappingsInput.Add(config.InputColumnId, config);
}
else
{
mappingsWithoutInputs.Add(config);
}
}
//Generate new mapping using SSIS input columns
foreach (IDTSInputColumn100 inputCol in input.InputColumnCollection)
{
ColumnConfig config;
if (mappingsInput.ContainsKey(inputCol.ID))
{
config = mappingsInput[inputCol.ID];
config.InputColumnName = inputCol.Name;
config.DataTypeInput = SqlCreator.GetSQLServerDataTypeFromInput(inputCol, config.IsGeometryDataType);
}
else
{
config = new ColumnConfig(inputCol.Name, SqlCreator.GetSQLServerDataTypeFromInput(inputCol, isGeometry: false), inputCol);
}
newMappings.Add(config);
}
//Add properties to the newly created mapping
ColumnConfigList.Clear();
foreach (ColumnConfig config in newMappings)
{
ColumnConfigList.Add(config);
}
foreach (ColumnConfig config in mappingsWithoutInputs)
{
ColumnConfigList.Add(config);
}
this.Save(componentMetaData);
}
public override IDTSComponentMetaData100 Initialize()
{
//create flat file destination component
IDTSComponentMetaData100 comp = base.Initialize();
_cm.Name = $"FlatFile Destination Connection Manager {comp.ID}";
//Create a new FlatFileDestination component
CManagedComponentWrapper dcomp = comp.Instantiate();
foreach (KeyValuePair <string, object> prop in _dst.CustomProperties.CustomPropertyCollection.InnerArrayList)
{
dcomp.SetComponentProperty(prop.Key, prop.Value);
}
/*Specify the connection manager for Src.The Connections class is a collection of the connection managers that have been added to that package and are available for use at run time*/
if (comp.RuntimeConnectionCollection.Count > 0)
{
comp.RuntimeConnectionCollection[0].ConnectionManagerID = _cm.ID;
comp.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(_cm);
}
this.Reinitialize(dcomp);
return(comp);
}
public void Initialize(IDTSComponentMetaData100 dtsComponentMetadata, IServiceProvider serviceProvider)
{
this.serviceProvider = serviceProvider;
this.metaData = dtsComponentMetadata;
this.connectionService = (IDtsConnectionService)serviceProvider.GetService(typeof(IDtsConnectionService));
}
public void TestValidateExtraCustomProperty()
{
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.New();
DTSValidationStatus actual = speechToTextInstance.Validate();
DTSValidationStatus expected = DTSValidationStatus.VS_ISCORRUPT;
Assert.AreEqual(expected, actual);
Assert.AreEqual("[Error] SSIS Speech To Text: There is either to many or not enough custom properties.", events.errorMessages[0]);
}
public static void CustomizeComponentProperties(IDTSComponentMetaData100 componentMetaData)
{
//add IsIdentifier property to input columns
IDTSInput100 input = componentMetaData.InputCollection[0];
foreach (IDTSInputColumn100 column in input.InputColumnCollection)
{
AddColumnIsIdentifierProperty(column);
}
//add output columns
IDTSOutput100 output = componentMetaData.OutputCollection[0];
AddOutputColumn(output, "TableName", DataType.DT_WSTR, 255, 0, 0, 0);
AddOutputColumn(output, "ColumnNameList", DataType.DT_WSTR, 4000, 0, 0, 0);
AddOutputColumn(output, "ColumnValueList", DataType.DT_WSTR, 4000, 0, 0, 0);
AddOutputColumn(output, "IdentifierCondition", DataType.DT_WSTR, 4000, 0, 0, 0);
AddOutputColumn(output, "ExceptionLevel", DataType.DT_WSTR, 20, 0, 0, 0);
AddOutputColumn(output, "StartTime", DataType.DT_DBTIMESTAMP, 0, 0, 0, 0);
AddOutputColumn(output, "PackageName", DataType.DT_WSTR, 255, 0, 0, 0);
AddOutputColumn(output, "TaskName", DataType.DT_WSTR, 255, 0, 0, 0);
AddOutputColumn(output, "ComponentName", DataType.DT_WSTR, 255, 0, 0, 0);
AddOutputColumn(output, "ErrorCode", DataType.DT_I4, 0, 0, 0, 0);
AddOutputColumn(output, "ErrorDescription", DataType.DT_WSTR, 1000, 0, 0, 0);
AddOutputColumn(output, "ErrorColumn", DataType.DT_I4, 0, 0, 0, 0);
AddOutputColumn(output, "ErrorColumnName", DataType.DT_WSTR, 255, 0, 0, 0);
}
public IDTSComponentMetaData100 AddComp_DerivedCol(string ComponentName,
IDTSOutput100 outCols
)
{
// Create
IDTSComponentMetaData100 Comp = dmp.ComponentMetaDataCollection.New();
Comp.ComponentClassID = "Microsoft.DerivedColumn";
// Instantiate
CManagedComponentWrapper Inst = Comp.Instantiate();
Inst.ProvideComponentProperties();
Comp.Name = ComponentName;
Comp.Description = "Derajw kolumn";
// Parametrize
Comp.OutputCollection[0].TruncationRowDisposition = DTSRowDisposition.RD_NotUsed;
Comp.OutputCollection[0].ErrorRowDisposition = DTSRowDisposition.RD_NotUsed;
// Connect
IDTSPath100 pth = dmp.PathCollection.New();
pth.AttachPathAndPropagateNotifications(outCols, Comp.InputCollection[0]);
// Return
return(Comp);
}
private void ProcessDataFlow(MainPipe mainPipe, TaskHost taskHost)
{
int sortCount = 0;
foreach (IDTSComponentMetaData100 comp in mainPipe.ComponentMetaDataCollection)
{
string key = PackageHelper.GetComponentKey(comp);
if (!PackageHelper.ComponentInfos.ContainsKey(key))
{
System.Diagnostics.Debug.WriteLine("DataFlowPerformancePractice ProcessDataFlow(113): Key not found '" + key + "'");
continue;
}
if (PackageHelper.ComponentInfos[key].CreationName == "DTSTransform.Sort.2")
{
sortCount++;
//Trace the input
IDTSComponentMetaData100 sourceComp = PackageHelper.TraceInputToSource(mainPipe, comp);
if (sourceComp != null)
{
key = PackageHelper.GetComponentKey(sourceComp);
if (PackageHelper.ComponentInfos[key].Name == "OLE DB Source" ||
PackageHelper.ComponentInfos[key].Name == "ADO NET Source")
{
Results.Add(new Result(false, string.Format("The {0} Sort transformation is operating on data provided from the {1} source. Rather than using the Sort transformation, which is fully blocking, the sorting should be performed using a WHERE clause in the source's SQL, and the IsSorted and SortKey properties should be set appropriately. Reference: http://msdn.microsoft.com/en-us/library/ms137653(SQL.90).aspx", comp.Name, sourceComp.Name), ResultSeverity.Normal));
}
}
}
}
if (sortCount > 2)
{
Results.Add(new Result(false, String.Format("There are {0} Sort transfomations in the {1} data flow. A large number of Sorts can slow down data flow performance. Consider staging the data to a relational database and sorting it there.", sortCount, taskHost.Name), ResultSeverity.Normal));
}
}
public void TestExecuteRecogniseAsyncBasic()
{
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 = config.AppSettings.Settings["subscriptionKey"].Value;
Uri targetURI = new Uri(@"wss://speech.platform.bing.com/api/service/recognition/continuous");
SpeechToTextImplement testMe = new SpeechToTextImplement(speechToText, "en-us", targetURI, config.AppSettings.Settings["subscriptionKey"].Value);
string filename = "Ex_Pro_1.mp3";
testMe.ExecuteRecogniseAsync(filename).Wait();
Assert.IsTrue(testMe.Results.Count > 0);
}
public SSISOdbcSource(OdbcSource dbsrc, MainPipe pipe, ConnectionManager cm)
: base(pipe, "ODBC Source")
{
// create the odbc source
//set connection properies
cm.Name = "ODBC Source Connection Manager";
cm.ConnectionString = dbsrc.ConnectionString;
cm.Description = dbsrc.Description;
//do not require Qualifier
//cm.Qualifier = dbsrc.DBConnection.Qualifier;
IDTSComponentMetaData100 comp = this.MetadataCollection;
CManagedComponentWrapper dcomp = comp.Instantiate();
//set Component Custom Properties
foreach (KeyValuePair <string, object> prop in dbsrc.CustomProperties.CustomPropertyCollection.InnerArrayList)
{
dcomp.SetComponentProperty(prop.Key, prop.Value);
}
if (comp.RuntimeConnectionCollection.Count > 0)
{
comp.RuntimeConnectionCollection[0].ConnectionManagerID = cm.ID;
comp.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(cm);
}
// Finalize
this.Reinitialize(dcomp);
}
public IDTSComponentMetaData100 AddComp_Multicast(string ComponentName,
IDTSOutput100 outCols
)
{
// Create
IDTSComponentMetaData100 Comp = dmp.ComponentMetaDataCollection.New();
Comp.ComponentClassID = "Microsoft.Multicast";
// Instantiate
CManagedComponentWrapper Inst = Comp.Instantiate();
Inst.ProvideComponentProperties();
Comp.Name = ComponentName;
Comp.Description = "Dodany Sort";
// Connect
IDTSPath100 pth = dmp.PathCollection.New();
pth.AttachPathAndPropagateNotifications(outCols, Comp.InputCollection[0]);
// Return
return(Comp);
}
/// <summary>
/// Updates input columns custom properties (GUIDs)
/// </summary>
/// <param name="input">the components input</param>
/// <param name="componentMetaData">>the components metadata</param>
/// <param name="isagCustomProperties">the components custom properties</param>
private static void UpdateInputIdProperties(IDTSInput100 input, IDTSComponentMetaData100 componentMetaData, IsagCustomProperties isagCustomProperties)
{
foreach (IDTSInputColumn100 col in input.InputColumnCollection)
{
UpdateInputIdProperty(col, componentMetaData, isagCustomProperties);
}
}
public override IDTSComponentMetaData100 Connect(IDTSComponentMetaData100 src, int outputID = 0)
{
ConnectComponents(src, outputID);
//add partitionfunction input
SetPartitionFunctionInput(_src.Partition.Input);
return(MetadataCollection);
}
public SSISExcelSource(ExcelSource dbsrc, MainPipe pipe, ConnectionManager cm)
: base(pipe, "Excel Source")
{
// create the oledb source
//set connection properies
cm.Name = "Excel Source Connection Manager";
cm.ConnectionString = dbsrc.ConnectionString;
cm.Description = dbsrc.Description;
//set connection properties
//mwrt.IDTSConnectionManagerExcel100 ecm = cm.InnerObject as mwrt.IDTSConnectionManagerExcel100;
//ecm.ExcelFilePath = dbsrc.FilePath;
//ecm.FirstRowHasColumnName = dbsrc.Header;
//ecm.ExcelVersionNumber = mwrt.DTSExcelVersion.DTSExcelVer_2007;
IDTSComponentMetaData100 comp = this.MetadataCollection;
CManagedComponentWrapper dcomp = comp.Instantiate();
foreach (KeyValuePair <string, object> prop in dbsrc.CustomProperties.CustomPropertyCollection.InnerArrayList)
{
dcomp.SetComponentProperty(prop.Key, prop.Value);
}
/*Specify the connection manager for Src.The Connections class is a collection of the connection managers that have been added to that package and are available for use at run time*/
if (comp.RuntimeConnectionCollection.Count > 0)
{
comp.RuntimeConnectionCollection[0].ConnectionManagerID = cm.ID;
comp.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(cm);
}
// Finalize
this.Reinitialize(dcomp);
}
public SSISAdoNetSource(AdoNetSource dbsrc, MainPipe pipe, ConnectionManager cm)
: base(pipe, "ADO NET Source")
{
// create the adonet source
//set connection properies
cm.Name = "AdoNet Source Connection Manager";
cm.ConnectionString = dbsrc.ConnectionString;
cm.Description = dbsrc.Description;
//cm.Qualifier = "System.Data.SqlClient.SqlConnection, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089";
cm.Qualifier = dbsrc.DBConnection.Qualifier;
IDTSComponentMetaData100 comp = this.MetadataCollection;
CManagedComponentWrapper dcomp = comp.Instantiate();
//set Component Custom Properties
foreach (KeyValuePair <string, object> prop in dbsrc.CustomProperties.CustomPropertyCollection.InnerArrayList)
{
dcomp.SetComponentProperty(prop.Key, prop.Value);
}
if (comp.RuntimeConnectionCollection.Count > 0)
{
comp.RuntimeConnectionCollection[0].ConnectionManagerID = cm.ID;
comp.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(cm);
}
// Finalize
this.Reinitialize(dcomp);
}
public void TestAddNumberOfRowsOutputColumns()
{
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 actual = textFileSplitter.OutputCollection.New();
ManageColumns.AddNumberOfRowsOutputColumns(actual);
Assert.AreEqual(3, actual.OutputColumnCollection.Count, "Number of Columns is wrong");
Assert.AreEqual(MessageStrings.KeyValueColumnName, actual.OutputColumnCollection[0].Name, "Column Name is wrong");
Assert.AreEqual(DataType.DT_STR, actual.OutputColumnCollection[0].DataType, "DataType is wrong");
Assert.AreEqual(255, actual.OutputColumnCollection[0].Length, "Length is wrong");
Assert.AreEqual(MessageStrings.KeyValueColumnDescription, actual.OutputColumnCollection[0].Description, "Description is wrong");
Assert.AreEqual(MessageStrings.NumberOfRowsColumnName, actual.OutputColumnCollection[1].Name, "Column Name is wrong");
Assert.AreEqual(DataType.DT_I8, actual.OutputColumnCollection[1].DataType, "DataType is wrong");
Assert.AreEqual(0, actual.OutputColumnCollection[1].Length, "Length is wrong");
Assert.AreEqual(MessageStrings.NumberOfRowsColumnDescription, actual.OutputColumnCollection[1].Description, "Description is wrong");
Assert.AreEqual(MessageStrings.KeyValueStatusColumnName, actual.OutputColumnCollection[2].Name, "Column Name is wrong");
Assert.AreEqual(DataType.DT_STR, actual.OutputColumnCollection[2].DataType, "DataType is wrong");
Assert.AreEqual(255, actual.OutputColumnCollection[2].Length, "Length is wrong");
Assert.AreEqual(MessageStrings.KeyValueStatusColumnDescription, actual.OutputColumnCollection[2].Description, "Description is wrong");
}
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");
}
protected SSISModule(MainPipe pipe, string module_name, int module_id, string module_clsid)
{
//create SSIS component
m_pipe = pipe;
IDTSComponentMetaData100 comp = pipe.ComponentMetaDataCollection.New();
m_ID = comp.ID;
Application app = new Application();
comp.ComponentClassID = (String.IsNullOrEmpty(module_clsid)) ? app.PipelineComponentInfos[module_name].CreationName : module_clsid;
CManagedComponentWrapper dcomp = comp.Instantiate();
dcomp.ProvideComponentProperties();
//set common SSIS module properties
if (module_id == 0)
{
comp.Name = String.Format(CultureInfo.InvariantCulture, "{0}", module_name);
}
else
{
comp.Name = String.Format(CultureInfo.InvariantCulture, "{0} - {1}", module_name, module_id);
}
PrintOutput.PrintToOutput(String.Format(CultureInfo.InvariantCulture, "DE added {0}", comp.Name), DERun.Debug);
}
protected virtual void MatchInputColumns(Dictionary <string, int> converted, bool needschema)
{
IDTSComponentMetaData100 comp = this.MetadataCollection;
CManagedComponentWrapper dcomp = comp.Instantiate();
IDTSInput100 input = comp.InputCollection[0];
IDTSVirtualInput100 vInput = input.GetVirtualInput();
IDTSVirtualInputColumnCollection100 vColumns = vInput.VirtualInputColumnCollection;
IDTSExternalMetadataColumnCollection100 exColumns = input.ExternalMetadataColumnCollection;
if (exColumns != null && exColumns.Count > 0)
{
bool hasMatch = false;
foreach (IDTSExternalMetadataColumn100 exColumn in exColumns)
{
int inputColId;
if (converted.ContainsKey(exColumn.Name.ToLower()))
{
inputColId = (int)converted[exColumn.Name.ToLower()];
}
else
{
inputColId = FindVirtualInputColumnId(vColumns, exColumn.Name);
}
if (inputColId == 0)
{
//the column wasn't found if the Id is 0, so we'll print out a message and skip this row.
PrintOutput.PrintToOutput("DE could not map external column " + exColumn.Name + ". Skipping column.", DERun.Debug);
}
else
{
// create input column
IDTSInputColumn100 vCol = dcomp.SetUsageType(input.ID, vInput, inputColId, DTSUsageType.UT_READONLY);
// and then we'll map it to the input row.
dcomp.MapInputColumn(input.ID, vCol.ID, exColumn.ID);
hasMatch = true;
}
}
if (!hasMatch)
{
throw new InvalidArgumentException("Unable to map input to destination");
}
}
//if output schema is required and not provided
else if (needschema)
{
//PrintOutput.PrintToError("No destination columns available");
throw new InvalidArgumentException("No destination columns available");
}
//otherwise use virtual inputs
else
{
foreach (IDTSVirtualInputColumn100 vColumn in vColumns)
{
// create input column for all virtual input columns
dcomp.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
}
}
}
public HDFSDestinationFormUI(IDTSComponentMetaData100 metaData, IServiceProvider serviceProvider)
: this()
{
this.metaData = metaData;
this.serviceProvider = serviceProvider;
this.connectionService = (IDtsConnectionService)serviceProvider.GetService(typeof(IDtsConnectionService));
this.designTimeInstance = metaData.Instantiate();
}
public void Initialize(IDTSComponentMetaData100 dtsComponentMetadata, IServiceProvider serviceProvider)
{
ArgumentVerifier.CheckObjectArgument(dtsComponentMetadata, "componentMetadata");
ArgumentVerifier.CheckObjectArgument(serviceProvider, "serviceProvider");
this.componentMetadata = dtsComponentMetadata;
this.serviceProvider = serviceProvider;
this.connService = this.serviceProvider.GetService(typeof(Microsoft.SqlServer.Dts.Runtime.Design.IDtsConnectionService)) as Microsoft.SqlServer.Dts.Runtime.Design.IDtsConnectionService;
}
public __ComponentMetadataWrapper(IDTSComponentMetaData100 dtsComponentMetaData100, Func<IUnitOfWork> dictionaryUnitOfWorkCallback)
: base((object)dtsComponentMetaData100 == null ? null : dtsComponentMetaData100.CustomPropertyCollection)
{
if ((object)dtsComponentMetaData100 == null)
throw new ArgumentNullException("dtsComponentMetaData100");
if ((object)dictionaryUnitOfWorkCallback == null)
throw new ArgumentNullException("dictionaryUnitOfWorkCallback");
this.dtsComponentMetaData100 = dtsComponentMetaData100;
this.dictionaryUnitOfWorkCallback = dictionaryUnitOfWorkCallback;
}
public ReverseStringUIForm(IDTSComponentMetaData100 dtsComponentMetaData, IServiceProvider serviceProvider, Connections connections)
{
InitializeComponent();
// Store constructor parameters for later
_dtsComponentMetaData = dtsComponentMetaData;
// Get design-time interface for changes and validation
_designTimeComponent = _dtsComponentMetaData.Instantiate();
// Get Input
_input = _dtsComponentMetaData.InputCollection[0];
// Set any form controls that host component properties here
// None required for ReverseString component
// Populate DataGridView with columns
SetInputVirtualInputColumns();
_connections = connections;
// Get IDtsConnectionService and store.
IDtsConnectionService dtsConnectionService = serviceProvider.GetService(typeof(IDtsConnectionService)) as IDtsConnectionService;
_dtsConnectionService = dtsConnectionService;
// Get Connections collection, and get name of currently selected connection.
string connectionName = "";
if (_dtsComponentMetaData.RuntimeConnectionCollection[0] != null)
{
IDTSRuntimeConnection100 runtimeConnection =
_dtsComponentMetaData.RuntimeConnectionCollection[0];
if (runtimeConnection != null
&& runtimeConnection.ConnectionManagerID.Length > 0
&& _connections.Contains(runtimeConnection.ConnectionManagerID))
{
connectionName = _connections[runtimeConnection.ConnectionManagerID].Name;
}
}
// Populate connections combo.
PopulateConnectionsCombo(this.cmbSqlConnections, Connection_Type,
connectionName);
}
public SsisComponent(Transformation transformation, SSISEmitterContext context)
{
if (!context.HasSSISDataFlowTask)
{
// TODO: Message.Trace(Severity.Error)
}
_transformation = transformation;
_dataFlowTask = context.SSISDataFlowTask;
_context = context;
_component = _dataFlowTask.NewComponentMetaData();
_component.ComponentClassID = ClassID;
_instance = _component.Instantiate();
_instance.ProvideComponentProperties();
_component.Name = _transformation.Name;
_component.ValidateExternalMetadata = transformation.ValidateExternalMetadata;
}
public Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSBuffer100 Clone(IDTSComponentMetaData100 pOwner)
{
return _innerObject.Clone(pOwner);
}
internal BipsProxyIDTSComponentMetaData100(IDTSComponentMetaData100 innerObject)
{
_innerObject = innerObject;
}
public void Instantiate(String bstrUserComponentTypeName, IDTSComponentMetaData100 pComponentMetaData,
IDTSVariableDispenser100 pVariableDispenser, IDTSBufferManager100 pBufferManager,
IDTSEventInfos100 pEventInfos, IDTSObjectReferenceTracker100 pRefTracker,
IDTSLogEntryInfos100 pLogEntryInfos, out DTSProductLevel peProductLevel)
{
_innerObject.Instantiate(bstrUserComponentTypeName, pComponentMetaData, pVariableDispenser, pBufferManager,
pEventInfos, pRefTracker, pLogEntryInfos, out peProductLevel);
}
/// <summary>
/// Initialize is called before Edit() to give us the component metadata for the
/// component about to be edited, and an IServiceProvider where we can ask
/// for common VS and SSIS services, like creating connection managers... (see below)
/// </summary>
public void Initialize(IDTSComponentMetaData100 dtsComponentMetadata, IServiceProvider serviceProvider)
{
// Save off the component metadata and the service provider interfaces for later...
component_ = dtsComponentMetadata;
serviceProvider_ = serviceProvider;
needsNewMetaData_ = true;
manualEditMode_ = false;
}
public void Initialize(IDTSComponentMetaData100 dtsComponentMetadata, IServiceProvider serviceProvider)
{
this.DtsComponentMetaData100 = dtsComponentMetadata;
this.ServiceProvider = serviceProvider;
this.ComponentMetadataWrapper = new __ComponentMetadataWrapper(this.DtsComponentMetaData100, this.GetDictionaryUnitOfWork);
}
public void NewDataFlow()
{
_lastComponent = null;
}
public void Initialize(IDTSComponentMetaData100 dtsComponentMetadata, IServiceProvider serviceProvider)
{
this.ServiceProvider = serviceProvider;
}
public static void RegisterOleDBConnection(SsisEmitterContext context, OleDBConnection oleDBConnection, IDTSComponentMetaData100 component)
{
oleDBConnection.Initialize(context);
component.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(oleDBConnection.ConnectionManager);
component.RuntimeConnectionCollection[0].ConnectionManagerID = oleDBConnection.ConnectionManager.ID;
}
void IDtsComponentUI.Initialize(Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSComponentMetaData100 dtsComponentMetadata, IServiceProvider serviceProvider)
{
this.componentMetadata = dtsComponentMetadata;
this.serviceProvider = serviceProvider;
Debug.Assert(this.serviceProvider != null);
this.errorCollector = this.serviceProvider.GetService(typeof(IErrorCollectionService)) as IErrorCollectionService;
Debug.Assert(this.errorCollector != null);
if (this.errorCollector == null)
{
throw new InvalidOperationException("Not all editing services available");
}
}
private static void InitializeComponent(IDTSComponentMetaData100 cmd, ConnectionManager cm)
{
try
{
CManagedComponentWrapper cmcw = cmd.Instantiate();
cmd.RuntimeConnectionCollection[0].ConnectionManager = Microsoft.SqlServer.Dts.Runtime.DtsConvert.GetExtendedInterface(cm);
cmd.RuntimeConnectionCollection[0].ConnectionManagerID = cm.ID;
// aquire the runtime connection to the designtime instance of the component.
cmcw.AcquireConnections(null);
cmcw.ReinitializeMetaData();
cmd.RemoveInvalidInputColumns();
cmcw.ReleaseConnections();
}
catch (Exception e)
{
Console.WriteLine("There is a problem with the connection manager {0}", cm.Name);
Console.WriteLine("More than likely it could not connect to the database and reinitialize the correct columns");
Console.WriteLine("Make sure you have the ability to connect and that the connection managers in the template package are correct");
Console.WriteLine("Error message is below:");
Console.WriteLine(e.Message);
System.Environment.Exit(0);
}
}
public void Initialize(IDTSComponentMetaData100 dtsComponentMetadata, IServiceProvider serviceProvider)
{
// Store ComponentMetaData for later use
_dtsComponentMetaData = dtsComponentMetadata;
_serviceProvider = serviceProvider;
}
private void AddConvertComponent(Hashtable colConvertTable, Hashtable colConnectTable)
{
convertComp = pipeline.ComponentMetaDataCollection.New();
convertComp.ComponentClassID = DATACONVERT_GUID;
convertComp.Name = "Data Conversion";
convertComp.ValidateExternalMetadata = true;
convertDesignTimeComp = convertComp.Instantiate();
convertDesignTimeComp.ProvideComponentProperties();
IDTSInput100 cvtInput = convertComp.InputCollection[0];
IDTSOutput100 cvtOutput = convertComp.OutputCollection[0];
IDTSInputColumnCollection100 cvtInputCols = cvtInput.InputColumnCollection;
IDTSOutput100 srcOutput = srcComp.OutputCollection[0];
pipeline.PathCollection.New().AttachPathAndPropagateNotifications(srcOutput, cvtInput);
IDTSVirtualInput100 cvtVirInput = cvtInput.GetVirtualInput();
int i = 0;
foreach (object key in colConvertTable.Keys)
{
int srcColID = (int)key;
cvtVirInput.SetUsageType(srcColID, DTSUsageType.UT_READONLY);
IDTSInputColumn100 cvtInputCol = cvtInputCols.GetInputColumnByLineageID(srcColID);
if (cvtInputCol != null)
{
IDTSOutputColumn100 cvtOutputCol = convertDesignTimeComp.InsertOutputColumnAt(cvtOutput.ID, i++, "Convert_" + cvtInputCol.Name, "");
IDTSExternalMetadataColumn100 destCol = (IDTSExternalMetadataColumn100)colConvertTable[key];
convertDesignTimeComp.SetOutputColumnDataTypeProperties(cvtOutput.ID,
cvtOutputCol.ID, destCol.DataType, destCol.Length, destCol.Precision, destCol.Scale, destCol.CodePage);
// map output column and input column
convertDesignTimeComp.SetOutputColumnProperty(cvtOutput.ID,
cvtOutputCol.ID, "SourceInputColumnLineageID", srcColID);
// add match table entry.
colConnectTable.Add(cvtOutputCol.ID, destCol);
}
}
}
public override void CreateNewOutputRows()
{
#region Set up verbose logging
__metadata = ComponentMetaData;
#endregion
// See http://blogs.msdn.com/b/brian_jones/archive/2008/11/10/reading-data-from-spreadsheetml.aspx
// http://openxmldeveloper.org/discussions/formats/f/14/p/5029/157797.aspx
// http://blogs.msdn.com/b/ericwhite/archive/2010/07/21/table-markup-in-open-xml-spreadsheetml.aspx
#region Configure Column Mapping
this.MapColumns();
if (this._columnMappings.Count != ComponentMetaData.OutputCollection[0].OutputColumnCollection.Count)
{
string message = this._columnMappings.Count.ToString() + " column relationships have been set up, but the Script Source has "
+ ComponentMetaData.OutputCollection[0].OutputColumnCollection.Count.ToString() + " output columns defined.";
ReportError(message, true);
throw new ArgumentException(message);
}
VerboseLog(this._columnMappings.Count.ToString() + " column mappings defined.");
#endregion
#region Extract Excel file name from connection manager
string workbookFileName = null;
SpreadsheetDocument document = null;
try
{
VerboseLog("Extracting Excel file name from connection manager.");
string connectionString = ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager.ConnectionString;
string[] connectionStringParts = connectionString.Split(';');
foreach (string connectionStringPart in connectionStringParts)
{
string[] pair = connectionStringPart.Split('=');
if (pair[0] == "Data Source")
{
workbookFileName = pair[1];
VerboseLog("File name of '" + workbookFileName + "' identified in connection manager.");
break;
}
}
}
#region catch ...
catch (Exception ex)
{
ReportError("Unable to parse connection string: " + ex.Message, true);
}
#endregion
#endregion
#region Opening Excel file
if (workbookFileName != null)
{
try
{
VerboseLog("Attempting to open Excel file.");
document = SpreadsheetDocument.Open(workbookFileName, false);
}
#region catch ...
catch (Exception ex)
{
ReportError("Unable to open '" + workbookFileName + "': " + ex.Message, true);
}
#endregion
}
VerboseLog("Excel file opened.");
#endregion
try
{
WorkbookPart workbook = document.WorkbookPart;
SharedStringTablePart sharedStringTablePart = workbook.SharedStringTablePart;
#region Unused code for finding ranges
//ComponentMetaData.FireInformation(0, "", "Got WorkbookPart", "", 0, ref fireAgain);
//#region Look at Ranges
//bool foundRange = false;
//RangeDef rangeDef = new RangeDef();
//foreach (DefinedName name in workbook.Workbook.GetFirstChild<DefinedNames>())
//{
// ComponentMetaData.FireInformation(0, "", "Looking at defined name '" + name.Name + "'", "", 0, ref fireAgain);
// if (name.Name == this._rangeName)
// {
// ComponentMetaData.FireInformation(0, "", "Saving def", "", 0, ref fireAgain);
// rangeDef.Name = name.Name;
// string reference = name.InnerText;
// ComponentMetaData.FireInformation(0, "", " reference: " + reference, "", 0, ref fireAgain);
// rangeDef.Sheet = reference.Split('!')[0].Trim('\'');
// string[] rangeArray = reference.Split('!')[1].Split('$');
// rangeDef.StartCol = rangeArray[1];
// rangeDef.StartRow = rangeArray[2].TrimEnd(':');
// rangeDef.EndCol = rangeArray[3];
// rangeDef.EndRow = rangeArray[4];
// foundRange = true;
// break;
// }
//}
//ComponentMetaData.FireInformation(0, "", "Done looking for defined names", "", 0, ref fireAgain);
//if (foundRange)
//{
// string rangeID = workbook.Workbook.Descendants<Sheet>().Where(r => r.Name.Equals(rangeDef.Sheet)).First().Id;
// ComponentMetaData.FireInformation(0, "", "Got rangeID " + rangeID, "", 0, ref fireAgain);
// WorksheetPart range = (WorksheetPart)workbook.GetPartById(rangeID);
// ComponentMetaData.FireInformation(0, "", "Got Range", "", 0, ref fireAgain);
//}
//#endregion
#endregion
#region Iterate over sheets to find table
// VerboseLog("Searching sheets for table '" + ExcelTableName + "'.");
VerboseLog("Searching for sheet '" + ExcelSheetName + "'.");
// Table table = null;
Worksheet worksheet = null;
foreach (Sheet sheet in workbook.Workbook.Sheets)
{
VerboseLog("Examining sheet '" + sheet.Name + "'.");
WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheet.Id);
if (sheet.Name == ExcelSheetName)
{
worksheet = worksheetPart.Worksheet;
VerboseLog("Sheet found.");
break;
}
//foreach (TableDefinitionPart tableDefinitionPart in worksheetPart.TableDefinitionParts)
//{
// VerboseLog("Sheet contains table '" + tableDefinitionPart.Table.DisplayName + "'.");
// if (tableDefinitionPart.Table.DisplayName == ExcelTableName)
// {
// worksheet = worksheetPart.Worksheet;
// table = tableDefinitionPart.Table;
// VerboseLog("Sheet and table found.");
// break;
// }
//}
//if (table != null)
//{
// break;
//}
}
#endregion
//if (table == null)
//{
// ReportError("Table '" + ExcelTableName + "' wasn't found in '" + workbookFileName + "'.", true);
//}
//else
//{
// string firstColumnHeader = "";
// #region Find Excel Column Offsets
// VerboseLog("Collecting column offsets for mapped columns.");
// int columnIndex = 1;
// foreach (TableColumn tableColumn in table.TableColumns)
// {
// if (columnIndex == 1)
// {
// firstColumnHeader = tableColumn.Name;
// }
// foreach (ColumnMapping columnRelationship in this._columnMappings)
// {
// if (tableColumn.Name == columnRelationship.ExcelColumnName)
// {
// VerboseLog("Found Excel column " + tableColumn.Name + " at offset " + columnIndex.ToString());
// columnRelationship.ExcelColumnOffset = columnIndex;
// break;
// }
// }
// columnIndex++;
// }
// #region Throw an error if not all columns were found
// foreach (ColumnMapping columnRelationship in this._columnMappings)
// {
// if (!columnRelationship.ExcelColumnFound)
// {
// string message = "Unable to locate column '" + columnRelationship.ExcelColumnName + "' in table '" + ExcelTableName + "'.";
// ReportError(message, true);
// }
// }
// #endregion
// #endregion
if (worksheet == null)
{
ReportError("Sheet '" + ExcelSheetName + "' wasn't found in '" + workbookFileName + "'.", true);
}
else
{
string firstColumnHeader = "";
#region Read spreadsheet data into SSIS output buffer
SheetData sheetData = worksheet.GetFirstChild<SheetData>();
IEnumerable<Row> rows = sheetData.Elements<Row>();
#region Find First Row
UInt32 firstRow = 0;
// VerboseLog("Finding first row of table.");
VerboseLog("Finding first row of sheet.");
foreach (Row row in rows)
{
foreach (Cell cell in row.Elements<Cell>())
{
if (this.CellReferenceToCoordinates(cell.CellReference)[0] == 1)
{
if (this.GetCellValue(cell, sharedStringTablePart) == firstColumnHeader)
{
firstRow = row.RowIndex + 1;
}
}
}
}
// VerboseLog("First row of table is on row " + firstRow.ToString() + ".");
VerboseLog("First row of sheet is on row " + firstRow.ToString() + ".");
#endregion
// VerboseLog("Preparing to read " + rows.Count<Row>().ToString() + " table rows from Excel.");
VerboseLog("Preparing to read " + rows.Count<Row>().ToString() + " sheet rows from Excel.");
foreach (Row row in rows)
{
VerboseLog("Reading row " + row.RowIndex.ToString() + ".");
if (row.RowIndex < firstRow)
{
VerboseLog("Skipping non-table or header row.");
}
else
{
VerboseLog("Reading data row " + (row.RowIndex - 1).ToString() + ".");
bool rowAdded = false;
foreach (Cell cell in row.Elements<Cell>())
{
foreach (ColumnMapping columnRelationship in this._columnMappings)
{
if (this.CellReferenceToCoordinates(cell.CellReference)[0] == columnRelationship.ExcelColumnOffset)
{
string cellValue = this.GetCellValue(cell, sharedStringTablePart);
if ((cellValue == null)
|| ((cellValue == "") && columnRelationship.TreatBlanksAsNulls))
{
// do nothing
}
else
{
if (!rowAdded)
{
Output0Buffer.AddRow();
rowAdded = true;
}
VerboseLog("Excel column '" + columnRelationship.ExcelColumnName + "' contains '" + cellValue + "'.");
columnRelationship.SetSSISBuffer(this.GetCellValue(cell, sharedStringTablePart));
}
}
}
}
}
}
#endregion
}
}
#region catch ...
catch (Exception ex)
{
ReportError("Unable to open Excel file using OpenXML API: " + ex.Message, true);
}
#endregion
VerboseLog("Closing Excel file");
document.Close();
}
public void AddComponent(SsisComponent component)
{
if (_lastComponent != component.Component)
{
_lastComponent = component.Component;
_componentList.Add(component);
}
}
public static string GetValidationErrorMessage(IDTSComponentMetaData100 componentMetaData)
{
// Validate that TableName is specified in a format of <schema>.<table>
if (componentMetaData.CustomPropertyCollection["TableName"].Value == null)
{
return "Need to specify TableName in the Component Properties tab.";
}
else
{
string tableName = componentMetaData.CustomPropertyCollection["TableName"].Value.ToString();
if (!Regex.IsMatch(tableName, @"(.+)\.(.+)", RegexOptions.IgnoreCase))
{
return "TableName needs to include both schema and table name.";
}
}
// Validate that ErrorCode and ErrorColumn are included in the input selection
int errorCodeFound = 0;
int errorColumnFound = 0;
foreach (IDTSInputColumn100 column in componentMetaData.InputCollection[0].InputColumnCollection)
{
string columnName = column.Name;
if (Regex.IsMatch(columnName, "(.*?)ErrorCode$", RegexOptions.IgnoreCase)) errorCodeFound++;
if (Regex.IsMatch(columnName, "(.*?)ErrorColumn$", RegexOptions.IgnoreCase)) errorColumnFound++; ;
}
if (errorCodeFound == 0)
return "Need to include ErrorCode in the Input Columns tab.";
else if (errorCodeFound > 1)
return "Only one ErrorCode is allowed in the Input Columns tab.";
if (errorColumnFound == 0)
return "Need to include ErrorColumn in the Input Columns tab.";
else if (errorColumnFound > 1)
return "Only one ErrorColumn is allowed in the Input Columns tab.";
// No validation errror
return "";
}
public DataFlowComponentNodeFactory(IDTSComponentMetaData100 input, IDTSComponentMetaDataCollection100 metadata)
{
_input = input;
_metadata = metadata;
}
// Add this to the Validate method. This may impact performance so make sure to remove it from the Validation method after the upgrade is done.
public static void OneTimeUpgrade(IDTSComponentMetaData100 componentMetaData)
{
bool isExist = false;
isExist = false;
foreach (IDTSCustomProperty100 customProperty in componentMetaData.CustomPropertyCollection)
{
if (customProperty.Name == "TableName")
isExist = true;
}
if (!isExist)
{
IDTSCustomProperty100 TableName = componentMetaData.CustomPropertyCollection.New();
TableName.Name = "TableName";
TableName.Description = "Specifies the table name associated with the exception";
TableName.Value = (componentMetaData.Name.Split())[0].Replace("T@", "edfi.");
}
}
/// <summary>
/// Initialize is called before Edit() to give us the component metadata for the
/// component about to be edited, and an IServiceProvider where we can ask
/// for common VS and SSIS services, like creating connection managers... (see below)
/// </summary>
public void Initialize(IDTSComponentMetaData100 dtsComponentMetadata, IServiceProvider serviceProvider)
{
// Save off the component metadata and the service provider interfaces for later...
component_ = dtsComponentMetadata;
serviceProvider_ = serviceProvider;
}
public Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSBuffer100 CreateFlatBuffer64(UInt64 lSize,
IDTSComponentMetaData100 pOwner)
{
return _innerObject.CreateFlatBuffer64(lSize, pOwner);
}
public Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSBuffer100 CreateBuffer(Int32 hBufferType,
IDTSComponentMetaData100 pOwner)
{
return _innerObject.CreateBuffer(hBufferType, pOwner);
}
public void ConstructPackage(bool createNew)
{
// Create SSIS package
package = new Microsoft.SqlServer.Dts.Runtime.Package();
// Add DataFlow task
dataFlowTask = package.Executables.Add("STOCK:PipelineTask");
TaskHost taskhost = dataFlowTask as TaskHost;
taskhost.Name = "Data Flow Task";
pipeline = taskhost.InnerObject as MainPipe;
// Add source connection manager and adapter
if (SourceProvider == null)
{
throw new InvalidOperationException("Empty source provider.");
}
else
{
srcConnMgr = SourceProvider.AddConnectionManager(package);
srcComp = SourceProvider.AddSourceAdapter(pipeline, srcConnMgr);
}
// Add destination connection manager and adapter, create new table if asked to.
if (DestProvider == null)
{
throw new InvalidOperationException("Empty destination provider.");
}
else
{
if (createNew)
{
DestProvider.CreateDestination(srcComp.OutputCollection[0].OutputColumnCollection);
}
destConnMgr = DestProvider.AddConnectionManager(package);
destComp = DestProvider.AddDestAdapter(pipeline, destConnMgr, out destDesignTimeComp);
}
}