Make a dynamic selection from SharePoint list data with the SSIS 2012 OData Source

Use SSIS to extract, transform and load data from a SharePoint list into a SQL Server table. Extraction from the SharePoint lists is done in a Data Flow Task by means of the OData Source for SSIS 2012. The Data must be dynamically extracted, i.e. only List data from the last <n> days must be read.

The OData Source Editor connects to the SharePoint REST service.

The URL of the REST service: http://myserver/_vti_bin/ListData.svc

A name after the URL of the service returns SharePoint Foundation list data in XML format as a standard Atom feed: http://myserver/_vti_bin/ListData.svc/Incidents

.In the OData Source Editor:

http://lh3.ggpht.com/-FOzCgFbywQM/VE-9e7vQEsI/AAAAAAAAHSU/i4vBVBJt0QI/s1600-h/image%25255B5%25255D.png

To make a selection of data created since 2014-08-04, the Filter is set to : $filter=Created+gt+datetime'2014-08-04'

http://lh6.ggpht.com/-JRuNM-_oGNQ/VE-9gn-AnJI/AAAAAAAAHSg/ue7d-wQcKRE/s1600-h/image%25255B8%25255D.png

There is no way to add a parameter in the Query options text box. How can the filter on the Created date be made variable so that only the last 5 days are read for a daily upload.

Solution

There are no expressions for the Filter property in the OData Source properties. However, in the Data Flow Task that contains the OData Source, we have Expressions that can be used to dynamically set the Query property of the OData Source.

First of all create a Project parameter called ‘LoadDays’ of type Int32 with the value 10.

In the SSIS package add a variable called ‘LoadCreated’ of type DateTime, with an expression to subtract 10 days from the current date: DATEADD( "d", - @[$Project::LoadDays] , GETDATE() )

http://lh5.ggpht.com/-M3x2TtHUB3Q/VE-9hxhuW7I/AAAAAAAAHSw/jk5B6CuTsfk/s1600-h/image%25255B23%25255D.png

In the Control Flow pane, select the Data Flow Task that contains the OData Source.

http://lh6.ggpht.com/-rSuaZFZF91o/VE-9jOiWCEI/AAAAAAAAHTA/UBcaXjQVMjo/s1600-h/image%25255B26%25255D.png

In the Properties Window, click on the ellipsis button of the Expressions property.

http://lh5.ggpht.com/-ygZP1SmV9TA/VE-9kOwhKfI/AAAAAAAAHTU/lyaxNZa93-w/s1600-h/image%25255B14%25255D.png

In the Property Expressions Editor window, in the Property column select the OData Source Query property, click on the ellipsis button to open the Expression Builder.

http://lh6.ggpht.com/-CgekwJUORak/VE-9lu2osVI/AAAAAAAAHTg/lTxFQsXf4xE/s1600-h/image%25255B20%25255D.png

The expression we create will need to mimic an OData filter expression like $filter=Created+gt+datetime'2014-08-04'

In the expression builder, the expression is:

"$filter=Created+gt+datetime'" + (DT_WSTR, 4)YEAR(@[User::LoadCreated]) + "-" + ( MONTH( @[User::LoadCreated]) < 10 ? "0" : "") + (DT_WSTR,2)MONTH( @[User::LoadCreated]) + "-" + (DAY( @[User::LoadCreated])<10 ? "0" : "" ) + (DT_WSTR,2)DAY( @[User::LoadCreated]) +"'"

The OData filter does not accept date literals like ‘201-8-4’, we need to add a leading zero for the month and day part, hence the conditional operator ( MONTH( @[User::LoadCreated]) < 10 ? "0" : "")

Click OK to close the Expression builder.

Click OK to close the Property Expressions Editor.

With the Data flow task selected, click the Data Flow tab. Select the OData Source control and edit. The filter property now holds the calculated values of the expression:

$filter=Created+gt+datetime'2014-08-04'

http://lh3.ggpht.com/-2G8dWyyhbuY/VE-9mgNdr3I/AAAAAAAAHTw/h6KR-zvGX7E/s1600-h/image%25255B32%25255D.png

When the SSIS package runs it will only load list data created after the calculated date.