Post reply

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

  • March 7, 2018 at 9:18 am

    #410297

    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:

     

    System.Runtime.InteropServices.COMException

      HResult=0xC020801C

      Message=Exception from HRESULT: 0xC020801C

      Source=AS400DynamicLoad

      StackTrace:

       atMicrosoft.SqlServer.Dts.Pipeline.Wrapper.IDTSDesigntimeComponent100.AcquireConnections(ObjectpTransaction)

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

    //Variables
    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
    package.Executables.Add("STOCK:PipelineTask");

    //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 
    instance.ProvideComponentProperties();

    //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. 
    instance.AcquireConnections(null);
    instance.ReinitializeMetaData();
    instance.ReleaseConnections();

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

    Stringdtsxfile = "Local_File_Path";
    Console.WriteLine("Saving Package...");
    app.SaveToXml(dtsxfile,package, null);

           


     


     

  • March 8, 2018 at 8:39 am

    #1982434

    Have you considered using BIML for this task?

  • March 21, 2018 at 3:53 am

    This was removed by the editor as SPAM

  • July 31, 2020 at 6:58 pm

    #3776398

    HI,

    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 3 (of 3 total)

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