1

I am using SSIS with an OData source to pull SharePoint Online 2013 List data into a SQL table. I'd like to filter on the Attachments boolean but can not figure out how to query it.

I saw that an Attachments property exists when I export the Attachment xml information from /_api/Web/Lists/getbytitle('<List>')/items?$expand=AttachmentFiles. I see the following:

        <m:properties>
            <d:FileSystemObjectType m:type="Edm.Int32">0</d:FileSystemObjectType>
            <d:Id m:type="Edm.Int32">143</d:Id>
            <d:ServerRedirectedEmbedUrl/>
            <d:ContentTypeId>0x010029B7BBCFD15FFC439AC3ED59CAD79F26</d:ContentTypeId>
            <d:Title m:null="true"/>
            <d:OData__x0031_0BDays_Email_Date m:null="true"/>
            <d:OData__x0032_0BDays_Email_Date m:null="true"/>
            <d:OData__x0033_BDays_Email_Date m:null="true"/>
            <d:Covenant_Owner_Name m:null="true"/>
            <d:Covenant_Owner_Id m:null="true"/>
            <d:Frequency_Narration m:null="true"/>
            <d:Business_OwnerId m:type="Edm.Int32">1</d:Business_OwnerId>
            <d:Executive_OwnerId m:type="Edm.Int32">37</d:Executive_OwnerId>
            <d:AuthorId m:type="Edm.Int32">491</d:AuthorId>
            <d:EditorId m:type="Edm.Int32">248</d:EditorId>
            <d:Modified m:type="Edm.DateTime">2016-05-10T13:24:58Z</d:Modified>
            <d:Created m:type="Edm.DateTime">2016-05-04T13:40:05Z</d:Created>
            <d:Flexible m:null="true"/>
            <d:ID m:type="Edm.Int32">143</d:ID>
            <d:OData__UIVersionString>2.0</d:OData__UIVersionString>
            <d:Attachments m:type="Edm.Boolean">true</d:Attachments>
            <d:GUID m:type="Edm.Guid">6e808ac8-b836-49b0-8343-9ae627b661d0</d:GUID>
        </m:properties>

I assume since I can query on an OData source with query options like $filter and $select that I could filter for: $filter = Attachments eq true as a query option since many of the other parameters do work, like Id, AuthorId, etc.

enter image description here

The error I receive is pretty vague: The remote server returned an error: (400) Bad Request. and the following when I expand it:

The remote server returned an error: (400) Bad Request. (System)

------------------------------
Program Location:

   at System.Net.HttpWebRequest.GetResponse()
   at Microsoft.SqlServer.IntegrationServices.DataFeedClient.Http.HttpWebRequestWrapper.GetResponse()
   at Microsoft.SqlServer.IntegrationServices.DataFeedClient.Http.WebRequestHelper.GetResponseWithThreadSafeRetry(IWebRequest& request, Func`1 createWebRequest, Int32 retryCount, Int32 retrySleepInMilliseconds, Boolean isOAuth, Func`2 refreshRequestWithNewAccessToken, Func`2 getStatusCode)
   at Microsoft.SqlServer.IntegrationServices.DataFeedClient.Http.WebRequestHelper.GetResponseWithThreadSafeRetryAndImpersonation(IWebRequest& request, DataFeedConnection connection, Func`1 createWebRequest)
   at Microsoft.SqlServer.IntegrationServices.DataFeedClient.DataFeedCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at Microsoft.DataTransformationServices.DataFlowUI.ODataConnectionPage.PreviewButton_Click(Object sender, EventArgs e)

Any guidance would be appreciated.

0

Instead of filtering

$filter=Attachments eq true

try

$filter=Attachments ne false.

Works for me.

  • Thanks for the help. Using ne false gave me the same error as before. I stumbled across this question that states you can't filter on attachments. I'm hoping it's not entirely true though. Any thoughts? – joshualeond Nov 14 '16 at 15:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.