微软BI开拓者

请问如何能利用C#去访问到IS的控件.我现在需要做的是IS上二次开发.利用C#把一些现在需要做清洗转换的需求封装成模块.但是不知道如何去连接到IS的控件.请指导.

参考:
http://www.windbi.com/showtopic-342.aspx

兄弟,打不开呀.怎么办?

包我知道怎么访问．我想知道的是如何在Ｃ＃里面去调用ＩＳ里面的控件．例如派生列．不过还是很感谢你．

你的需求挺怪的

完全可以实现,下面的例子是c#创建了一个IS包,调用了IS里面的控件,你可以自定义对各控件进行控制,代码如下:


using System;
using System.IO;
using Microsoft.SqlServer.Dts.Runtime;              //  Managed Runtime namespace          [Microsoft.SqlServer.ManagedDTS.dll]
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;    //  Pipeline Primary Interop Assembly  [Microsoft.SqlServer.DTSPipelineWrap.dll]
using wrap = Microsoft.SqlServer.Dts.Runtime.Wrapper;

namespace Microsoft.Samples.SqlServer.SSIS
{
    public class OleDBToFlatFile
    {
        const string PACKAGEFILENAME = @"\SampleRuntimePackage.dtsx";
        const string DATAFILENAME = @"\SampleRuntimeData.txt";
        const string LOGFILENAME = @"\SampleRuntimeLog.log";

        private Package package;                                // package object
        private MainPipe dataFlow;                              // dataFlow task object
        private IDTSComponentMetaData90 oledbSource;            // OLEDB dataFlow component object
        private IDTSComponentMetaData90 sort;                  // Sort transform dataFlow component object
        private IDTSComponentMetaData90 flatfileDestination;    // FlatFile dataFlow component object
        private PackageEvents packageEvents;            // class that implements the Package events interface IDTSEvents90
        private ComponentEvents pipelineEvents;        // class that implements the component events interface IDTSComponentEvents90

        #region Constructor
        public OleDBToFlatFile(string sortColumnName)
        {
            if (string.IsNullOrEmpty(sortColumnName))
            {
                sortColumnName = "Name";
            }

            string currentDirectory = Directory.GetCurrentDirectory();

            // Delete the log file, destination, and package files.
            File.Delete(currentDirectory + LOGFILENAME);
            File.Delete(currentDirectory + DATAFILENAME);
            File.Delete(currentDirectory + PACKAGEFILENAME);

            package = CreatePackage("SSISPackage", "The package for the SSIS CreatePackage sample");
            package.Variables.Add("SortColumn", false, "", sortColumnName);

            // Events.
            packageEvents = new PackageEvents();
            pipelineEvents = new ComponentEvents();

            // Add the Runtime objects.
            AddLogging(currentDirectory + LOGFILENAME);
            AddConnectionManagers(currentDirectory);
            AddDataFlowTask();

            // Add the DataFlow components.
            AddOLEDBSource();
            AddSort();
            AddFlatFileDestination();

            // Validate the layout of the package.
            DTSExecResult status = package.Validate(null, null, packageEvents, null);
            System.Console.WriteLine("Validation result: " + status);

            // Save the package
            Application a = new Application();
            a.SaveToXml(currentDirectory + PACKAGEFILENAME, package, packageEvents);
            Console.WriteLine("Package saved to " + currentDirectory + PACKAGEFILENAME);

            // If the package validated successfully, then execute it.
            if (status == DTSExecResult.Success)
            {
                Console.WriteLine("Beginning package execution.");
                // Execute the package
                DTSExecResult result = package.Execute(null, null, packageEvents, null, null);
                Console.WriteLine("Execution result: " + result);
            }

            if (File.Exists(currentDirectory + DATAFILENAME))
            {
                Console.WriteLine("Sample data saved to " + currentDirectory + DATAFILENAME);
            }
        }
        #endregion

        #region CreatePackage
        private static Package CreatePackage(string Name, string Description)
        {
            Package p = new Package();
            p.PackageType = DTSPackageType.DTSDesigner90;
            p.Name = Name;
            p.Description = Description;
            p.CreatorComputerName = System.Environment.MachineName;
            p.CreatorName = System.Environment.UserName;

            return p;
        }
        #endregion

        #region AddLogging
        /// <summary>
        /// Enable package level logging.
        /// </summary>
        private void AddLogging(string Path)
        {
            try
            {
                package.LoggingMode = DTSLoggingMode.Enabled;

                // Add a file connection manager for the text log provider.
                ConnectionManager cm = package.Connections.Add("FILE");

                cm.ConnectionString = Path;
                cm.Name = "FileLogProviderConnection";

                // Add a LogProvider.
                LogProvider provider = package.LogProviders.Add("DTS.LogProviderTextFile.1");

                provider.ConfigString = cm.Name;
                package.LoggingOptions.SelectedLogProviders.Add(provider);
            }
            catch (System.NullReferenceException nre)
            {
                System.Diagnostics.Debug.WriteLine(nre.StackTrace);
            }
        }
        #endregion

