SSIS Lookup Transformation error: The EXECUTE permission was denied on the object 'sp_describe_undeclared_parameters'

3

I'm upgrading SSIS packages from SQL Server 2008 R2 to 2017. After upgrading one of the packages, the parameterized query in the Advanced tab of a Lookup Transformation is now throwing a validation error. It is possible that this is the only package in the collection that uses a Partial Cache, since I couldn't find any successfully upgraded examples to compare it to.

When I tried to run the package, it logged these errors:

Error: 2019-02-11 10:49:40.11 Code: 0xC0202009 Source: Anonymized DFT [209] Description: 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 EXECUTE permission was denied on the object 'sp_describe_undeclared_parameters', database 'mssqlsystemresource', schema 'sys'.". End Error

Error: 2019-02-11 10:49:40.15 Code: 0xC020824C

Source: Anonymized DFT [209] Description: OLE DB error occurred while loading column metadata. Check SQLCommand and SqlCommandParam properties. End Error

Error: 2019-02-11 10:49:40.18 Code: 0xC004706B

Source: Anonymized DFT SSIS.Pipeline Description: "Anonymized DFT" failed validation and returned validation status "VS_ISBROKEN". End Error

Error: 2019-02-11 10:49:40.22 Code: 0xC004700C

Source: Anonymized DFT SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2019-02-11 10:49:40.27 Code: 0xC0024107

Source: Anonymized DFT
Description: There were errors during task validation. End Error

I was hoping to just re-map the parameters, but I get about the same set of errors by trying to open the Parameters dialog on the Advanced tab of the Lookup Transformation.

The obvious answer, based on the messaging, is that it's a permissions issue, but according to the documentation for sp_describe_undeclared_parameters:

Permissions

Requires permission to execute the @tsql argument.

Both I and the service account that the job runs under have permission to execute the parameterized query.

I did a side-by-side comparison of the both the Lookup Transformations and their corresponding Properties windows between the 2008 R2 package, which runs fine on the 2008 R2 server, and the new 2017 package. The only new property value I found was TreatDuplicateKeysAsError, but changing that has no effect on my situation.

Looking at the Advanced Editor, the column references in the ParameterMap appear to be correct, but the query isn't picking them up.

Is there more to the permissions issue than I'm seeing, or is there something else that I'm missing here?

sql-server
parameters
ssis
etl
lookup
asked on Stack Overflow Feb 12, 2019 by Eric Brandt • edited Feb 13, 2019 by Yahfoufi

1 Answer

2

I can't point to a specific source that suggested this as a possibility, but in the end I added an additional Connection Manager pointing to the same server and database as the original, but instead of using the SQL Server Native Client 11.0 provider, I changed it to Microsoft OLE DB Provider for SQL Server.

Boom. Validation error cleared; package is running.

While that's a solution, it's not a explanation, so if anyone wants to chime in, have at it. I've wasted most of a day chasing this ghost, so for the time being I'm going to take my running package and call it good enough for a Tuesday.

answered on Stack Overflow Feb 12, 2019 by Eric Brandt

User contributions licensed under CC BY-SA 3.0