Querying against Access db in SSIS

  • sql server developer

    SSCarpal Tunnel

    Points: 4746

    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

  • John H Marsh

    SSChampion

    Points: 11671

    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

  • sql server developer

    SSCarpal Tunnel

    Points: 4746

    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.

  • Phil Parkin

    SSC Guru

    Points: 243779

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

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

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • sql server developer

    SSCarpal Tunnel

    Points: 4746

    Hi,

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

  • sql server developer

    SSCarpal Tunnel

    Points: 4746

    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.

  • Phil Parkin

    SSC Guru

    Points: 243779

    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 🙂

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • John H Marsh

    SSChampion

    Points: 11671

    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

  • mlinden80

    SSC Veteran

    Points: 240

    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 9 (of 9 total)

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