User-defined fields empty - SP2010 with SSIS2005

Oct 14, 2010 at 3:40 PM
Edited Oct 14, 2010 at 4:11 PM

I've been successfully using the list adaptor in several packages for a year or so with our SP2003 server. I am now trying to create a package for the first time pulling data from our new SP2010 server (using SSIS 2005). At first I thought it would be a piece of cake since the Source adapter had no problem at all retrieving the metadata for the list from SP. However, at runtime the package failed. I put a data viewer so I could see what was coming from SP and was surprised to see this:

ID LinkTitle Energyloadmap Energyauditwalkthr Energysavingproced LightingImprovements OfficeSetbacks_x00 CompressedAirSyste BarrelHeaterJacket Percentagecomplete CompanyNumber
2 Clinton               float;#0  
3 Mebane               float;#0  

Now, I don't do much Sharepoint work, but I think the ID and LinkTitle fields are the ones provided by Sharepoint, correct? The others were created by the author of the list. The Percentagecomplete field is a calculated field I am not going to retrieve anyways, I just included it in the trial to see if it could be retrieved.

So, can someone give me a clue as to why the user-defined fields are all empty? They definitely contain data when the list is viewed in the browser:

 

 

 
 Open Menu 
  
  
 Open Menu 
  
  
  
  
  
  
<input class="s4-itm-cbx" title="Clinton Energy Initiative " type="checkbox" />
 Open Menu 
Complete Incomplete Complete Incomplete Incomplete Incomplete Incomplete
28.57 %
02
<input class="s4-itm-cbx" title="Mebane Energy Initiative " type="checkbox" />
 Open Menu 
Incomplete Incomplete Complete Incomplete Incomplete Complete Complete
42.86 %
20

 

<input title="text" name="__spText1" /> <input title="text" name="__spText2" />

<input id="_wpcmWpid" name="_wpcmWpid" type="hidden" /><input id="wpcmVal" name="wpcmVal" type="hidden" />

 

Oct 15, 2010 at 3:40 PM

I've created a little VB app to access the Sharepoint web service and retrieve the xml returned from GetListItems for this list. The result is below. All of the data is included. One thing I notice is that ows_LinkTitle contains "Clinton Energy Initiative"" while my result from the Sharepoint adapter contains only "Clinton"

Another strange thing is if I add another column to the Output, "Title" for example, this error is returned:

