public SSISExcelDestination(ExcelDestination dst, MainPipe pipe, ConnectionManager cm, ILogger logger, Application app)
: base(pipe, "Excel Destination", logger, app)
{
_dst = dst;
_cm = cm;
}
private Package BuildPackage()
{
try
{
PrintOutput.PrintToOutput("DE building the Package...", DERun.Debug);
//SSISEvents ev = new SSISEvents();
//m_p.DesignEvents = ev;
Executable ex = m_p.Executables.Add("STOCK:PipelineTask");
MainPipe pipe = (MainPipe)(((TaskHost)ex).InnerObject);
// Set the IDTSComponentEvent handler to capture the details from any
// COMExceptions raised during package generation
SSISEventHandler events = new SSISEventHandler();
pipe.Events = DtsConvert.GetExtendedInterface(events as IDTSComponentEvents);
// Add variable to point to staging area root
dsv dsv = null;
if (!String.IsNullOrEmpty(m_movedata.StagingAreaRoot))
{
m_p.Variables.Add("StagingAreaRoot", true, "", m_movedata.StagingAreaRoot);
dsv = new dsv(m_movedata.StagingAreaRoot);
}
// Add variable RowCount
m_p.Variables.Add("RowCount", false, "", 0);
IDTSComponentMetaData100 src = null;
//create FlatFile source
if (m_movedata.DataSource.Type == SourceType.FlatFile)
{
// use dsv as external metadata
bool bFound = false;
if (dsv != null)
{
bFound = dsv.FindTable(m_movedata.DataSource.FlatFileSource.CustomProperties.StagingAreaTableName);
if (!bFound)
{
PrintOutput.PrintToOutput("Warning: DsvSchemaTable is not found", DERun.Debug);
//throw new DsvTableNotFound(m_movedata.StagingAreaRoot,m_movedata.DataSource.FlatFileSource.CustomProperties.StagingAreaTableName);
}
}
//Connection manager
ConnectionManager cm = m_p.Connections.Add("FLATFILE");
Dictionary <string, MyColumn> colCollection = (bFound) ? dsv.ColumnCollection : null;
SSISFlatFileConnection.ConfigureConnectionManager(cm, m_movedata.DataSource.FlatFileSource.CustomProperties.FlatFileConnectionProperties, colCollection);
SSISFlatFileSource ssissource = new SSISFlatFileSource(m_movedata.DataSource.FlatFileSource, pipe, cm);
src = ssissource.MetadataCollection;
}
//create Excel source
else if (m_movedata.DataSource.Type == SourceType.Excel)
{
//Connection manager
ConnectionManager cm = m_p.Connections.Add("EXCEL");
SSISExcelSource ssissource = new SSISExcelSource(m_movedata.DataSource.ExcelSource, pipe, cm);
src = ssissource.MetadataCollection;
}
//create SharePoint source
else if (m_movedata.DataSource.Type == SourceType.SPList)
{
SSISSharePointSource ssissource = new SSISSharePointSource(m_movedata.DataSource.SharePointSource, pipe);
src = ssissource.MetadataCollection;
}
//create OleDb source
else if (m_movedata.DataSource.Type == SourceType.OleDb)
{
//Add variable for SQL query if access mode is 3
m_p.Variables.Add("srcSelect", true, "", m_movedata.DataSource.OleDbSource.CustomProperties.SqlCommand);
//Connection manager
ConnectionManager cm = m_p.Connections.Add("OLEDB");
SSISOleDbSource ssissource = new SSISOleDbSource(m_movedata.DataSource.OleDbSource, pipe, cm);
src = ssissource.MetadataCollection;
}
//create AdoNet source
else if (m_movedata.DataSource.Type == SourceType.AdoNet)
{
//Connection manager
ConnectionManager cm = m_p.Connections.Add("ADO.NET");
SSISAdoNetSource ssissource = new SSISAdoNetSource(m_movedata.DataSource.AdoNetSource, pipe, cm);
src = ssissource.MetadataCollection;
}
//create Odbc source
else if (m_movedata.DataSource.Type == SourceType.Odbc)
{
//Connection manager
ConnectionManager cm = m_p.Connections.Add("ODBC");
SSISOdbcSource ssissource = new SSISOdbcSource(m_movedata.DataSource.OdbcSource, pipe, cm);
src = ssissource.MetadataCollection;
}
else
{
throw new UnknownSourceType();
}
//create and connect rowcount to the source
SSISRowCount ssiscount = new SSISRowCount(pipe, src);
src = ssiscount.MetadataCollection;
if (m_movedata.Partition == null || String.IsNullOrEmpty(m_movedata.Partition.Function) || m_movedata.Partition.Function == "NONE")
{
//create and connect multicast to the rowcount
SSISMultiCast ssissplit = new SSISMultiCast(pipe, src);
src = ssissplit.MetadataCollection;
}
else
{
//create and connect partition data custom component
SSISPartitionColumn ssispcol = new SSISPartitionColumn(pipe, src, m_movedata);
src = ssispcol.MetadataCollection;
//create and connect a partition splitter
SSISPartitionSplit ssissplit = new SSISPartitionSplit(pipe, src, m_movedata);
src = ssissplit.MetadataCollection;
}
//connect none partition destinations to multicast
//connect partition destinations to partition splitter
CManagedComponentWrapper dsrc = src.Instantiate();
foreach (object odst in m_movedata.DataDestination.Destinations)
{
//FlatFile Destinations
if (((IDeDestination)odst).Type == DestinationType.FlatFile)
{
FlatFileDestination dst = (FlatFileDestination)odst;
// use dsv as external metadata
bool bFound = false;
if (dsv != null)
{
bFound = dsv.FindTable(dst.CustomProperties.StagingAreaTableName);
if (!bFound)
{
PrintOutput.PrintToOutput("Warning: DsvSchemaTable is not found", DERun.Debug);
//throw new DsvTableNotFound(m_movedata.StagingAreaRoot, dst.CustomProperties.StagingAreaTableName);
}
}
IDTSOutput100 output = ConfigureOutput(src, dst, dsrc);
//Connection manager
ConnectionManager cm = m_p.Connections.Add("FLATFILE");
//cm.Name = String.Format(CultureInfo.InvariantCulture, "FlatFile Destination Connection Manager {0}", output.ID);
Dictionary <string, MyColumn> colCollection = (bFound) ? dsv.ColumnCollection : getColumnCollectionFromPipe(src);
SSISFlatFileConnection.ConfigureConnectionManager(cm, dst.CustomProperties.FlatFileConnectionProperties, colCollection);
SSISFlatFileDestination ssisdest = new SSISFlatFileDestination(dst, pipe, src, output.ID, cm);
}
// OleDb Destinations
if (((IDeDestination)odst).Type == DestinationType.OleDb)
{
OleDbDestination dst = (OleDbDestination)odst;
IDTSOutput100 output = ConfigureOutput(src, dst, dsrc);
//Connection manager
ConnectionManager cm = m_p.Connections.Add("OLEDB");
//cm.Name = String.Format(CultureInfo.InvariantCulture, "OLEDB Destination Connection Manager {0}", output.ID);
SSISOleDbDestination ssisdest = new SSISOleDbDestination(dst, pipe, src, output.ID, cm);
}
//ExcelDestinations
if (((IDeDestination)odst).Type == DestinationType.Excel)
{
ExcelDestination dst = (ExcelDestination)odst;
IDTSOutput100 output = ConfigureOutput(src, dst, dsrc);
//Connection manager
ConnectionManager cm = m_p.Connections.Add("EXCEL");
//cm.Name = String.Format(CultureInfo.InvariantCulture, "Excel Destination Connection Manager {0}", output.ID);
SSISExcelDestination ssisdest = new SSISExcelDestination(dst, pipe, src, output.ID, cm);
}
// Create SharePointDestinations
if (((IDeDestination)odst).Type == DestinationType.SPList)
{
SharePointDestination dst = (SharePointDestination)odst;
IDTSOutput100 output = ConfigureOutput(src, dst, dsrc);
SSISSharePointDestination ssisdest = new SSISSharePointDestination(dst, pipe, src, output.ID);
}
// Ado Net Destinations
if (((IDeDestination)odst).Type == DestinationType.AdoNet)
{
AdoNetDestination dst = (AdoNetDestination)odst;
IDTSOutput100 output = ConfigureOutput(src, dst, dsrc);
//Connection manager
ConnectionManager cm = m_p.Connections.Add("ADO.NET");
//cm.Name = String.Format(CultureInfo.InvariantCulture, "ADONET Destination Connection Manager {0}", output.ID);
SSISAdoNetDestination ssisdest = new SSISAdoNetDestination(dst, pipe, src, output.ID, cm);
}
// Odbc Destinations
if (((IDeDestination)odst).Type == DestinationType.Odbc)
{
OdbcDestination dst = (OdbcDestination)odst;
IDTSOutput100 output = ConfigureOutput(src, dst, dsrc);
//Connection manager
ConnectionManager cm = m_p.Connections.Add("ODBC");
//cm.Name = String.Format(CultureInfo.InvariantCulture, "ODBC Destination Connection Manager {0}", output.ID);
SSISOdbcDestination ssisdest = new SSISOdbcDestination(dst, pipe, src, output.ID, cm);
}
// SqlBulk Destinations
if (((IDeDestination)odst).Type == DestinationType.SqlBulk)
{
SqlBulkDestination dst = (SqlBulkDestination)odst;
IDTSOutput100 output = ConfigureOutput(src, dst, dsrc);
//Connection manager
ConnectionManager cm = m_p.Connections.Add("OLEDB");
//cm.Name = String.Format(CultureInfo.InvariantCulture, "OLEDB Destination Connection Manager {0}", output.ID);
SSISSqlBulkDestination ssisdest = new SSISSqlBulkDestination(dst, pipe, src, output.ID, cm);
}
}
PrintOutput.PrintToOutput("DE Package is ready", DERun.Debug);
}
//catch (COMException cexp)
catch (Exception cexp)
{
PrintOutput.PrintToError("Exception occured : " + cexp.TargetSite + cexp);
StringBuilder dtserrors = new StringBuilder();
foreach (DtsError error in m_p.Errors)
{
//PrintOutput.PrintToError(error.Description);
dtserrors.AppendLine(error.Description);
}
throw new UnexpectedSsisException(dtserrors.ToString());
}
finally
{
//Save the Package to XML
if (!(m_movedata.SavePackage == null) && m_movedata.SavePackage.Save)
{
if (String.IsNullOrEmpty(m_movedata.SavePackage.File))
{
PrintOutput.PrintToError("No location to save the package was supplied. Package will not be saved to XML.");
}
else
{
app.SaveToXml(m_movedata.SavePackage.File, m_p, null);
}
}
}
return(m_p);
}
private Package BuildPackage()
{
Application app = new Application();
_logger.Debug($"component store path: {app.ComponentStorePath}");
Package package = new Package();
lock (_lockBuild)
{
package.Name = Guid.NewGuid().ToString();
try
{
_logger.Debug("DE building the Package {Name}...", package.Name);
//SSISEvents ev = new SSISEvents();
//_p.DesignEvents = ev;
//Executable ex = package.Executables.Add("SSIS.Pipeline");
Executable ex = package.Executables.Add("STOCK:PipelineTask");
TaskHost host = ex as TaskHost;
host.Name = $"DE Data Flow Task {package.ID}";
MainPipe pipe = host.InnerObject as MainPipe;
_logger.Debug($"package creation name: {package.CreationName}");
_logger.Debug($"host creation name: {host.CreationName}");
// Set the IDTSComponentEvent handler to capture the details from any
// COMExceptions raised during package generation
SSISEventHandler events = new SSISEventHandler(_logger);
pipe.Events = DtsConvert.GetExtendedInterface(events as IDTSComponentEvents);
// Add variable to point to staging area root
Dsv dsv = null;
if (!String.IsNullOrEmpty(_movedata.StagingAreaRoot))
{
package.Variables.Add("StagingAreaRoot", true, "", _movedata.StagingAreaRoot);
dsv = new Dsv(_movedata.StagingAreaRoot, _logger);
}
// Add variable RowCount
package.Variables.Add("RowCount", false, "", 0);
IDTSComponentMetaData100 src = null;
IDTSComponentMetaData100 current = null;
ISSISModule module;
//create FlatFile source
if (_movedata.DataSource.Type == SourceType.FlatFile)
{
// use dsv as external metadata
bool bFound = false;
if (dsv != null)
{
bFound = dsv.FindTable(_movedata.DataSource.FlatFileSource.CustomProperties.StagingAreaTableName);
if (!bFound)
{
_logger.Debug("Warning: DsvSchemaTable is not found");
//throw new DsvTableNotFound(_movedata.StagingAreaRoot,_movedata.DataSource.FlatFileSource.CustomProperties.StagingAreaTableName);
}
}
//Connection manager
ConnectionManager cm = package.Connections.Add("FLATFILE");
Dictionary <string, MyColumn> colCollection = (bFound) ? dsv.ColumnCollection : null;
SSISFlatFileConnection.ConfigureConnectionManager(cm, _movedata.DataSource.FlatFileSource.CustomProperties.FlatFileConnectionProperties, colCollection, _logger);
module = new SSISFlatFileSource(_movedata.DataSource.FlatFileSource, pipe, cm, _logger, app);
src = module.Initialize();
}
//create Excel source
else if (_movedata.DataSource.Type == SourceType.Excel)
{
//Connection manager
ConnectionManager cm = package.Connections.Add("EXCEL");
module = new SSISExcelSource(_movedata.DataSource.ExcelSource, pipe, cm, _logger, app);
src = module.Initialize();
}
//create SharePoint source
else if (_movedata.DataSource.Type == SourceType.SPList)
{
module = new SSISSharePointSource(_movedata.DataSource.SharePointSource, pipe, _logger, app);
src = module.Initialize();
}
//create OleDb source
else if (_movedata.DataSource.Type == SourceType.OleDb)
{
//Add variable for SQL query if access mode is 3
package.Variables.Add("srcSelect", true, "", _movedata.DataSource.OleDbSource.CustomProperties.SqlCommand);
//Connection manager
ConnectionManager cm = package.Connections.Add("OLEDB");
module = new SSISOleDbSource(_movedata.DataSource.OleDbSource, pipe, cm, _logger, app);
src = module.Initialize();
}
//create AdoNet source
else if (_movedata.DataSource.Type == SourceType.AdoNet)
{
//Connection manager
ConnectionManager cm = package.Connections.Add("ADO.NET");
module = new SSISAdoNetSource(_movedata.DataSource.AdoNetSource, pipe, cm, _logger, app);
src = module.Initialize();
}
//create Odbc source
else if (_movedata.DataSource.Type == SourceType.Odbc)
{
//Connection manager
ConnectionManager cm = package.Connections.Add("ODBC");
module = new SSISOdbcSource(_movedata.DataSource.OdbcSource, pipe, cm, _logger, app);
src = module.Initialize();
}
//create OData source
else if (_movedata.DataSource.Type == SourceType.OData)
{
//Connection manager
ConnectionManager cm = package.Connections.Add("ODATA");
module = new SSISODataSource(_movedata.DataSource.ODataSource, pipe, cm, _logger, app);
src = module.Initialize();
}
else
{
throw new UnknownSourceType();
}
//create and connect rowcount to the source
module = new SSISRowCount(pipe, _logger, app);
current = module.Initialize();
src = module.Connect(src);
if (_movedata.Partition == null || String.IsNullOrEmpty(_movedata.Partition.Function) || _movedata.Partition.Function == "NONE")
{
//create and connect multicast to the rowcount
module = new SSISMultiCast(pipe, _logger, app);
current = module.Initialize();
src = module.Connect(src);
}
else
{
//create and connect partition data custom component
module = new SSISPartitionColumn(_movedata, pipe, _logger, app);
current = module.Initialize();
src = module.Connect(src);
//create and connect a partition splitter
module = new SSISPartitionSplit(_movedata, pipe, _logger, app);
current = module.Initialize();
src = module.Connect(src);
}
//connect none partition destinations to multicast
//connect partition destinations to partition splitter
CManagedComponentWrapper dsrc = src.Instantiate();
foreach (object odst in _movedata.DataDestination.Destinations)
{
//FlatFile Destinations
if (((IDeDestination)odst).Type == DestinationType.FlatFile)
{
FlatFileDestination dst = (FlatFileDestination)odst;
// use dsv as external metadata
bool bFound = false;
if (dsv != null)
{
bFound = dsv.FindTable(dst.CustomProperties.StagingAreaTableName);
if (!bFound)
{
_logger.Information("Warning: DsvSchemaTable is not found");
//throw new DsvTableNotFound(_movedata.StagingAreaRoot, dst.CustomProperties.StagingAreaTableName);
}
}
IDTSOutput100 output = ConfigureOutput(src, dst, dsrc);
//Connection manager
ConnectionManager cm = package.Connections.Add("FLATFILE");
//cm.Name = String.Format(CultureInfo.InvariantCulture, "FlatFile Destination Connection Manager {0}", output.ID);
Dictionary <string, MyColumn> colCollection = (bFound) ? dsv.ColumnCollection : getColumnCollectionFromPipe(src);
SSISFlatFileConnection.ConfigureConnectionManager(cm, dst.CustomProperties.FlatFileConnectionProperties, colCollection, _logger);
module = new SSISFlatFileDestination(dst, pipe, cm, _logger, app);
current = module.Initialize();
current = module.ConnectDestination(src, dst.DataTypeAutoConvert, output.ID);
}
// OleDb Destinations
if (((IDeDestination)odst).Type == DestinationType.OleDb)
{
OleDbDestination dst = (OleDbDestination)odst;
IDTSOutput100 output = ConfigureOutput(src, dst, dsrc);
//Connection manager
ConnectionManager cm = package.Connections.Add("OLEDB");
//cm.Name = String.Format(CultureInfo.InvariantCulture, "OLEDB Destination Connection Manager {0}", output.ID);
module = new SSISOleDbDestination(dst, pipe, cm, _logger, app);
current = module.Initialize();
current = module.ConnectDestination(src, dst.DataTypeAutoConvert, output.ID);
}
//ExcelDestinations
if (((IDeDestination)odst).Type == DestinationType.Excel)
{
ExcelDestination dst = (ExcelDestination)odst;
IDTSOutput100 output = ConfigureOutput(src, dst, dsrc);
//Connection manager
ConnectionManager cm = package.Connections.Add("EXCEL");
//cm.Name = String.Format(CultureInfo.InvariantCulture, "Excel Destination Connection Manager {0}", output.ID);
module = new SSISExcelDestination(dst, pipe, cm, _logger, app);
current = module.Initialize();
current = module.ConnectDestination(src, dst.DataTypeAutoConvert, output.ID);
}
// Create SharePointDestinations
if (((IDeDestination)odst).Type == DestinationType.SPList)
{
SharePointDestination dst = (SharePointDestination)odst;
IDTSOutput100 output = ConfigureOutput(src, dst, dsrc);
module = new SSISSharePointDestination(dst, pipe, _logger, app);
current = module.Initialize();
current = module.ConnectDestination(src, dst.DataTypeAutoConvert, output.ID);
}
// Ado Net Destinations
if (((IDeDestination)odst).Type == DestinationType.AdoNet)
{
AdoNetDestination dst = (AdoNetDestination)odst;
IDTSOutput100 output = ConfigureOutput(src, dst, dsrc);
//Connection manager
ConnectionManager cm = package.Connections.Add("ADO.NET");
//cm.Name = String.Format(CultureInfo.InvariantCulture, "ADONET Destination Connection Manager {0}", output.ID);
module = new SSISAdoNetDestination(dst, pipe, cm, _logger, app);
current = module.Initialize();
current = module.ConnectDestination(src, dst.DataTypeAutoConvert, output.ID);
}
// Odbc Destinations
if (((IDeDestination)odst).Type == DestinationType.Odbc)
{
OdbcDestination dst = (OdbcDestination)odst;
IDTSOutput100 output = ConfigureOutput(src, dst, dsrc);
//Connection manager
ConnectionManager cm = package.Connections.Add("ODBC");
//cm.Name = String.Format(CultureInfo.InvariantCulture, "ODBC Destination Connection Manager {0}", output.ID);
module = new SSISOdbcDestination(dst, pipe, cm, _logger, app);
current = module.Initialize();
current = module.ConnectDestination(src, dst.DataTypeAutoConvert, output.ID);
}
// SqlBulk Destinations
if (((IDeDestination)odst).Type == DestinationType.SqlBulk)
{
SqlBulkDestination dst = (SqlBulkDestination)odst;
IDTSOutput100 output = ConfigureOutput(src, dst, dsrc);
//Connection manager
ConnectionManager cm = package.Connections.Add("OLEDB");
//cm.Name = String.Format(CultureInfo.InvariantCulture, "OLEDB Destination Connection Manager {0}", output.ID);
module = new SSISSqlBulkDestination(dst, pipe, cm, _logger, app);
current = module.Initialize();
current = module.ConnectDestination(src, dst.DataTypeAutoConvert, output.ID);
}
}
_logger.Debug("DE Package is ready");
}
//catch (COMException cexp)
catch (Exception cexp)
{
_logger.Error(cexp, "Exception occured: {Target}.", cexp.TargetSite);
//StringBuilder dtserrors = new StringBuilder();
foreach (DtsError error in package.Errors)
{
_logger.Error("Error: {Desc}, {ErrorCode}", error.Description, error.ErrorCode);
//dtserrors.AppendLine(error.Description);
}
if (package != null)
{
package.Dispose();
}
throw new UnexpectedSsisException("Failed to build SSIS package.");
}
finally
{
//Save the Package to XML
if (!(_movedata.SavePackage == null) && _movedata.SavePackage.Save)
{
if (String.IsNullOrEmpty(_movedata.SavePackage.File))
{
_logger.Information("No location to save the package was supplied. Package will not be saved to XML.");
}
else
{
app.SaveToXml(_movedata.SavePackage.File, package, null);
}
}
}
}
return(package);
}
public SSISExcelDestination(ExcelDestination dbdst, MainPipe pipe, IDTSComponentMetaData100 src, int outputID, ConnectionManager cm)
: base(pipe, "Excel Destination", outputID)
{
//create ole db destination component
//set connection properties
cm.Name = String.Format(CultureInfo.InvariantCulture, "Excel Destination Connection Manager {0}", outputID);
cm.ConnectionString = dbdst.ConnectionString;
cm.Description = dbdst.Description;
//mwrt.IDTSConnectionManagerExcel100 ecm = cm.InnerObject as mwrt.IDTSConnectionManagerExcel100;
//ecm.ExcelFilePath = dbdst.FilePath;
//ecm.FirstRowHasColumnName = dbdst.Header;
//ecm.ExcelVersionNumber = mwrt.DTSExcelVersion.DTSExcelVer_2007;
IDTSComponentMetaData100 comp = this.MetadataCollection;
CManagedComponentWrapper dcomp = comp.Instantiate();
// Set oledb destination custom properties
//default to openrowset
dcomp.SetComponentProperty("AccessMode", 0);
//foreach (KeyValuePair<string, object> prop in dbdst.CustomProperties.CustomPropertyCollection.InnerArrayList)
foreach (KeyValuePair <string, object> prop in dbdst.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);
}
this.Reinitialize(dcomp);
//Create datatype converter if needed
Dictionary <string, int> converted = new Dictionary <string, int>();
IDTSVirtualInput100 vInput = src.InputCollection[0].GetVirtualInput();
if (this.needDataTypeChange(vInput, comp.InputCollection[0]))
{
//create the destination column collection
Dictionary <string, MyColumn> exColumns = new Dictionary <string, MyColumn>();
foreach (IDTSExternalMetadataColumn100 exColumn in comp.InputCollection[0].ExternalMetadataColumnCollection)
{
MyColumn col = new MyColumn();
col.Name = exColumn.Name;
col.DataType = exColumn.DataType;
col.Length = exColumn.Length;
col.Precision = exColumn.Precision;
col.Scale = exColumn.Scale;
col.CodePage = exColumn.CodePage;
exColumns.Add(exColumn.Name, col);
}
SSISDataConverter ssisdc = new SSISDataConverter(pipe, src, outputID, exColumns);
src = ssisdc.MetadataCollection;
converted = ssisdc.ConvertedColumns;
outputID = 0;
}
this.ConnectComponents(src, outputID);
this.MatchInputColumns(converted, true);
}