Metadata error in SSIS when using Openquery and ADSI when Project deployed to SSIS Server

  • Hello all,
    I have an SSIS process that uses an ADSI connection to AD to pull down active directory information, and though it's working fine when ran manually I get this error when I deploy the project to the SSIS Server (SQL 2014).  All fields returned from ADSI are accounted for, so I'm not sure why it's giving a Metadata issue.  The Source is a View and the Destination is a table all on the same database.  I even tried putting it into a Stored Procedure with the Result Set defined but this didn't work either.

    Thanks for any suggestions.
    Sam


    Get ADSI User Information:Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred.
    Error code: 0x80004005.
    An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "The metadata could not be determined because every code path results in an error; see previous errors for some of  these.".
    An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "An error occurred while preparing the query "
    select userAccountControl,
    accountExpires,
    Department,
    sAMAccountName,
    name,
    Manager,
    telephoneNumber,
    physicalDeliveryOfficeName,
    Title,
    Mail,
    displayName,
    givenName,
    initials,
    sn,
    streetAddress,
    l,
    st,
    ADsPath,
    postalCode,
    objectGUID,
    whenChanged
    FROM 'LDAP://ou=Users,dc=********,dc=com'
    WHERE objectClass='user'" for execution against OLE
    DB provider "ADSDSOObject" for linked server "ADSI". ".

  • If the error happens only when you run the package from SQL Agent, it's probably a permissions issue. Does the SQL Agent user have rights to query AD?


  • Phil Parkin - Monday, May 21, 2018 10:20 AM

    If the error happens only when you run the package from SQL Agent, it's probably a permissions issue. Does the SQL Agent user have rights to query AD?

    Yes, I've verified the user the SSIS process is running has the appropriate permissions.  Also I have another step pulling AD Group information over the same connection in the same package which is working without issue.  I had to use Result Set for it, but this didn't work for the one pulling AD User information.

    Sam

  • samalex - Monday, May 21, 2018 1:04 PM

    Phil Parkin - Monday, May 21, 2018 10:20 AM

    If the error happens only when you run the package from SQL Agent, it's probably a permissions issue. Does the SQL Agent user have rights to query AD?

    Yes, I've verified the user the SSIS process is running has the appropriate permissions.  Also I have another step pulling AD Group information over the same connection in the same package which is working without issue.  I had to use Result Set for it, but this didn't work for the one pulling AD User information.

    Sam

    OK, please post the exact text of the error message.


Viewing 4 posts - 1 through 4 (of 4 total)

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