title: SSIS Programmatic generation of an ODBC Connection
body: <p>I'm trying to create an ODBC destination source in an SSIS package using methods like the ones described at <a href="http://msdn.microsoft.com/en-us/library/ms345167.aspx" rel="nofollow">http://msdn.microsoft.com/en-us/library/ms345167.aspx</a> I can't seem to initialize the ODBC connection correctly.  Here is the code I'm using to create the ODBC Connection</p>
  
  <pre><code>    // Specify the connection manager.
      component1.RuntimeConnectionCollection.NewAt(0);
      if (component1.RuntimeConnectionCollection.Count &gt; 0)
      {
          component1.RuntimeConnectionCollection[0].ConnectionManager =
            DtsConvert.GetExtendedInterface(myPackage.Connections["ODBC Connection"]);
          component1.RuntimeConnectionCollection[0].ConnectionManagerID =
            myPackage.Connections["ODBC Vertica Connection"].ID;
          component1.RuntimeConnectionCollection[0].Name = myPackage.Connections["ODBC Connection"].Name;
      }
  </code></pre>
  
  <p>When I attempt to connect to a server using this code I get a COM exception thrown.  Does anyone have an example of a working ODBC connection created using SSIS generation</p>
  
  <p>I Create an instance from component1 using the code </p>
  
  <pre><code>CManagedComponentWrapper instance = component1.Instantiate(); 
  </code></pre>
  
  <p>When I attempt to initialize the metadata for the object using the connection the COM exception is thrown. The line throwing the exception reads </p>
  
  <pre><code>instance.AcquireConnections(null); 
  </code></pre>
  
  <p>The COM Exception itself is </p>
  
  <blockquote>
    <p>Error HRESULT E_FAIL has been returned from a call to a COM component</p>
  </blockquote>
  
  <p>Which translates to System.Runtime.InteropServices.COMException error code -2147467259</p>
  
  <p>Here is the code in its entirety.  I attempt to add a an ODBC Destination to an existing Data Flow component</p>
  
  <pre><code>    private IDTSComponentMetaData100 addODBCDest(MainPipe dataFlow_pipe, String tableName)
      {
          // ADO Net Data Source
          IDTSComponentMetaData100 component1 = dataFlow_pipe.ComponentMetaDataCollection.New();
          component1.Name = tableName + " ODBC Destination";
          Application app = new Application();
  
          component1.ComponentClassID = "{074B8736-CD73-40A5-822E-888215AF57DA}";
          // Get the design time instance of the component.
          CManagedComponentWrapper instance = component1.Instantiate();
  
          // Initialize the component
          try
          {
              instance.ProvideComponentProperties();
          }
          catch (Exception e)
          {
              Console.WriteLine(e.ToString());
          }
  
          // Specify the connection manager.
          component1.RuntimeConnectionCollection.NewAt(0);
          if (component1.RuntimeConnectionCollection.Count &gt; 0)
          {
              component1.RuntimeConnectionCollection[0].ConnectionManager =
                DtsConvert.GetExtendedInterface(myPackage.Connections["ODBC Connection"]);
              component1.RuntimeConnectionCollection[0].ConnectionManagerID =
                myPackage.Connections["ODBC Vertica Connection"].ID;
              component1.RuntimeConnectionCollection[0].Name = myPackage.Connections["ODBC Connection"].Name;
          }
  
          // Set the custom properties.
          instance.SetComponentProperty("InsertMethod", 1);
          instance.SetComponentProperty("BatchSize", 10000);
  
          // Reinitialize the metadata.
          instance.AcquireConnections(null);  // crashes with COM exception -2147467259
          instance.ReinitializeMetaData();
          instance.ReleaseConnections();
          return component1;
      }
  </code></pre>