[SharePoint List Source [6399]] Error: System.Runtime.InteropServices.COMException (0xC0048004): Exception from HRESULT: 0xC0048004    at Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSCustomPropertyCollection90.get_Item(Object Index)    at Microsoft.Samples.SqlServer.SSIS.SharePointListAdapters.SharePointListSource.<PrimeOutput>b__22(IDTSOutputColumn90 col)    at System.Linq.Enumerable.<>c__DisplayClass12`3.<CombineSelectors>b__11(TSource x)    at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()    at System.Xml.Linq.XContainer.AddContentSkipNotify(Object content)    at System.Xml.Linq.XContainer.Add(Object content)    at Microsoft.Samples.SqlServer.SSIS.SharePointUtility.Adapter.ListsAdapter.GetSharePointListItemData(String listName, String viewId, IEnumerable`1 fieldNames, XElement query, Boolean isRecursive, Int16 pagingSize)    at Microsoft.Samples.SqlServer.SSIS.SharePointUtility.ListServiceUtility.GetListItemData(Uri sharepointUri, String listName, String viewName, IEnumerable`1 fieldNames, XElement query, Boolean isRecursive, Int16 pagingSize)    at Microsoft.Samples.SqlServer.SSIS.SharePointListAdapters.SharePointListSource.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers)    at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPrimeOutput(IDTSManagedComponentWrapper90 wrapper, Int32 outputs, Int32[] outputIDs, IDTSBuffer90[] buffers, IntPtr ppBufferWirePacket)

 

<listitems xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" 
xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema" xmlns="http://schemas.microsoft.com/sharepoint/soap/"><rs:data 
ItemCount="2">   <z:row ows_ContentTypeId="0x0100C57620A9D66E3546833C842637B3A3A4" ows_Title="Clinton Energy Initiative" 
ows_LinkTitleNoMenu="Clinton Energy Initiative" ows_LinkTitle="Clinton Energy Initiative" ows_LinkTitle2="Clinton Energy Initiative" 
ows_Energy_x0020_load_x0020_map="Complete" ows_Energy_x0020_audit_x0020_walkthr="Incomplete" 
ows_Energy_x0020_saving_x0020_proced="Complete" ows_Lighting_x0020_Improvements="Incomplete" 
ows_Office_x0020_Setbacks_x0020__x00="Incomplete" ows_Compressed_x0020_Air_x0020_Syste="Incomplete" 
ows_Barrel_x0020_Heater_x0020_Jacket="Incomplete" ows_Percentage_x0020_complete="float;#0.285714285714286" ows_Company_x0020_Number="02" 
ows_ID="4" ows_ContentType="Item" ows_Modified="2010-10-11 11:53:12" ows_Created="2010-10-08 14:18:00" ows_Author="2;#NYPRO\HouleR" 
ows_Editor="2;#NYPRO\HouleR" ows_owshiddenversion="2" ows_WorkflowVersion="1" ows__UIVersion="512" ows__UIVersionString="1.0" 
ows_Attachments="0" ows__ModerationStatus="0" ows_SelectTitle="4" ows_Order="400.000000000000" 
ows_GUID="{19412D14-D2AB-4709-8DC2-0C77B9D5AFCB}" ows_FileRef="4;#sites/dept/EHS/Sustainability/Lists/EnergyInit/Clinton/4_.000" 
ows_FileDirRef="4;#sites/dept/EHS/Sustainability/Lists/EnergyInit/Clinton" ows_Last_x0020_Modified="4;#2010-10-08 14:18:00" 
ows_Created_x0020_Date="4;#2010-10-08 14:18:00" ows_FSObjType="4;#0" ows_SortBehavior="4;#0" ows_PermMask="0xb008431061" 
ows_FileLeafRef="4;#4_.000" ows_UniqueId="4;#{5854C5B4-CA68-46F0-93B8-916E5F5E612F}" ows_ProgId="4;#" 
ows_ScopeId="4;#{5450A236-5778-4466-8FEA-188002C3E864}" ows__EditMenuTableStart="4_.000" ows__EditMenuTableStart2="4" 
ows__EditMenuTableEnd="4" ows_LinkFilenameNoMenu="4_.000" ows_LinkFilename="4_.000" ows_LinkFilename2="4_.000" 
ows_ServerUrl="/sites/dept/EHS/Sustainability/Lists/EnergyInit/Clinton/4_.000" 
ows_EncodedAbsUrl="http://clnshrpt3/sites/dept/EHS/Sustainability/Lists/EnergyInit/Clinton/4_.000" ows_BaseName="4_" ows_MetaInfo="4;#" 
ows__Level="1" ows__IsCurrentVersion="1" ows_ItemChildCount="4;#0" ows_FolderChildCount="4;#0" />   <z:row 
ows_ContentTypeId="0x0100C57620A9D66E3546833C842637B3A3A4" ows_Title="Mebane Energy Initiative" ows_LinkTitleNoMenu="Mebane Energy 
Initiative" ows_LinkTitle="Mebane Energy Initiative" ows_LinkTitle2="Mebane Energy Initiative" ows_Energy_x0020_load_x0020_map="Incomplete" 
ows_Energy_x0020_audit_x0020_walkthr="Incomplete" ows_Energy_x0020_saving_x0020_proced="Complete" 
ows_Lighting_x0020_Improvements="Incomplete" ows_Office_x0020_Setbacks_x0020__x00="Incomplete" 
ows_Compressed_x0020_Air_x0020_Syste="Complete" ows_Barrel_x0020_Heater_x0020_Jacket="Complete" 
ows_Percentage_x0020_complete="float;#0.428571428571429" ows_Company_x0020_Number="20" ows_ID="5" ows_ContentType="Item" 
ows_Modified="2010-10-12 09:53:13" ows_Created="2010-10-08 14:18:20" ows_Author="2;#NYPRO\HouleR" ows_Editor="2;#NYPRO\HouleR" 
ows_owshiddenversion="2" ows_WorkflowVersion="1" ows__UIVersion="512" ows__UIVersionString="1.0" ows_Attachments="0" 
ows__ModerationStatus="0" ows_SelectTitle="5" ows_Order="500.000000000000" ows_GUID="{EF6DF402-42E2-4DC8-AA55-E5DE35A51FD5}" 
ows_FileRef="5;#sites/dept/EHS/Sustainability/Lists/EnergyInit/Mebane/5_.000" 
ows_FileDirRef="5;#sites/dept/EHS/Sustainability/Lists/EnergyInit/Mebane" ows_Last_x0020_Modified="5;#2010-10-08 14:18:20" 
ows_Created_x0020_Date="5;#2010-10-08 14:18:20" ows_FSObjType="5;#0" ows_SortBehavior="5;#0" ows_PermMask="0xb008431061" 
ows_FileLeafRef="5;#5_.000" ows_UniqueId="5;#{1B85C685-B16C-4433-96D4-B0C8C5435880}" ows_ProgId="5;#" 
ows_ScopeId="5;#{5450A236-5778-4466-8FEA-188002C3E864}" ows__EditMenuTableStart="5_.000" ows__EditMenuTableStart2="5" 
ows__EditMenuTableEnd="5" ows_LinkFilenameNoMenu="5_.000" ows_LinkFilename="5_.000" ows_LinkFilename2="5_.000" 
ows_ServerUrl="/sites/dept/EHS/Sustainability/Lists/EnergyInit/Mebane/5_.000" 
ows_EncodedAbsUrl="http://clnshrpt3/sites/dept/EHS/Sustainability/Lists/EnergyInit/Mebane/5_.000" ows_BaseName="5_" ows_MetaInfo="5;#" 
ows__Level="1" ows__IsCurrentVersion="1" ows_ItemChildCount="5;#0" ows_FolderChildCount="5;#0" /></rs:data></listitems>

Oct 15, 2010 at 6:45 PM

Just in case I was using an obsolete version, I've downloaded the latest msi, uninstalled and installed the latest. Here is the UserComponentTypeName from the BIDS Source Adapter dialog:

Microsoft.Samples.SqlServer.SSIS.SharePointListAdapters.SharePointListSource, SharePointListAdapters, Version=1.2005.0.0, Culture=neutral, PublicKeyToken=933a2c7edf82ac1f

Oct 18, 2010 at 5:10 PM

Is anyone supporting this? I've looked at the other questions being asked and see very few answers lately.

I've had to move on so I had to resort to using a Script Component to successfully accomplish my task. I would still like to know how to get the adapter working so I can resume using it in the future. The Script Component approach is a real PITA.