Programmatically derived Columns from flatfile source using SSIS DataFlowTask

Question

this code using an OLEDB source will populate the OutputColumnCollection below it, the code using a flatfile source will not populate the OutputColumnCollection.

Why not?

        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 contains the columns of the data source. Below, the same code altered for a Flatfile source does not work.

---

        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

I have had issues using SSIS with flat files for input and sql output because the data types didn't match. Check to make sure its not something simple like this first.

Also you didn't include very much information in your post about what is going wrong so its hard for me to tell if its a DB problem or what kind of errors your getting? Have you tried using the SSIS gui rather then programmatically doing this?

Answer 2

Could any one got the solution for the same kind of issue, I have a flatfile with columns can vary each time and also the order of the columns, I need to map the columns which is there in SQL table and load the data to SQl table. Please let me know if anyone come across this problem. my contact Info: [email protected].

Answer 3

Hi

You might have fixed this by now... :) If so let us know.

In the Flat File version the following lines after the destination connection assignement are missing. This may not help much (because your input columns are missing) but lets fix it and see what error are we getting....

InstanceDestination.SetComponentProperty("OpenRowset", DestinationTableNameInternal);

InstanceDestination.SetComponentProperty("AccessMode", 0);