SSIS Control flow with Precedence constraints need to come together

Problem:

• When developing in an SSIS For each loop, e.g. process a set of files in a directory.
• Where different actions need to be undertaken depending on the file type
• And Precedence constraints are used to accomplish those different actions in separate dataflow tasks.
• After the separate dataflow tasks, link those to one more tasks common to all constraints.
• Then: the result will be that the common tasks are never executed.

Example:

• In the example The Move the File task will not be executed.

Cause:

• Constraint expressions not only work at the exit of a task. The are also active at the entry of a task.
• In the example: the 2 green arrows pointing to the common task 'Move the file' need to be valid simultaneously, before the common task gets executed. But it is either File type A or file type B that gets processed in one iteration of the Foreach file loop.

Solution:

• Double click the green arrow going from 'process type A' to 'Move the file'.
• Change the Multiple constraints property from logical AND to logical OR.
• The green line will change to a dotted green line, for both tasks A and B.

Example:

Check VAT numbers Webservice hosted by EU

There is a webservice hosted by the European Union to check VAT numbers for any of the member states: http://ec.europa.eu/taxation_customs/vies/checkVatService.wsdl

An example of how to make use of this in Visual Studio was written VIES VAT number checker by Christian Kleinheinz.

SQL Server Maintenance Solution

Introduction

Because we needed additional processing power some databases need to be migrated to a new server. On the current server we have a 3^rd party database maintenance tool that works well for keeping the database indexes and table healthy. I discovered that we do not have any more licenses for the new server. The SQL 2008 R2 maintenance plans are rather simple and do not provide the fine tuning tools essential to run the maintenance plans efficiently and with as little time necessary. Some of those databases are nearly 24 hours per day.

SQL Server maintenance solution

The need for an extra license has been reported to management. While they decide on buying an extra license or not, I searched the internet for available and workable tools for free. The SQL Server Maintenance Solution by Ola Hallengren seems to fit what is needed for the time being.

The SQL Server Maintenance Solution is essentially a SQL server script to run on your server.

Installation

• Download the script from Downloads at Ola's site, it is called MaintenanceSolution.sql
• Start SSMS and connect to the database server. You need to have sysadmin rights.
• Open the script file MaintenanceSolution.sql. The script uses the master database.
• To create jobs, edit the lines32 and 33, leave line 31 with @CreateJob = “Y”. In my case we use the R drive on the server

SET @BackupDirectory     = N'R:\Backup' -- Specify the backup root directory.
SET @OutputFileDirectory = N'R:\Log'    -- Specify the output file directory. If no directory is specified, then the SQL Server error log directory is used.

• · Further down the script there is code to create 11 jobs. To distinguish these jobs from other jobs that are defined on the server, I inserted the prefix ‘MNTSOL’ in the names of the jobs. That way they are more easily recognizable and they are listed together.



• SET @JobName01 = 'MNTSOL DatabaseBackup - SYSTEM_DATABASES - FULL'
  SET @JobName02 = 'MNTSOL DatabaseBackup - USER_DATABASES – DIFF'
  Run the script



• The script creates a number of tables and stored procedures in the master database




• TABLE [dbo].[CommandLog]


• FUNCTION [dbo].[DatabaseSelect]


• PROCEDURE [dbo].[CommandExecute]


• PROCEDURE [dbo].[DatabaseBackup]


• PROCEDURE [dbo].[DatabaseIntegrityCheck]


• PROCEDURE [dbo].[IndexOptimize]

• Because @CreateJob = “Y” 11 jobs were created in the SQL Server Agent Jobs section.

• The jobs use the table and stored procedures. They are not scheduled yet. They are not setup to send alerts.

Index Optimize job

As the first test I ran the Index Optimize job on the test server. The user databases are not quite large. The job ran for about 5 minutes. After which I opened the table dbo.CommandLog to see what was registered in there. I saw 6 records, for 4 different databases. Each row explained in detail what kind of action was undertaken on a particular index.

Example of a row in the CommandLog:

DatabaseName	Sales
SchemaName	fact
ObjectName	SalesOrder
ObjectType	U
IndexName	IX_SalesOrder
IndexType	2
StatisticsName	NULL
PartitionNumber	NULL
ExtendedInfo	<ExtendedInfo> <PageCount>666349</PageCount> <Fragmentation>99.1803</Fragmentation></ExtendedInfo>
Command	ALTER INDEX [IX_SalesOrder] ON [Sales].[fact].[SalesOrder] REORGANIZE WITH (LOB_COMPACTION = ON)
CommandType	ALTER_INDEX
StartTime	2012-11-22 10:48:13.717
EndTime	2012-11-22 10:48:16.833
ErrorNumber	0
ErrorMessage	NULL

Conclusion

This looks like a workable solution. I will keep on testing this during the upcoming days .

LightSwitch 2011 out of browser app shows completely blank screen after installation

Problem

After a migration of a LightSwitch application with XtraReports to a new web server with Windows 2008 and IIS 7, and after installation of the out of browser application on the clients, the app starts up with a blank screen. No menus or status bar is shown at all.

Solution

Some kind of error must have happened, but no indication at all of what happened. I have read about the blank screen in fora on the internet: it involves setting the basic authentication to false. So I tried to follow the advice, but in the IIS manager for the LightSwitch website there was not even Basic authentication under the authentication options.

• Basic authentication is not installed on the web server:
• On the 2008 server, run Server Manager
• Select Roles / Web Server (IIS), in the Role services pane, add the Basic Authentication role, (along with Windows Authentication)
• 
• Restart IIS Manager, select the LightSwitch web site
• select Authentication, verify that the Basic Authentication is disabled
• 

After setting Basic Authentication and stopping and starting the web server, I started the client app again, and this time I was shown a Silverlight dialog box with an error message: ‘Could not load file or assembly DevExpress.Xpf.V11.1.LightSwitch.Common.dll…’

To solve the real problem I had to manually copy this file from my development machine to the server under …Inetpub\wwwroot\Myapp\bin