Querying against Access db in SSIS

  • Hi All,

    I'm trying to read the data in Access database using Execute SQL Task. I'm trying to get the user created tables in MS Access and transfer the data in each table into an temp table in sql server for comparison purpose. In that for getting the list of all user tables in MS access i've done the following.

    1) I've created a connection manager using Microsoft Jet 4.0 provider and configured it. I took an Execute Sql task and selected the created connection manager. In that i've given the query as SELECT name FROM Msysobjects WHERE type=1 AND name NOT LIKE 'Msys*'

    When i execute the above task i'm receiving the following error. Can some one help me out what that permission issue is.

    Error:

    SSIS package "Package2.dtsx" starting.

    Error: 0xC002F210 at Getting the tables in MS Access, Execute SQL Task: Executing the query "SELECT name FROM Msysobjects WHERE type=1 AND name NOT LIKE 'Msys*'" failed with the following error: "Record(s) cannot be read; no read permission on 'Msysobjects'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    Task failed: Getting the tables in MS Access

    Warning: 0x80019002 at Package2: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

    SSIS package "Package2.dtsx" finished: Failure.

    Thanks in advance

  • Hello,

    Does this thread help?

    http://www.dotnetspider.com/forum/202799-Records-s-cannot-be-read-no-read-permission-msysobjects.aspx

    Regards,

    John Marsh

    www.sql.lu
    SQL Server Luxembourg User Group

  • Hi Thanks for your reply.

    I tried the way you said :

    Set View-- >Options/ General/Show System objects to "yes".

    When I set that I could only see those system objects in MS Access db but getting the same error in SSIS.

  • Are you able to query user tables in the Access db?

    Just wondering whether any additional permissions are required to query Access' system objects.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Hi,

    I could not access the MS Access system tables from SSIS.

  • Yes I could access the MS Access user tables from SSIS using Microsoft Jet OLE DB provider but could not access system tables like Msysobjects.

  • You could try making a view (in Access) of MSysObjects and attempt to access that?

    --edit: (cough) Oops! I think they're just called 'queries' in Access 🙂

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Hello again,

    Now that the msysobjects table is visible in the Access GUI, you should be able to grant read permission to the table for the user-id that you are supplying in your Connection (from SSIS).

    Regards,

    John Marsh

    www.sql.lu
    SQL Server Luxembourg User Group

  • Sorry to ressurect a 10 year old thread...

    However, I experience the same issue, when i run my 'Execute SQL Task' querying my AccessDB connection

    "Record(s) cannot be read; no read permission on 'Msysobjects'."

    I can set the permissions if i go into the access table, but is it possible to set the permissions in a SSIS script task using c# or VB?

    I cant seem to find any script template via multiple google searches

    Thanks in advance

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply