Example #1
0
        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);
        }
Example #2
0
        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);
        }