none
OlE DB Source - Cannot acquire connection from connection manager

    Question

  • My source files are in C:\Process folder. File names are in the format proc*.csv

    I am using foreach loop container to iterate over the files in folder "C:\Process"

    I have a user variable "filename"

     

    The order of my tasks

    ForEach - Data Flow task - File System Task

     

    In Data Flow task, I have ole db source  which should take the dynamic filename from user variable "filename". whats happening is when i give fixed filename, without foreach loop container the package is working fine.

    with dynamic filename, I am not able to run the package because of ole db source Error - cannot aquire connection from connection manager.

     

    Thanks for u'r help.

     

    Friday, July 13, 2007 4:22 PM

All replies

  • Where are you assigning the User::Filename variable to the OLE DB connection?
    Friday, July 13, 2007 4:25 PM
    Moderator
  • In ole db connection -properties- expressions [ Property("Connection String") - Expression(@[User::filename]) ]
    Friday, July 13, 2007 6:14 PM
  • Wait a minute.  After having lunch, I can now think with a clear head.

    You're trying to access a file?  You shouldn't be using the OLE DB source object.  Instead you should be using the flat file source object.
    Friday, July 13, 2007 6:26 PM
    Moderator
  • sorry, they are *.mdb files
    Friday, July 13, 2007 6:30 PM
  • You mean they are Microsoft Access files?

    Note that in the expression for ConnectionString, you probably want:

    "Data Source=" + @[User::filename] + ";Provider=Microsoft.Jet.OLEDB.4.0;"
    Friday, July 13, 2007 6:35 PM
    Moderator
  • Thanks for u'r time. I tried the connection string you provided but still its giving me the same error " cannot acquire connection".

     

    Is there any efficient way to copy access database table contents to another access database table(Stage)? Then I can leave the source to a fixed name.

    Friday, July 13, 2007 7:40 PM
  •  NR01 wrote:

    Thanks for u'r time. I tried the connection string you provided but still its giving me the same error " cannot acquire connection".

    Is there any efficient way to copy access database table contents to another access database table(Stage)? Then I can leave the source to a fixed name.



    Are you sure User::filename is populated with the full path to the mdb file?  (Have you confirmed it?)  Also, what version of Access created the files?  Also, are they on a local drive, or network drive?

    You could also try this connection string in your expression:
    "Data Source=" + @[User::filename] + ";Provider=Microsoft.Jet.OLEDB.3.51;"
    Friday, July 13, 2007 7:47 PM
    Moderator
  • Still i am having the same problem. I have dynamic sources that need to map to a table in sql server 2005. I know the table format of access.
    Friday, July 13, 2007 7:55 PM
  •  NR01 wrote:
    Still i am having the same problem. I have dynamic sources that need to map to a table in sql server 2005. I know the table format of access.


    Just try setting up a test data flow.  Setup the OLE DB connection to an existing Access database file.  Set it up to pull from one of the tables you are trying to work with.  Hook it up to a row count transformation and run the data flow.  Does it work?

    Also know that SSIS can't handle changing metadata.  Even though you can change the source database, you won't be able to dynamically move data from changing table structures.  That is, once a data flow is setup, its metadata cannot change.
    Friday, July 13, 2007 8:09 PM
    Moderator