SQL Server Performance

MS Access workgroup for 2005 package

Discussion in 'SQL Server 2005 Integration Services' started by cronid, Apr 10, 2008.

  1. cronid New Member

    I am trying to create a SQL Server 2005 package to query an MS Access network database that uses a user defined workgroup and a userid and password to enter the database. I can enter Access as the SQL Server package owner and view the table I am trying to query via the package in an Access session. The package database owner has permission to the network folder and I define the Access userid and password when I create the database connection in the package. I can successfully test that connection and when I invoke the OLE DB Source Editor specifying the connection for table or view, the correct list of the MS Access tables is loaded. But when I try to preview the table that I want to query I get an error code 0x80040E09 with the message, 'Opening a rowset for 'tablename' failed'. Alternatively, when I try to import the Access table data into my SQL Server database via the Import Export Wizard I define the data source as Access, enter the filename, User Name & password but get an error message which says 'the workgroup information file is missing'. I think that's the problem. I believe the Windows' system workgroup default file for any Windows’ user is c:documents & settingsWindowsidapplication dataMicrosoftAccesssystem.mdw. I think I need to point either the package or the import task to the .mdw file that this Access database references, not to the default. But I don't see where to do that in either the Visual Studio package creation or the Import Wizard. Is the workgroup confusion the likely cause of the problem? Is there a way to point to a specific workgroup?
  2. Adriaan New Member

    In SQL 2000 DTS, you need to select the driver for MS Access that has the Access icon next to it. Click on Advanced... and set the Jet OLE:System database property to point to the appropriate MDW file. You can also set user name and password here.
    As you notice, the MDB cannot be accessed referring to the default MDW (which is what happens if you do not select a specific MDW) --- and it probably does not allow access with the user name "admin" either. The MS Access security setup may be limited, but if used properly it is not too bad.
  3. cronid New Member

    My environment is SSIS 2005 not DTS 2000. The advanced button for the Visual Studio connection manager and for the Management Studio Import Wizard does not have a place to enter the revised .mdw reference.
    Do you or anyone else, know how to reference it in the 2005 environment?
  4. satya Moderator

    If you're using a data flow task to pick up the relevant source file and import into a db, set the DelayValidation property of the data flow task to True.
    If the file isn't there the dataflow task wil not be validated since it will not run.
  5. cronid New Member

    The 'file' is there. This is an Access table that I am able to view and modify in an Access session using the package owner id from the package server. But I'm unabel to view or modify it in a SSIS package or via the import wizard.
  6. satya Moderator

    can you see DelayValidation property within that DataFlow task?
  7. cronid New Member

    I got this to work with two steps. I had to explicitly give the Admin user read permission in Access by checking that box in the workgroups display. I was able to view the table data in an Access session without that box checked but was not able to read the table in the SSIS package without it checked. I also needed to copy the resultant .mdw file to the Docs & Settingspackageuserid.....system.mdw default file for my package owner.
    Thanks for the suggestions.
  8. Adriaan New Member

    Remember that permissions on an object in an MDB file are recorded in system tables inside the MDB file, referring to the users and groups identified in the MDW under which the MDB is opened. So these permissions "travel" with the MDB file, not with the MDW file (common misconception).
    So you've actually broken the security setup. Now anyone who gets hold of the MDB file can read the data from Excel, Access, Word, through an ADO connection ...
    The "Admin" user is present in the standard System.MDW that is delivered with each and every Office installation (the exact name will differ between language versions). By giving this user read permission, the application-specific MDW is no longer needed to read the data.
    You can set up a connection string for ADO to refer to a specific MDW, with a user name and password - not sure why you could not do that within SSIS.

Share This Page