Programmatically Creating SSIS Package in C# Exception from HRESULT: 0xC020801C System.Runtime.InteropServices.COMException

  • arc9917

    SSC Enthusiast

    Points: 182

    I amworking on a C# Console Application in Visual Studio 2017 to dynamically createpackages to copy data from thousands of AS400 (DB2) tables to SQL Server 2016.Right now I’m just trying to get it to build one package and running into aproblem getting the source OLEDB connection to pull the metadata. Theconnection string and query works greatif I manually create the package in SSDT. I can also comment out thehighlighted code below and import the created package, then using SSDT I can open the source and it pullsin the metadata. Lastly, if I switch outthe connection string and query for SQL server infomationit works just fine.



    Error Thrown:




      Message=Exception from HRESULT: 0xC020801C




       at AS400DynamicLoad.Program.Main(String[]args) in H:\VS 2017 Projects\AS400DynamicLoad\AS400DynamicLoad\Program.cs:line123

    String AS400_connStr = "Data Source=SYSTEMNAME;User ID=user;Password=PASSWORD;Initial Catalog=CATALOGNAME;Provider=IBMDA400;";

    Console.WriteLine("Building Package...");

    //Create a new SSIS Package

    Package package = newPackage();

    package.Name= "Test";

    //Add a Connection Manager to the Package, of type, OLE DB AS400

    ConnectionManager connMgrAS400 = package.Connections.Add("OLEDB");

    connMgrAS400.Name= "AS400 ConnMgr " + AS400;
    connMgrAS400.Description= "AS400 ConnMgr " + AS400;
    connMgrAS400.ConnectionString= AS400_connStr;

    //Add a Data Flow Task to the Package

    //Get the task host wrapper, and the Data Flow task
    TaskHost taskHost = package.Executables[0] as TaskHost;
    MainPipe dataFlowTask = (MainPipe)taskHost.InnerObject;
    taskHost.Name= "DataFlowTask";

    Application app = newApplication();

    //Add an OLE DB source to the data flow. 

    IDTSComponentMetaData100 componentSource = dataFlowTask.ComponentMetaDataCollection.New();
    componentSource.ComponentClassID= "DTSAdapter.OleDbSource.5";

    //Get the design time instance of the component.
    CManagedComponentWrapper instance = componentSource.Instantiate();

    //Initialize the component 

    //Set source connection
    componentSource.RuntimeConnectionCollection[0].ConnectionManagerID = connMgrAS400.ID;
    componentSource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(connMgrAS400);

    //Set the custom properties. 
    instance.SetComponentProperty("AccessMode", 2);
    instance.SetComponentProperty("SqlCommand","Select * From lib.table");

    //Reinitialize the metadata. 

    //SetConnection Name
    componentSource.Name= "OLEDBSource AS400";

    Stringdtsxfile = "Local_File_Path";
    Console.WriteLine("Saving Package...");

    app.SaveToXml(dtsxfile,package, null);




  • leehbi


    Points: 2874

    Have you considered using BIML for this task?

  • This was removed by the editor as SPAM

  • latham


    Points: 1


    Did you get any solution to this error? I am having a similar issue, but not getting much help

    Thank you!



Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply