使用SSIS DataFlowTask从平面文件源编程派生列

Question

使用OLEDB数据源的代码将填充下面的OutputColumnCollection，而使用flatfile数据源的代码将不会填充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 包含数据源的列。下面是针对 Flatfile 数据源修改后的代码，但不起作用。

        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]);

---

- Steve Schlichter

Answer 1

我使用SSIS处理输入的平面文件和SQL输出时遇到了问题，因为数据类型不匹配。首先检查一下它是否像这样简单。

另外，您在帖子中没有包含很多有关出现什么问题的信息，所以很难告诉我它是一个数据库问题还是你得到了什么样的错误？您是否尝试过使用SSIS图形界面而不是通过编程来实现这一点？

- Andrew Jahn

Answer 2

你可能已经解决了这个问题... :) 如果是这样，请告诉我们。

在平面文件版本中，目标连接分配后面的以下行缺失。这可能帮助不大（因为你的输入列也缺失了），但让我们修复它并查看我们得到了什么错误...

InstanceDestination.SetComponentProperty("OpenRowset", 
DestinationTableNameInternal);        

InstanceDestination.SetComponentProperty("AccessMode", 0);

- Nasser

Answer 3

这段代码是错误的：

conMgrSource.Properties["RowDelimiter"].SetValue(conMgrSource, "{CR}{LF}");
conMgrSource.Properties["HeaderRowDelimiter"].SetValue(conMgrSource, "{CR}{LF}");

这段代码运行良好：

conMgrSource.Properties["HeaderRowDelimiter"].SetValue(conMgrSource, "\r\n");

- Rainer Schütze