c# - 使用 SSIS 数据流任务从平面文件源以编程方式派生列

Question

此代码使用 OLEDB 源将填充 OutputColumnCollection在它下面，使用平面文件源的代码将不会填充 OutputColumnCollection。

为什么不呢？

        Microsoft.SqlServer.Dts.Runtime.Application a = new Microsoft.SqlServer.Dts.Runtime.Application();
        String SSISPackageFilePath;
        SSISPackageFilePath = "Package Name"; 

        Package pkg = new Package();
        MainPipe dataFlow;

        //oledb source
        ConnectionManager conMgrSource = pkg.Connections.Add("OLEDB");
        conMgrSource.ConnectionString = "Data Source=Steve;Initial Catalog=Scrambler;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;";

        // set the standardized name on source
        conMgrSource.Name = "ConnectionSource";

        ConnectionManager conMgrDestination = pkg.Connections.Add("OLEDB");
        conMgrDestination.Name = "OLEDBConnectionDestination";
        conMgrDestination.ConnectionString = "Data Source=server;Initial Catalog=Scrambler;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;";

        Executable exe = pkg.Executables.Add("DTS.Pipeline.1");

        TaskHost th = exe as TaskHost;
        th.Name = "DynamicDataFlowTask";
        dataFlow = th.InnerObject as MainPipe;

        IDTSComponentMetaDataCollection90 metadataCollection = dataFlow.ComponentMetaDataCollection;
        IDTSComponentMetaData90 Source = dataFlow.ComponentMetaDataCollection.New();
        Source.Name = "Source";
        //sql server
        //-------
        Source.ComponentClassID = "DTSAdapter.OLEDBSource.1";
        //-------

        IDTSComponentMetaData90 OLEDBDestination = dataFlow.ComponentMetaDataCollection.New();
        OLEDBDestination.Name = "OLEDBDestination";
        OLEDBDestination.ComponentClassID = "DTSAdapter.OLEDBDestination.1";

        // Get the design time instance of the component.
        CManagedComponentWrapper InstanceSource = Source.Instantiate();
        // Initialize the component
        InstanceSource.ProvideComponentProperties();
        // Specify the connection manager.
        if (Source.RuntimeConnectionCollection.Count > 0)
        {
            Source.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(pkg.Connections["ConnectionSource"]);
            Source.RuntimeConnectionCollection[0].ConnectionManagerID = pkg.Connections["ConnectionSource"].ID;
        }

        //sql server
        InstanceSource.SetComponentProperty("OpenRowset", SourceTableNameInternal);
        InstanceSource.SetComponentProperty("AccessMode", 0);

        //reinitialize the component 
        InstanceSource.AcquireConnections(null);
        InstanceSource.ReinitializeMetaData();
        InstanceSource.ReleaseConnections();

        // Get the design time instance of the component.
        CManagedComponentWrapper InstanceDestination = OLEDBDestination.Instantiate();
        // Initialize the component
        InstanceDestination.ProvideComponentProperties();
        // Specify the connection manager.
        if (OLEDBDestination.RuntimeConnectionCollection.Count > 0)
        {
            OLEDBDestination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(pkg.Connections["OLEDBConnectionDestination"]);
            OLEDBDestination.RuntimeConnectionCollection[0].ConnectionManagerID = pkg.Connections["OLEDBConnectionDestination"].ID;
        }

        InstanceDestination.SetComponentProperty("OpenRowset", DestinationTableNameInternal);
        InstanceDestination.SetComponentProperty("AccessMode", 0);

        //reinitialize the component 
        InstanceDestination.AcquireConnections(null);
        InstanceDestination.ReinitializeMetaData();
        InstanceDestination.ReleaseConnections();

        //map the columns
        IDTSPath90 path = dataFlow.PathCollection.New();
        path.AttachPathAndPropagateNotifications(**Source.OutputCollection[0]**, OLEDBDestination.InputCollection[0]);

---

//Source.OutPutCollection[0].OutputColumnCollection 包含数据源的列。下面，为平面文件源更改的相同代码不起作用。