        #region AddConnectionManagers
        /// <summary>
        /// Adds the OLEDB and FlatFile connection managers to the package.
        /// </summary>
        private void AddConnectionManagers(string DestinationDataDirectory)
        {
            // Add the OLEDB connection manager.
            ConnectionManager adventureWorks = package.Connections.Add("OLEDB");

            // Set stock properties.
            adventureWorks.Name = "OLEDBConnection";
            adventureWorks.ConnectionString = @"Provider=SQLNCLI;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=AdventureWorks;Data Source=(local);Auto Translate=False;";

            // Add the Destination connection manager.
            ConnectionManager cmflatFile = package.Connections.Add("FLATFILE");

            // Set the stock properties.
            cmflatFile.Properties["ConnectionString"].SetValue(cmflatFile, DestinationDataDirectory + DATAFILENAME);
            cmflatFile.Properties["Format"].SetValue(cmflatFile, "Delimited");
            cmflatFile.Properties["DataRowsToSkip"].SetValue(cmflatFile, 0);
            cmflatFile.Properties["ColumnNamesInFirstDataRow"].SetValue(cmflatFile, false);
            cmflatFile.Properties["Name"].SetValue(cmflatFile, "FlatFileConnection");
            cmflatFile.Properties["RowDelimiter"].SetValue(cmflatFile, "\r\n");
            cmflatFile.Properties["TextQualifier"].SetValue(cmflatFile, "\"");
        }
        #endregion

        #region AddDataFlowTask
        /// <summary>
        /// Adds a DataFlow task to the Executables collection of the package.
        /// Retrieves the MainPipe object from the TaskHost and stores it in
        /// the dataFlow member variable
        /// </summary>
        private void AddDataFlowTask()
        {
            TaskHost th = package.Executables.Add("DTS.Pipeline") as TaskHost;
            th.Name = "DataFlow";
            th.Description = "The DataFlow task in the DTSAuto sample.";

            dataFlow = th.InnerObject as MainPipe;
            dataFlow.Events = pipelineEvents as wrap.IDTSComponentEvents90;
        }
        #endregion

        #region AddOLEDBSource
        /// <summary>
        /// Adds the OLEDB Data Source component to the DataFlow task.
        /// Creates an instance of the component.
        /// Sets the runtime connection manager.
        /// Sets two custom properties; the SqlCommand, and ValidateColumnMetaData.
        /// Acquires the connection and Reinitializes the metadata.
        /// </summary>
        private void AddOLEDBSource()
        {
            oledbSource = dataFlow.ComponentMetaDataCollection.New();

            // Set stock properties.
            oledbSource.ComponentClassID = "DTSAdapter.OLEDBSource";
            oledbSource.Name = "OLEDBSource";
            oledbSource.Description = "Source data in the dataFlow";

            CManagedComponentWrapper instance = oledbSource.Instantiate();
            instance.ProvideComponentProperties();

            // Associate the runtime connection manager
            //  The connection manager association will fail if called before ProvideComponentProperties
            oledbSource.RuntimeConnectionCollection[0].ConnectionManagerID
                = package.Connections["OLEDBConnection"].ID;
            oledbSource.RuntimeConnectionCollection[0].ConnectionManager
                = DtsConvert.ToConnectionManager90(package.Connections["OLEDBConnection"]);

            // set custom component properties
            instance.SetComponentProperty("OpenRowset", "[Production].[Product]");
            instance.SetComponentProperty("AccessMode", 0);

            // Acquire Connections and reinitialize the component
            instance.AcquireConnections(null);
            instance.ReinitializeMetaData();
            instance.ReleaseConnections();
        }
        #endregion

        #region AddSort
        /// <summary>
        /// Adds the DTSTransform.Sort to the DataFlow task.
        /// </summary>
        private void AddSort()
        {
            // Add the component to the DataFlow task.
            sort = dataFlow.ComponentMetaDataCollection.New();

            // Set component's stock properties.
            sort.ComponentClassID = "DTSTransform.Sort";
            sort.Name = "SortTransform";
            sort.Description = "Sort component";

            CManagedComponentWrapper instance = sort.Instantiate();
            instance.ProvideComponentProperties();

            // Attach path between the OLEDB source components Output, and the Sort Component's Input.
            dataFlow.PathCollection.New().AttachPathAndPropagateNotifications(
                oledbSource.OutputCollection[0], sort.InputCollection[0]);

            IDTSVirtualInput90 vInput = sort.InputCollection[0].GetVirtualInput();

            foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
            {
                IDTSInputColumn90 col = instance.SetUsageType(sort.InputCollection[0].ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);

                if (col.Name == package.Variables["SortColumn"].Value.ToString())
                {
                    if (col.CustomPropertyCollection.Count > 0)
                    {
                        instance.SetInputColumnProperty(sort.InputCollection[0].ID, col.ID, "NewSortKeyPosition", 1);
                    }
                }
            }
        }

