none
Calling package from C# fails with bad OleDbConnection

    Question

  • I have a VS2012 solution with a number of projects, one of which is based on SSIS.

    The package reads in a CSV flat file, filters it, clears a SQL Server 2008 table and then loads the CSV file into that table. The environment is Windows 7 x64. The SQL Server instance is not on the local machine.

    When I execute the package directly in Visual Studio, it completes successfully. However, when I call it programmatically, it fails. I do not know what to try next. My hunch is that there's something weird with the password. 

    I would appreciate any suggestions - Thanks

    Code calling package:      

          private static void importToDatabase(string fullPath)
          {
             MyEventListener listener = new MyEventListener();
             Application app = new Application();
             Package package = app.LoadPackage("package.dtsx", listener);
             DTSExecResult pkgResults = package.Execute(null, null, listener, null, null);
             Console.WriteLine("Package results: {0}", pkgResults);
          }
       }
    
       class MyEventListener : DefaultEvents
       {
          public override bool OnError(DtsObject source, int errorCode, string subComponent,
            string description, string helpFile, int helpContext, string idofInterfaceWithError)
          {
             Console.WriteLine("Error in {0}/{1} : {2}", source, subComponent, description);
             return false;
          }
       } 


    Error message from MyEventListener:
    Error in Microsoft.SqlServer.Dts.Runtime.TaskHost/SSIS.Pipeline : Cannot find the connection manager with ID "{C25724AB-D25F-48EB-B821-0D24E2E9FB98}" in the connection manager collection due to error code 0xC0010009. That connection manager is needed by "OLE DB Destination.Connections[OleDbConnection]" in the connection manager collection of "OLE DB Destination". Verify that a connection manager in the connection manager collection, Connections, has been created with that ID.


    From package.dtsx:
                  <connections>
                    <connection
                      refId="Package\Import Data\OLE DB Destination.Connections[OleDbConnection]"
                      connectionManagerID="{C25724AB-D25F-48EB-B821-0D24E2E9FB98}:external"
                      connectionManagerRefId="Project.ConnectionManagers[ZEBAY.Mail.pow]"
                      description="The OLE DB runtime connection used to access the database."
                      name="OleDbConnection" />
                  </connections>

    From ZEBAY.Mail.pow.conmgr:    
    <?xml version="1.0"?>
    <DTS:ConnectionManager xmlns:DTS="www.microsoft.com/SqlServer/Dts"
      DTS:ObjectName="ZEBAY.Mail.pow"
      DTS:DTSID="{C25724AB-D25F-48EB-B821-0D24E2E9FB98}"
      DTS:CreationName="OLEDB">
      <DTS:ObjectData>
        <DTS:ConnectionManager
          DTS:ConnectionString="Data Source=ZEBAY;User ID=pow;Initial Catalog=Mail;Provider=SQLNCLI11.1;Persist Security Info=True;Auto Translate=False;">
          <DTS:Password
            DTS:Name="Password"
            Sensitive="1">AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAAhI0KfBwx2EK6eU0KGMzzJwAAAAACAAAAAAAQZgAAAAEAACAAAABmoDqlG38/fl0EmUKR4SibW+9bIlEG4ZkrpIUBJG8yNgAAAAAOgAAAAAIAACAAAABGugaTuSIDMg2k4l4v8bu6OnhCjvsiVf+BJp80OvHN5IAAAADzq/Ht1y50sNtTeqYG/sj3YHGR+0q5Q/bTusEE5yzGjurzBNFuE230QspL+8hXlrqc+9+Wcqp1x/PFeMuinT5YjCD15iOw62gF2/xh8FKwK5v1IU09jNpvVjzphE6gxlze+pxkLf0n9xZHda7rjee5gcrnaEpEX1Za7WzyaNrfXEAAAAAXtvrXxoXugYmFEqRrDe1VCpsxyUT5vwBCHXp9oGUIyhn9amfyhdoJ6NG4ZjUsbp88/+qRTFNii2WmiyO2/MXC</DTS:Password>
        </DTS:ConnectionManager>
      </DTS:ObjectData>
    </DTS:ConnectionManager>

                  
    Thursday, July 04, 2013 9:28 PM

Answers

All replies

  • Looks like you build your package programmatically in code and thus probably missed a step to create the package so it does not validate itself. In other words you need to set its DelayValidationProperty to true in code.

    PS: This is typically necessary say when you load a differently structured file each time and then obviously you need the package created dynamically. Looks like this being the case here, too.


    Arthur My Blog

    Friday, July 05, 2013 1:26 AM
  • The package is not created dynamically. I defined the package in a VS2012 project and can execute the package from within the IDE just fine.

    The destination connection (OleDbconnection) causing the problem does not have a DelayValidation property. The flat file does and is set to True.

    Friday, July 05, 2013 2:48 PM
  • Is it a package or project connection manager?

    What is the protection level of your package?

    Are you using integrated security or do you have a database user in the connection string?


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Friday, July 05, 2013 4:08 PM
  • The source connection for the flat file is a package manager. The OleDbConnection destination connection is a project manager.

    The protection level is EncryptSensitiveWithUserKey.

    I am not using integrated security. The 'User ID'  is specified in the connection string.

    I made sure DelayValidation property for both connection managers are set to True via their Property Pages.

    Friday, July 05, 2013 5:16 PM
  • For project connection managers you should use other code

    http://microsoft-ssis.blogspot.com/2013/01/call-ssis-2012-package-within-net.html


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Friday, July 05, 2013 5:41 PM
  • Thanks for that link. However, I don't understand how to find the first three literals in the following statement (SSISDB, MasterChild and MasterChildPackages:

    PackageInfo ssisPackage = ssisServer.Catalogs["SSISDB"].Folders["MasterChild"].Projects["MasterChildPackages"].Packages["master.dtsx"];
    

    Friday, July 05, 2013 5:57 PM
  • Thanks for that link. However, I don't understand how to find the first three literals in the following statement (SSISDB, MasterChild and MasterChildPackages:

    PackageInfo ssisPackage = ssisServer.Catalogs["SSISDB"].Folders["MasterChild"].Projects["MasterChildPackages"].Packages["master.dtsx"];

    I think project Connection Managers only work if you deploy the packages to the SSIS Catalog.

    That line of code executes this package from the catalog:


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Friday, July 05, 2013 6:14 PM
  • Alternative is to switch to package connection managers...

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    • Marked as answer by larlap Friday, July 05, 2013 10:11 PM
    Friday, July 05, 2013 6:56 PM
  • I deleted the OleDbConnection project manager and made a a package manager instead. Now the package execute method runs without error.

    I did have to add the following to the app.config:

      <startup useLegacyV2RuntimeActivationPolicy="true">
              <supportedRuntime version="v4.0"/>
      </startup>

    Thank you so much for your help. I would not have been able to fix this without your aid.

    Friday, July 05, 2013 10:12 PM