(@billinkc here). There's no exception raised from the library but the actual package won't be configured to use the variable as expected which in turn leads to "Validation error. PackageName OLE DB Source : Accessing variable "" failed with error code
0xC0010001. EzApiPackage.dtsx "
I tried reordering them as well as using/not using the variable's namespace but with no luck.
If I debug through the operations, I see
throws COM exceptions that are swallowed "Exception from HRESULT: 0xC02020E9", "Exception from HRESULT: 0xC0202042" but that may be par for the course as I see a 0xC0202009 and 0xC0202042 thrown for a different AccessMode.
I found an article describing how to programmatically set a variable for an
Excel Adapter source (expand Programming Integration Service in SQL Server 2008)and it
seems that it and the EzAPI are calling the same methods in the same order but at this point, I'm sadly well out of my comfort zone of really knowing what's going on.
- SetComponentProperty("OpenRowsetVariable", "User::CurrentExcelSheet")
What I can do though, is compare XML. It seems the difference between the generated XML and a version I've manually assigned the variable, it seems the SqlCommandVariable is not being set for the source component.
One of the few search results I found was your
article covering the OleDB Source API. I saw that the SqlCommandVariable should also be set when the access mode is SQL "Command from Variable."
I modified EzComponents.cs, line 1027 of changeset 65381 to modify the setter for the DataSourceVariable property. This necessitates the order of operations being
- Set AccessMode to AM_SQLCOMMAND_VARIABLE
- Assign variable
but I don't care as it works. Someone on your team will undoubtedly have a better understanding of what the proper holistic solution is. The AccessMode check was required because I had also used a variable in my destination and an access mode of AM_OPENROWSET_FASTLOAD_VARIABLE
doesn't allow a property of SqlCommandVariable.
The protocol is to create an item on the Issue Tracker with all this info, yeah?
if (AccessMode == AccessMode.AM_SQLCOMMAND_VARIABLE)