Large SSIS package runs out of resources/memory on server

Problem

A large SSIS package, scheduled as a SQL server job, that needs to import tens of large Excel files into a datawarehouse, always halted with errors out of resources or memory on a SQL 2008 R2. We noticed that on the file server only 2 or 3 of the excel files were handled, and moved over to another folder, before the package halted.
Cause
After inspection of the way the package was constructed, I noticed that several data flow tasks were not interconnected to run in serial, so they ran in parallel, because the dataflow tasks do not depend on each other.
Solution
In Visual Studio 2008 we connected each data flow task to the next so that all data flow tasks ran in serial, not in parallel. After installing the package on the server, we launched the job and this time the job ran fine. Although it still ate large amounts of CPU it kept on going. Of course I will have to monitor this for the next days to see if the job runs properly from now on.
Conclusion
Parallel processing may sound fine, but I doubt that SSIS has enough intelligence to handle things properly, it looks like it starts all the flows at once and then chokes in all parallel threads. Therefore if there is no need for a parallel processing just let the flows work in serial. If one really wanted parallel processing, it would probably be better to split the package in different packages, schedule those to start at the same time and then let the operating system handle the parallel processing. But then your maintenance of the packages is more of a burden in comparison to whatever time you may gain over serial processing.

Visual Studio 2008 / Windows 7 / Nvidia display problems

Problem
Recently I have been working on a notebook with NVidia Quadro FX 1700M. Windows 7 enterprise 32-bit SP1, with Visual Studio 2005 and 2008. 2 1920x1200 LCD monitors. The Visual Studio 2008 environment gave problems: I could not select certain vertical scrollbars or open dropdwon boxes , particularly in the Source Control Explorer window for TFS. Which made it impossible for me to recent versions of projects from the source control system. I noticed that the vertical scrollbar in the source control explorer window could be manipulated when I moved the cursor approx 2 cm to the right of the scrollbar. when I hovered 2 cm to the right the scrollbar changed color and by clicking I could then moved it up or down. But I could not click the button bar on top of the window, almost as if it all buttons were disabled.
On this computer I als used SQL Server Management studio. In this window I noticed that when dragging the right hand edge of the Object explorer window, a ghost edge was displayd 2 cm to the left of my cursor, althought I could click and drag on the exact edge position.
Diagnose
These 2 phenomena made it look like a display driver problem. Since the Nvidia drivers were the certified Windows drivers I was reluctant to update them by downloading newer drivers from the Nvidia site. By comparing with the screens from a collegue with similar hardware, I noticed my Icons and windows were taller than his.
Solution

• In Windows 7 rightclick on the desktop and select 'screen resolution'

• Click on 'make text and other items larger or smaller

• The setting was 125% - Select 'Smaller 100%' (then it dos not scale)
• click on apply
• Log out and login - Problem is solved !

Server prerequisites to host Lightswitch apps - part 2

To install the prerequisites on SBS server 2008 with SQL 2008 (not R2).
On the SBS server use the Web Platform Installer 3.0.
In the Web Platform Installer menu choose 'Products', in the bar on the left choose 'Tools'.
Then select Visual Studio Lightswitch 2011 Server Runtime without local SQL.
Althought this is for a 3-Tier deployment ( IIS server - SQL server on separate machines) this should make it work. I intend to use the SQL 2008 standard edition on the SBS server.

2012-03-10 - I have discoverd that sometimes it is necessary to install the Web Deployment Tool also. This is necessary on the Windows Server with IIS, to publish from Visual Studio to the Web server, i.e. when not publishing to a package. New post about installation here.

How Reporting Services on 64-bit server can print report from MS-Access 2007 accdb

Introduction to the problem
When you develop a report on a 64-bit server in Business Intelligence studio that reports from an accdb database (MS-Access 2007/2010 format), everything will work fine, because it uses 32-bit drivers. It is only when you deploy the ms-access datasource and report to the 64-bit Reporting services site that you run into a problem
Solution 1
Install the Access version 12 oledb drivers - Use SSIS data flow task (which can be made to run in 32-bit) to read the data and send to output to a DataReader destination component. This can be picked up as a datasource by Reporting Services and serve as basis for a report.
Prerequisite
The Jet 4.0 odbc driver will not wortk on the new accdb format. You have to install the MS-Access version 12 drivers first and install them on the 64-bit machine.
Download the driver from Microsoft downloads
Making it work
I have not tried this, because I opted for solution 2, but the concept of using a Datareader destination in SSIS may well be usable in other situations.
Solution 2


The solution came from Daniel Cai's blog
Apparently Microsoft has made 64-bit drivers for accdb files and included them in the MS-Access 2010 redistributable.
Select the 64-bit download from Microsoft downloads here
Note: If you had the Microsoft Access 2007 database engine installed, you will need to uninstall it.
Install it on the 64-bit server and change the connection string in the Reporting services datasource
Provider=Microsoft.Jet.OLEDB.4.0; 
to 
Provider=Microsoft.ACE.OLEDB.12.0;

LightSwitch 2011 - Securing access to applications

MSDN article about securing access to LightSwitch apps

Lightswitch 2011 - Entity default creation date and time

Creation date and time
Entities have no default property. If the entity needs a creation datetime field that registers the time when an entity record was created you can use the ' <entity> _Created' event from the from the 'Write Code' drop-down menu in the entity designer. 

For instance for an entity called 'Personeel' I want to use a field DatumCreatie that contains the date and time when the record was created. In the '<entity>_Created' event I need to add this code:

        partial void Personeel_Created()
        {
            // set default values for any field of the entity where necessary
            this.DateCreation = DateTime.Now;
        }

DateCreation is set as a required field in the entity designer.
In Lightswitch screens I use the DateTime Viewer control to show this Creation datetime field, since it should not be modifiable by the user.
Modification date and time
To track each time a record has been changed, I have added another datetime field to the entity. In the 'Write Code' I have selected the '<entity>_Updating' event, to change the modification date.
        partial void Personeels_Updating(Personeel entity)
        {
            entity.DateModification = DateTime.Now;
        }
DateModification is not set as required in the entity designer.
Take care NOT to use the '<entity>_Updated' event. Because the change of the date would retrigger the same event endlessly (i.e. until you run into stack overflow).
Creation and modification datetime fields default values and triggers
Creation date can be managed in SQL server by specifying a default value for the DateCreation field. The default value is a sql server GETDATE() function.
For the modification date a trigger after update could be used.