EzAPI OLEDB Source command from variable

Jan 19, 2012 at 3:52 PM
Edited Jan 19, 2012 at 3:54 PM

I'm having a spot of trouble using the EzAPI to create an OLE DB Source and configuring it. I'd like to use an Access Mode of "SQL command from variable" / AccessMode.AM_SQLCOMMAND_VARIABLE but I can't seem to find the magic to actually have it set in the generated package. I have a longer, more detailed writeup over on StackOverflow  but thought I'd check here as well.

this.Source.AccessMode = AccessMode.AM_SQLCOMMAND_VARIABLE;
this.Source.DataSourceVariable = this.Variables["sourceQuery"].QualifiedName;

Is this a known issue or am I doing something wrong?
Coordinator
Jan 20, 2012 at 1:37 PM

What error(s) are you getting?

Can you try setting the values the other way around?  (set DataSourceVariable, then AccessMode)

You could try a fully qualified variable name ("User::sourceQuery") ... also make sure the CaSe is correct, and that the variable exists at that scope.

Looking through our test code, I don't see anywhere that we actually use AccessMode.AM_SQLCOMMAND_VARIABLE with the EzAPI code, so it could be there is a bug there.

Jan 20, 2012 at 9:02 PM

Hi Matt,

(@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 [1]: 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 

 base.ReinitializeMetaDataNoCast()

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")
  • AcquireConnections(null)
  • ReinitializeMetaData()
  • ReleaseConnections()

 

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."

 

Resolution

I modified EzComponents.cs, line 1027 of changeset 65381 to modify the setter for the DataSourceVariable property. This necessitates the order of operations being 

  1. Set AccessMode to AM_SQLCOMMAND_VARIABLE
  2. 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.

                if (AccessMode == AccessMode.AM_SQLCOMMAND_VARIABLE)
                {
                    m_comp.SetComponentProperty("SqlCommandVariable", value); 
                }

The protocol is to create an item on the Issue Tracker with all this info, yeah?