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

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

Answer 1

你现在可能已经修好了..。如果是的话，请告诉我们。

在平面文件版本中，目标连接分配后的下列行丢失。这可能没有多大帮助(因为您的输入列丢失了)，但是让我们修复它，看看我们得到了什么错误.

InstanceDestination.SetComponentProperty("OpenRowset", 
DestinationTableNameInternal);        

InstanceDestination.SetComponentProperty("AccessMode", 0);

Answer 2

由于数据类型不匹配，我在使用带有平面文件的SSIS输入和sql输出时遇到了问题。先检查一下，确保不是像这样简单的事情。

另外，在你的帖子中没有包含太多关于哪里出了问题的信息，所以我很难判断它是DB问题还是什么类型的错误？您是否尝试过使用SSIS而不是以编程方式进行此操作？

Answer 3

这个代码是错误的：

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

此代码运行良好：

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