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

Problem

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:

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

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()  )

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

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

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.

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'

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

A deadlock was detected while trying to lock variables for R/W access

Problem

While testing an Integration Service package with a File Watcher task as described in Using the Script Task in SSIS to Process Data Files When They Arrive, the following error was thrown:

The script threw an exception: A deadlock was detected while trying to lock variables <variablelist> for read access and variables <variablelist> for read/write access. A lock cannot be acquired after 16 attempts. The locks timed out.

This was developed as an SSIS 2012 project and deployed on a SQL server in the Integration Services Catalog. The package ran fine when I tested it in Visual Studio and when I executed the task from the Catalog. The error came when I create a SQL Server Agent job to run the task.

First I read some articles about variable locking in SSIS; but why did my package run without errors when I ran it under my account?

Solution

The cause of the problem: it is the account under which the package is run in the Job step that does not have read/write access to the folders where the File Watcher Task and the ensuing File System Tasks are doing their work. In this case the job step was run as the SQL Server Agent Service Account.

Either give this service account read/write access to the folders used by the SSIS package (may not be a good idea) or use a special windows account with sufficient rights and create a SQL Server Agent proxy for this account, see Use a windows account to grant SSIS packages folder and file access on a file share

Error occurred while the attribute of the dimension from the database was being processed–snowflake dimension

Problem

While designing a new snowflake dimension in an Analysis services project, I received the following error message during deployment with Visual Studio of the project to the Analysis Server:

Errors in the OLAP storage engine: An error occurred while the 'Plant' attribute of the 'Device' dimension from the '<projectname>' database was being processed.

The snowflake dimension ‘Device’ is a combination of related tables: Country – Region – Plant – Device. The Dimension the same hierarchy as in the one-to-many relationship.

Each table is composed of a primary key Id column, a name column and various other properties. The snowflake dimension will be used for grouping and totalling production data, so that users can drill down from Country level to Regional, to Plant or to Device. The users do not know the Id values, they are shown the names.

The dimension attribute properties are setup to use the Id as Key, but to display the name and order the listed values by name, e.g. for Region

• KeyColumns: Region.id
• NameColumn: Region.Name
• OrderBy: Name

After the initial design of this dimension I wanted to deploy to the server, so as to be able to test my design in the Dimension Browser. Then I received the abovementioned error message.

SOLUTION: look for NULL values in Foreign key column that refers to parent table

The error message was displayed in the Error List window in Visual Studio. The Output window shows some more information about the error.

Warning -2128674815 : Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: 'dbo_Region', Column: 'Id', Value: '0'. The attribute is 'Region'.

When I queried SQL database table Plant for foreign key RegionId with value 0, none were found. When I queried for foreign key field being NULL, I found one such record in the Plant table. This record happens to be the main office of the company and is there for other purposes, so it must not be selected.

• Go to the Data Source View, right click on Plant table and select ‘Replace Table –> With New Named Query…
• In the query designer add the condition WHERE RegionId IS NOT NULL
• Save the query.
• Deploy the project

The project will now deploy without any further error messages, unless you have other data to clean up.

Analysis services 2012: a connection cannot be made, ensure that the server is running

After the installation of a new SQL Server 2012 with Analysis Services, I found that I could reach the Analysis services database perfectly well when I was remotely logged in on the Server and connected with SSMS locally. When I tried to connect with SSMS from another computer  I received the error message:

A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices.AdomdClient)

On the internet some advice is given about this connection problem.On the server with Analysis Services 2012 the SQL Server Browser must be running under the LOCALSYSTEM account. On my Server the browser service was running under LOCALSERVICE. After I changed this I still could not connect remotely.

Another advice was to try to connect to the Analysis Service using <Servername>:2383, in order not use SQL Server Browser. But I still received the same error message.

Finally it dawned to me that the firewall was probably blocking this port. On the Server with Analysis Services 2012, open ‘Windows Firewall with Advanced Security’ and add a new inbound rule, for TCP port 2383, to allow the connection in the Domain. This rule is similar as the rule one must setup for SQL Server Service in port 1433.

open ‘Windows Firewall with Advanced Security’

Select Inbound Rules, In Actions, click on ‘New Rule…’

In the New Inbound Rule Wizard, Rule Type, select Port, click Next

In the Protocols and Ports, Select ‘TCP’, Specific local ports, type ‘2383’, click Next

In Action, Select ‘Allow the connection’, Next

In Profile, check only ‘Domain’ (because I only want to grant access from my corporate domain). Next

In Name, type the name: ‘Sql Server analysis service port’ (or some other name that allows you to recognize your inbound rule). Finish.

As soon as the Inbound rule is created, the connection from another computer to the analysis service will work.

Dates in Excel files rendered from reports are displayed as plain numbers

Problem description

A report subscription is setup to send an email message on regular schedule. The report is included in the email as an attachment file rendered in excel format.

When MS-Windows users receive the email, they can open the excel file and dates will be shown in a normal date format:

When iPad users receive the email, they can open the excel file, but the dates are shown as numbers:

 

Workaround

Standard solution for MS-Windows users

In the report design, the date field, e.g. ‘ReportDate’ is displayed as a regular field

The textbox has a custom date format

The report is displayed with this custom format

And the export to excel will show formatted date cells when opened with MS-Windows

However on the iPad the date in column H will be shown as a number.

 

Workaround for iPad users

In the report design do not use a custom format property; instead use an expression with the Format function to represent the date:
=Format(Fields!ReportDate.Value, "dd/MM/yyyy")

The result from the report subscription email attachment looks like this on the iPad:

 

Reminder

With the workaround, the MS-Windows user will see the dates visualized correctly, but the cells are treated as plain text, not as dates. The cells are also left-aligned like any standard text.