        #endregion

        #region AddFlatFileDestination
        /// <summary>
        ///
        /// </summary>
        private void AddFlatFileDestination()
        {
            // Add the component to the dataFlow metadata collection
            flatfileDestination = dataFlow.ComponentMetaDataCollection.New();

            // Set the common properties
            flatfileDestination.ComponentClassID = "DTSAdapter.FlatFileDestination";
            flatfileDestination.Name = "FlatFileDestination";
            flatfileDestination.Description = "Flat file destination";

            // Create an instance of the component
            CManagedComponentWrapper inst = flatfileDestination.Instantiate();
            inst.ProvideComponentProperties();

            // Associate the runtime ConnectionManager with the component
            flatfileDestination.RuntimeConnectionCollection[0].ConnectionManagerID
                = package.Connections["FlatFileConnection"].ID;
            flatfileDestination.RuntimeConnectionCollection[0].ConnectionManager
                = DtsConvert.ToConnectionManager90(
                package.Connections["FlatFileConnection"]);

            // Map a path between the Sort transformation component to the FlatFileDestination
            dataFlow.PathCollection.New().AttachPathAndPropagateNotifications(
                sort.OutputCollection[0], flatfileDestination.InputCollection[0]);

            // Add columns to the FlatFileConnectionManager
            AddColumnsToFlatFileConnection();

            // Acquire the connection, reinitialize the metadata,
            // map the columns, then release the connection.
            inst.AcquireConnections(null);
            inst.ReinitializeMetaData();
            MapFlatFileDestinationColumns();
            inst.ReleaseConnections();
        }
        #endregion

        #region MapFlatFileDestination Columns
        private void MapFlatFileDestinationColumns()
        {
            CManagedComponentWrapper wrp = flatfileDestination.Instantiate();

            IDTSVirtualInput90 vInput = flatfileDestination.InputCollection[0].GetVirtualInput();
            foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
                wrp.SetUsageType(flatfileDestination.InputCollection[0].ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);

            // For each column in the input collection
            // find the corresponding external metadata column.
            foreach (IDTSInputColumn90 col in flatfileDestination.InputCollection[0].InputColumnCollection)
            {
                IDTSExternalMetadataColumn90 exCol = flatfileDestination.InputCollection[0].ExternalMetadataColumnCollection[col.Name];
                wrp.MapInputColumn(flatfileDestination.InputCollection[0].ID, col.ID, exCol.ID);
            }
        }
        #endregion

        #region AddColumnsToFlatFileConnection
        private void AddColumnsToFlatFileConnection()
        {
            wrap.IDTSConnectionManagerFlatFile90 ff = null;
            foreach (ConnectionManager cm in package.Connections)
            {
                if (cm.Name == "FlatFileConnection")
                {
                    ff = cm.InnerObject as wrap.IDTSConnectionManagerFlatFile90;
                    DtsConvert.ToConnectionManager90(cm);
                }
            }

            // if the connection manager is null here, then we have a problem
            if (ff != null)
            {
                // Get the upstream columns
                IDTSVirtualInputColumnCollection90 vColumns = flatfileDestination.InputCollection[0].GetVirtualInput().VirtualInputColumnCollection;

                for (int cols = 0; cols < vColumns.Count; cols++)
                {
                    wrap.IDTSConnectionManagerFlatFileColumn90 col = ff.Columns.Add();


                    // If this is the last column, set the delimiter to CRLF.
                    // Otherwise keep the delimiter as ",".
                    if (cols == vColumns.Count - 1)
                    {
                        col.ColumnDelimiter = "\r\n";
                    }
                    else
                    {
                        col.ColumnDelimiter = @",";
                    }

                    col.ColumnType = "Delimited";
                    col.DataType = vColumns[cols].DataType;
                    col.DataPrecision = vColumns[cols].Precision;
                    col.DataScale = vColumns[cols].Scale;
                    wrap.IDTSName90 name = col as wrap.IDTSName90;
                    name.Name = vColumns[cols].Name;
                }
            }
        }
        #endregion
    }
}

谢谢你了,兄弟,我马上调试一下.非常的感谢.

高手,是不是可以给我稍微详细的讲解一下你的代码?
我是新手,看的不是很明白.
在就是我调试的时候出了点问题.有很多类型或命名空间定义不上.麻烦了

请问6楼前辈的代码中,ComponentEvents这个类 要引用的是哪个包啊?
找了好久都没找到...:default2: