SQL Server Performance

excel connection error

Discussion in 'SQL Server 2005 Integration Services' started by arkiboys, Nov 5, 2007.

  1. arkiboys Member

    I am looping through excel files to import data into a sql server table.
    Added a filename variable.
    This variable gets set inside a foreach loop container.
    In the Dataflow tab, there is a;
    excel source
    oledb source
    In the design mode, there is an error icon (red circle with cross in it).
    Can not acquire connection from connection manager.
    In the connection manager, the excel connection file path is empty. It is empty because I think the variable should be assigned to it.
    So when I ruun the ssis package I get this error:
    The AcquireConnection method call to the connection manager "Excel Connection" failed with error code 0x.....
    Any thoughts please?
  2. satya Moderator

  3. arkiboys Member

    Thanks for the link.
    Read it but there is nothing in there which is specific to my error.
    Please note that it seems the filepath for the excel files does not get set.
  4. satya Moderator

    That talks about the privileges for the user on the package, have you checked it all
  5. arkiboys Member

    And that does not seem to be the problem because when I hard code the path to one of the excel files, the package ruuns fine.
    It seems the loop is interfering somehow...
  6. satya Moderator

    Post the complete error, as the one above doesn't give muc information.
  7. arkiboys Member

    Hi, I think I am getting close to the solution. In the connectionstring for the excel connection manager, I am using: "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::FileName] + ";Extended Properties=Excel 8.0;HDR=YES;"
    The exact error is: Error at dataflow Task [Excel Source 1]]:ssis error code DTS_E_CANNOTACQUIRECONNECCTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel connection Manager" falied with error code 0x...
  8. satya Moderator

    Is this a SQL x64 bit installation?
    If so then the Excel connection manager does not work on 64-bit, so if this is 64-bit machine, you need to use 32-bit DTEXEC and run it in WOW64 (more info here: http://ssis.wik.is/64-bit_Story).

  9. arkiboys Member

    No this is 32bit
    Please note that I think I am getting closer to the solution...
    IN control flow tab, I set the dela validation property to true
    Cannot detach from one or more processes.
    The object invoked has disconnected from its clients
    Do you want to terminate them instead?

Share This Page