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 AddSourceAdapter(IDTSPipeline100 pipeline, ConnectionManager srcConnMgr)
{
if (String.IsNullOrEmpty(quotedTableName))
{
throw new ArgumentException("Source table name is empty");
}
IDTSComponentMetaData100 srcComp = pipeline.ComponentMetaDataCollection.New();
srcComp.ComponentClassID = OLEDB_SRC_GUID;
srcComp.ValidateExternalMetadata = true;
IDTSDesigntimeComponent100 srcDesignTimeComp = srcComp.Instantiate();
srcDesignTimeComp.ProvideComponentProperties();
srcComp.Name = "OleDB Source - Sql Server";
srcDesignTimeComp.SetComponentProperty("AccessMode", 0);
srcDesignTimeComp.SetComponentProperty("OpenRowset", quotedTableName);
// set connection
srcComp.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(srcConnMgr);
srcComp.RuntimeConnectionCollection[0].ConnectionManagerID = srcConnMgr.ID;
// get metadata
srcDesignTimeComp.AcquireConnections(null);
srcDesignTimeComp.ReinitializeMetaData();
srcDesignTimeComp.ReleaseConnections();
extCols = srcComp.OutputCollection[0].ExternalMetadataColumnCollection;
return(srcComp);
}
/// <summary>
/// Called to invoke the UI.
/// </summary>
/// <param name="parentWindow">The calling window</param>
/// <param name="variables">The SSIS variables</param>
/// <param name="connections">The SSIS connections</param>
/// <returns>True all works</returns>
bool IDtsComponentUI.Edit(IWin32Window parentWindow, Microsoft.SqlServer.Dts.Runtime.Variables variables, Microsoft.SqlServer.Dts.Runtime.Connections connections)
{
this.ClearErrors();
try
{
Debug.Assert(this.componentMetadata != null, "Original Component Metadata is not OK.");
this.designtimeComponent = this.componentMetadata.Instantiate();
Debug.Assert(this.designtimeComponent != null, "Design-time component object is not OK.");
// Cache the virtual input so the available columns are easily accessible.
this.LoadVirtualInput();
// Cache variables and connections.
this.variables = variables;
this.connections = connections;
// Here comes the UI that will be invoked in EditImpl virtual method.
return(this.EditImpl(parentWindow));
}
catch (Exception ex)
{
this.ReportErrors(ex);
return(false);
}
}
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);
}
private void TransferConnectionMetadata(ConnectionManager conn)
{
IDTSDesigntimeComponent100 designtimeComponent = this.componentMetadata.Instantiate();
this.componentMetadata.LocaleID = (int)conn.Properties["LocaleID"].GetValue(conn);
designtimeComponent.SetComponentProperty(PropertiesManager.IsUnicodePropName, conn.Properties["Unicode"].GetValue(conn));
designtimeComponent.SetComponentProperty(PropertiesManager.CodePagePropName, conn.Properties["CodePage"].GetValue(conn));
designtimeComponent.SetComponentProperty(PropertiesManager.ColumnNamesInFirstRowPropName, conn.Properties["ColumnNamesInFirstDataRow"].GetValue(conn));
designtimeComponent.SetComponentProperty(PropertiesManager.HeaderRowDelimiterPropName, conn.Properties["HeaderRowDelimiter"].GetValue(conn));
designtimeComponent.SetComponentProperty(PropertiesManager.HeaderRowsToSkipPropName, conn.Properties["HeaderRowsToSkip"].GetValue(conn));
designtimeComponent.SetComponentProperty(PropertiesManager.DataRowsToSkipPropName, conn.Properties["DataRowsToSkip"].GetValue(conn));
designtimeComponent.SetComponentProperty(PropertiesManager.TextQualifierPropName, conn.Properties["TextQualifier"].GetValue(conn));
CopyFlatFileColumns(conn, designtimeComponent);
}
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);
}
/// <summary>
/// Refresh meta data for a given component instance
/// </summary>
/// <param name="componentInstance"></param>
public static void RefreshMetaData(this IDTSDesigntimeComponent100 componentInstance)
{
try
{
if (componentInstance != null)
{
componentInstance.AcquireConnections(null);
componentInstance.ReinitializeMetaData();
componentInstance.ReleaseConnections();
return;
}
}
catch (Exception err)
{
throw new SsisScripterException("Exception caught when component refreshing meta data", err);
}
throw new SsisScripterException("The IDTSDesigntimeComponent100 object passed to this method was null");
}
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 override IDTSComponentMetaData100 AddSourceAdapter(IDTSPipeline100 pipeline, ConnectionManager srcConnMgr)
{
IDTSComponentMetaData100 srcComp = pipeline.ComponentMetaDataCollection.New();
srcComp.ComponentClassID = FLATFILE_SRC_GUID;
srcComp.ValidateExternalMetadata = true;
IDTSDesigntimeComponent100 srcDesignTimeComp = srcComp.Instantiate();
srcDesignTimeComp.ProvideComponentProperties();
srcComp.Name = "Flat File Source";
// set connection
srcComp.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(srcConnMgr);
srcComp.RuntimeConnectionCollection[0].ConnectionManagerID = srcConnMgr.ID;
// get metadata
srcDesignTimeComp.AcquireConnections(null);
srcDesignTimeComp.ReinitializeMetaData();
srcDesignTimeComp.ReleaseConnections();
return(srcComp);
}
private void CopyFlatFileColumns(ConnectionManager conn, IDTSDesigntimeComponent100 designtimeComponent)
{
int codePage = (int)conn.Properties["CodePage"].GetValue(conn);
IDTSConnectionManagerFlatFileColumns100 columns = conn.Properties["Columns"].GetValue(conn) as IDTSConnectionManagerFlatFileColumns100;
if (columns.Count > 1)
{
designtimeComponent.SetComponentProperty(PropertiesManager.ColumnDelimiterPropName, columns[0].ColumnDelimiter);
}
if (columns.Count > 0)
{
designtimeComponent.SetComponentProperty(PropertiesManager.RowDelimiterPropName, columns[columns.Count - 1].ColumnDelimiter);
}
int columnIndex = 0;
IDTSOutput100 mainOutput = GetMainOutput();
foreach (IDTSConnectionManagerFlatFileColumn100 column in columns)
{
AddColumn(designtimeComponent, column, mainOutput.ID, columnIndex, codePage);
columnIndex++;
}
}
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 IDTSComponentMetaData100 AddSourceAdapter(IDTSPipeline100 pipeline, ConnectionManager srcConnMgr)
{
IDTSComponentMetaData100 srcComp = pipeline.ComponentMetaDataCollection.New();
srcComp.ComponentClassID = EXCEL_SRC_GUID;
srcComp.ValidateExternalMetadata = true;
IDTSDesigntimeComponent100 srcDesignTimeComp = srcComp.Instantiate();
srcDesignTimeComp.ProvideComponentProperties();
srcComp.Name = "OleDB Source - Excel";
srcDesignTimeComp.SetComponentProperty("AccessMode", 0);
// OleDb provider does not recognize OpenRowSet name quoted with `
srcDesignTimeComp.SetComponentProperty("OpenRowset", UnquotedSheetName);
// set connection
srcComp.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(srcConnMgr);
srcComp.RuntimeConnectionCollection[0].ConnectionManagerID = srcConnMgr.ID;
// get metadata
srcDesignTimeComp.AcquireConnections(null);
srcDesignTimeComp.ReinitializeMetaData();
srcDesignTimeComp.ReleaseConnections();
extCols = srcComp.OutputCollection[0].ExternalMetadataColumnCollection;
return(srcComp);
}
bool IDtsComponentUI.Edit(System.Windows.Forms.IWin32Window parentWindow, Microsoft.SqlServer.Dts.Runtime.Variables variables, Microsoft.SqlServer.Dts.Runtime.Connections connections)
{
this.ClearErrors();
try
{
Debug.Assert(this.componentMetadata != null, "Original Component Metadata is not OK.");
this.designtimeComponent = this.componentMetadata.Instantiate();
Debug.Assert(this.designtimeComponent != null, "Design-time component object is not OK.");
//Cache the virtual input
this.LoadVirtualInput();
//cache variables and connections
this.variables = variables;
this.connections = connections;
return(EditImpl(parentWindow));
}
catch (Exception ex)
{
this.ReportErrors(ex);
return(false);
}
}
bool IDtsComponentUI.Edit(System.Windows.Forms.IWin32Window parentWindow, Microsoft.SqlServer.Dts.Runtime.Variables variables, Microsoft.SqlServer.Dts.Runtime.Connections connections)
{
this.ClearErrors();
try
{
Debug.Assert(this.componentMetadata != null, "Original Component Metadata is not OK.");
this.designtimeComponent = this.componentMetadata.Instantiate();
Debug.Assert(this.designtimeComponent != null, "Design-time component object is not OK.");
//Cache the virtual input
this.LoadVirtualInput();
//cache variables and connections
this.variables = variables;
this.connections = connections;
return EditImpl(parentWindow);
}
catch (Exception ex)
{
this.ReportErrors(ex);
return false;
}
}
public abstract IDTSComponentMetaData100 AddDestAdapter(IDTSPipeline100 pipeline, ConnectionManager destConnMgr, out IDTSDesigntimeComponent100 destDesignTimeComp);
private void CopyFlatFileColumns(ConnectionManager conn, IDTSDesigntimeComponent100 designtimeComponent)
{
int codePage = (int)conn.Properties["CodePage"].GetValue(conn);
IDTSConnectionManagerFlatFileColumns100 columns = conn.Properties["Columns"].GetValue(conn) as IDTSConnectionManagerFlatFileColumns100;
if (columns.Count > 1)
{
designtimeComponent.SetComponentProperty(PropertiesManager.ColumnDelimiterPropName, columns[0].ColumnDelimiter);
}
if (columns.Count > 0)
{
designtimeComponent.SetComponentProperty(PropertiesManager.RowDelimiterPropName, columns[columns.Count - 1].ColumnDelimiter);
}
int columnIndex = 0;
IDTSOutput100 mainOutput = GetMainOutput();
foreach (IDTSConnectionManagerFlatFileColumn100 column in columns)
{
AddColumn(designtimeComponent, column, mainOutput.ID, columnIndex, codePage);
columnIndex++;
}
}
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 IDTSComponentMetaData100 AddDestAdapter(IDTSPipeline100 pipeline, ConnectionManager destConnMgr, out IDTSDesigntimeComponent100 destDesignTimeComp)
{
IDTSComponentMetaData100 destComp = pipeline.ComponentMetaDataCollection.New();
destComp.ComponentClassID = FLATFILE_DEST_GUID;
destComp.ValidateExternalMetadata = true;
destDesignTimeComp = destComp.Instantiate();
destDesignTimeComp.ProvideComponentProperties();
destComp.Name = "Flat File Destination";
destDesignTimeComp.SetComponentProperty("Overwrite", true);
// set connection
destComp.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(destConnMgr);
destComp.RuntimeConnectionCollection[0].ConnectionManagerID = destConnMgr.ID;
// get metadata
destDesignTimeComp.AcquireConnections(null);
destDesignTimeComp.ReinitializeMetaData();
destDesignTimeComp.ReleaseConnections();
return(destComp);
}
internal BipsProxyIDTSDesigntimeComponent100(IDTSDesigntimeComponent100 innerObject)
{
_innerObject = innerObject;
}
public override IDTSComponentMetaData100 AddDestAdapter(IDTSPipeline100 pipeline, ConnectionManager destConnMgr, out IDTSDesigntimeComponent100 destDesignTimeComp)
{
IDTSComponentMetaData100 destComp = pipeline.ComponentMetaDataCollection.New();
destComp.ComponentClassID = EXCEL_DEST_GUID;
destComp.ValidateExternalMetadata = true;
destDesignTimeComp = destComp.Instantiate();
destDesignTimeComp.ProvideComponentProperties();
destComp.Name = "OleDB Destination - Excel";
destDesignTimeComp.SetComponentProperty("AccessMode", 0);
// OleDb provider does not recognize OpenRowSet name quoted with `
destDesignTimeComp.SetComponentProperty("OpenRowset", UnquotedSheetName);
// 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 abstract IDTSComponentMetaData100 AddDestAdapter(IDTSPipeline100 pipeline, ConnectionManager destConnMgr, out IDTSDesigntimeComponent100 destDesignTimeComp);
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 IDTSComponentMetaData100 AddDestAdapter(IDTSPipeline100 pipeline, ConnectionManager destConnMgr, out IDTSDesigntimeComponent100 destDesignTimeComp)
{
IDTSComponentMetaData100 destComp = pipeline.ComponentMetaDataCollection.New();
destComp.ComponentClassID = FLATFILE_DEST_GUID;
destComp.ValidateExternalMetadata = true;
destDesignTimeComp = destComp.Instantiate();
destDesignTimeComp.ProvideComponentProperties();
destComp.Name = "Flat File Destination";
destDesignTimeComp.SetComponentProperty("Overwrite", true);
// set connection
destComp.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(destConnMgr);
destComp.RuntimeConnectionCollection[0].ConnectionManagerID = destConnMgr.ID;
// get metadata
destDesignTimeComp.AcquireConnections(null);
destDesignTimeComp.ReinitializeMetaData();
destDesignTimeComp.ReleaseConnections();
return destComp;
}