---

        Microsoft.SqlServer.Dts.Runtime.Application a = new Microsoft.SqlServer.Dts.Runtime.Application();
        String SSISPackageFilePath;
        SSISPackageFilePath = PackageNameInternal; 

        if (File.Exists(SSISPackageFilePath))
            File.Delete(SSISPackageFilePath);

        Package pkg = new Package();
        MainPipe dataFlow;

        // csv source
        ConnectionManager conMgrSource = pkg.Connections.Add("FLATFILE");
        conMgrSource.ConnectionString = @"c:\temp\test.txt";
        conMgrSource.Properties["ColumnNamesInFirstDataRow"].SetValue(conMgrSource, true);
        conMgrSource.Properties["FileUsageType"].SetValue(conMgrSource, Microsoft.SqlServer.Dts.Runtime.Wrapper.DTSFileConnectionUsageType.DTSFCU_FILEEXISTS);
        conMgrSource.Properties["Format"].SetValue(conMgrSource, "Delimited");
        conMgrSource.Properties["RowDelimiter"].SetValue(conMgrSource, "{CR}{LF}");
        conMgrSource.Properties["HeaderRowDelimiter"].SetValue(conMgrSource, "{CR}{LF}");

        // set the standardized name on source
        conMgrSource.Name = "ConnectionSource";

        ConnectionManager conMgrDestination = pkg.Connections.Add("OLEDB");
        conMgrDestination.Name = "OLEDBConnectionDestination";
        conMgrDestination.ConnectionString = "Data Source=server;Initial Catalog=Scrambler;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;";

        Executable exe = pkg.Executables.Add("DTS.Pipeline.1");

        TaskHost th = exe as TaskHost;
        th.Name = "DynamicDataFlowTask";
        dataFlow = th.InnerObject as MainPipe;

        IDTSComponentMetaDataCollection90 metadataCollection = dataFlow.ComponentMetaDataCollection;
        IDTSComponentMetaData90 Source = dataFlow.ComponentMetaDataCollection.New();
        Source.Name = "Source";

        //csv
        //-------
        Source.ComponentClassID = "DTSAdapter.FlatFileSource.1";
        // Get native flat file connection 
        // customize delimiters through the columns collection
        //RuntimeWrapper.IDTSConnectionManagerFlatFile90 connectionFlatFile = conMgrSource.InnerObject as RuntimeWrapper.IDTSConnectionManagerFlatFile90;
        //foreach (RuntimeWrapper.IDTSConnectionManagerFlatFileColumns90 col in connectionFlatFile.Columns)
        //{
        //                    
        //}
        //-------

        IDTSComponentMetaData90 OLEDBDestination = dataFlow.ComponentMetaDataCollection.New();
        OLEDBDestination.Name = "OLEDBDestination";
        OLEDBDestination.ComponentClassID = "DTSAdapter.OLEDBDestination.1";

        // Get the design time instance of the component.
        CManagedComponentWrapper InstanceSource = Source.Instantiate();
        // Initialize the component
        InstanceSource.ProvideComponentProperties();
        // Specify the connection manager.
        if (Source.RuntimeConnectionCollection.Count > 0)
        {
            Source.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(pkg.Connections["ConnectionSource"]);
            Source.RuntimeConnectionCollection[0].ConnectionManagerID = pkg.Connections["ConnectionSource"].ID;
        }

        //reinitialize the component 
        InstanceSource.AcquireConnections(null);
        InstanceSource.ReinitializeMetaData();
        InstanceSource.ReleaseConnections();

        // Get the design time instance of the component.
        CManagedComponentWrapper InstanceDestination = OLEDBDestination.Instantiate();
        // Initialize the component
        InstanceDestination.ProvideComponentProperties();
        // Specify the connection manager.
        if (OLEDBDestination.RuntimeConnectionCollection.Count > 0)
        {
            OLEDBDestination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(pkg.Connections["OLEDBConnectionDestination"]);
            OLEDBDestination.RuntimeConnectionCollection[0].ConnectionManagerID = pkg.Connections["OLEDBConnectionDestination"].ID;
        }

        //reinitialize the component 
        InstanceDestination.AcquireConnections(null);
        InstanceDestination.ReinitializeMetaData();
        InstanceDestination.ReleaseConnections();

        //map the columns
        IDTSPath90 path = dataFlow.PathCollection.New();
        path.AttachPathAndPropagateNotifications(**Source.OutputCollection[0]**, OLEDBDestination.InputCollection[0]);

---

Answer 1

我在将 SSIS 用于输入和 sql 输出的平面文件时遇到问题，因为数据类型不匹配。首先检查以确保它不是像这样简单的东西。

此外，您在帖子中没有提供太多有关问题所在的信息，因此我很难判断这是数据库问题还是您遇到了什么样的错误？您是否尝试过使用 SSIS gui 而不是以编程方式执行此操作？