SSIS Error with Destination Adapter

Nov 28, 2011 at 12:49 PM
Edited Nov 28, 2011 at 3:11 PM

Can anyone help me with the following error message I'm receiving in BIDS when running a simple integration to delete all records from a list?  The data flow is using a Sharepoint list source adapter connected to a Sharepoint list destination adapter and joining on the IDs.  The destination adapter fails with the following error message:

SSIS package "Company.dtsx" starting.
Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.
Warning: 0x80049304 at Data Flow Task, SSIS.Pipeline: Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available.  To resolve, run this package as an administrator, or on the system's console.
Information: 0x40043006 at Data Flow Task, SSIS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Task, SSIS.Pipeline: Pre-Execute phase is beginning.
Information: 0x4004300C at Data Flow Task, SSIS.Pipeline: Execute phase is beginning.
Information: 0x0 at Data Flow Task, SharePoint List Source: Loaded 839 records from list 'Company List' at 'https://xxxxx'. Elapsed time is 13097ms
Error: 0xC0047062 at Data Flow Task, SharePoint List Destination [104]: System.ServiceModel.ProtocolException: The content type text/html; charset=utf-8 of the response message does not match the content type of the binding (text/xml; charset=utf-8). If using a custom encoder, be sure that the IsContentTypeSupported method is implemented properly. The first 1024 bytes of the response were: '
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
<html xmlns:o="urn:schemas-microsoft-com:office:office" lang="en-us" dir="ltr">
<head><meta name="GENERATOR" content="Microsoft SharePoint" /><meta name="progid" content="SharePoint.WebPartPage.Document" /><meta http-equiv="Content-Type" content="text/html; charset=utf-8" /><meta http-equiv="Expires" content="0" /><meta name="ROBOTS" content="NOHTMLINDEX" /><title>

</title><link rel="stylesheet" type="text/css" href="/_layouts/1033/styles/Themable/corev4.css?rev=iIikGkMuXBs8CWzKDAyjsQ%3D%3D"/>
<script type="text/javascript">var ULS;if (!ULS) ULS=new Object();ULS.enable=true;ULS.Correlation="580297d8-d55a-4469-9c30-9be70b85c45d";</script><script type="text/javascript">
// <![CDATA[
document.write('<script type="text/javascript" src="/_layouts/1033/init.js?rev=BJDmyeIV5jS04CPkRq4Ldg%3D%3D"></' + 'script>');
document.write('<script type="text/javascript" src="/ScriptResource.axd?d=PN_U'.

Server stack trace:
   at System.ServiceModel.Channels.HttpChannelUtilities.ValidateRequestReplyResponse(HttpWebRequest request, HttpWebResponse response, HttpChannelFactory factory, WebException responseException, ChannelBinding channelBinding)
   at System.ServiceModel.Channels.HttpChannelFactory.HttpRequestChannel.HttpChannelRequest.WaitForReply(TimeSpan timeout)
   at System.ServiceModel.Channels.RequestChannel.Request(Message message, TimeSpan timeout)
   at System.ServiceModel.Dispatcher.RequestChannelBinder.Request(Message message, TimeSpan timeout)
   at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout)
   at System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage methodCall, ProxyOperationRuntime operation)
   at System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage message)

Exception rethrown at [0]:
   at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
   at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
   at Microsoft.Samples.SqlServer.SSIS.SharePointUtility.ListsService.ListsSoap.UpdateListItems(UpdateListItemsRequest request)
   at Microsoft.Samples.SqlServer.SSIS.SharePointUtility.ListsService.ListsSoapClient.ListsService_ListsSoap_UpdateListItems(UpdateListItemsRequest request)
   at Microsoft.Samples.SqlServer.SSIS.SharePointUtility.ListsService.ListsSoapClient.UpdateListItems(String listName, XElement updates)
   at Microsoft.Samples.SqlServer.SSIS.SharePointUtility.Adapter.ListsAdapter.ExecuteSharePointUpdateBatch(String listName, String viewId, XElement batchXml, Int16 batchSize)
   at Microsoft.Samples.SqlServer.SSIS.SharePointUtility.ListServiceUtility.DeleteListItems(Uri sharepointUri, NetworkCredential credentials, String listName, String viewName, IEnumerable`1 idList)
   at Microsoft.Samples.SqlServer.SSIS.SharePointListAdapters.SharePointListDestination.ProcessInput(Int32 inputID, PipelineBuffer buffer)
   at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper100 wrapper, Int32 inputID, IDTSBuffer100 pDTSBuffer, IntPtr bufferWirePacket)
Error: 0xC0047022 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "SharePoint List Destination" (104) failed with error code 0x80131501 while processing input "Component Input" (113). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.
Information: 0x40043008 at Data Flow Task, SSIS.Pipeline: Post Execute phase is beginning.
Information: 0x4004300B at Data Flow Task, SSIS.Pipeline: "component "SharePoint List Destination" (104)" wrote 0 rows.
Information: 0x40043009 at Data Flow Task, SSIS.Pipeline: Cleanup phase is beginning.
Task failed: Data Flow Task
Warning: 0x80019002 at Package2: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Company.dtsx" finished: Failure.

Nov 28, 2011 at 2:56 PM

Looks like the site in the target address is causing a HTML page which is not the webservice, to come back. Double-check your URL and make sure it is for the list, and that security is OK for your account.

Nov 28, 2011 at 3:17 PM

The SSIS package is deleting some, but not all, of the list records.  Therefore, I don't believe it's an issue with the URL or account permission.  Also, the source and destination URLs are identical and I haven't had any issues loading records into the list.  The issue above is only experienced when using the destination adapter to delete records.

Dec 22, 2011 at 2:10 PM

Perhaps the batch size is too big for deleting and it is timing out? Try to reduce the batch size to something smaller. Based on various end user workflows / data, this is a knob you have to tweak yourself for your environment.