Data Migration and processing

04
Oct

When loading data through an SSIS package, one of the problems we face is when data causes a step to fail.  If the source data contains 10,000 rows, how do we tell which row had the data that was too long, in the wrong format, null or caused the bad join? Well, when running in automated mode (via a SQL agent job), SQL will log the cause of the error (the error description and the column), but will not log anything

19
Sep

One of the problems with moving a SQL insert statement to SSIS based packages is that logic which is freely available in SQL needs some work arounds to get the same result in SSIS. Such work arounds needs to be developed when working with pattern matching. In SQL, doing pattern matching is fairly easy. We may match patterns for logical tests using the SQL LIKE clause as follows: ...,[SLAM_HRG_Code] = CASE WHEN LEFT(r.[Service],1) LIKE '[A-Za-z]' AND SUBSTRING(r.[Service],2,1) LIKE '[A-Za-z]' AND ISNUMERIC(SUBSTRING(r.[Service],3,2))

09
Sep

If you ever have problems loading EXCEL based data into SQL via SSIS, you are not alone.  It can cause all kinds of problems with data type selection for the columns.  The real problem is not with how SSIS selects the data types for all the columns, but actually how far down a spread sheet that SSIS scans to process the data. I think of this as a deliberate Microsoft bug (or a feature, as they like to call it). I am

19
Feb

The purpose of Merge is where you have two tables – maybe a transaction table and a staging table which could have records to be inserted and/or replaced in the transaction table.  Or maybe you have two tables of people (staff or patients) – one table (the master) with last years data and one table from this year, and you want to bring the master up to date with additions, updates or removals. Under SQL 2005 you would have had to

05
Feb

With the introduction of SQL Server 2012, it is worth looking at the features available in the different versions of SQL Server, and the benefits that they bring. The following list the major features (and a few negatives) for each upgrade step from SQL 2005 to 2012. Note, SQL Server 2012 SP1 is now available, and brings very few new features, but improves performance and satiability. SQL Server 2005 to 2008 • Transparent Data Encryption. The ability to encrypt

17
Oct

One of the functions most useful in programming is Instr. This routine gets parts of strings - yet the funtion is missing in SQL Server. Well, its missing no more - welcome to my version which I call GETPART. This function returns part of a string based on a seperator character As an example select GetPart("the~cat~sat~on",'~',2) returns "cat" So imagine you have a persons name in a column of "Jaffa Brown" - Getpart(Name,' ',1) will get you the first name.

24
Sep

There is a bug in SQL Server 2008 and 2005.  When you try and link a 64 bit server to a 32 bit server, the select of data can fail.   But the fail is not obvious. When you say "select * from linkedserver.mydbname.dbo.table" it will return a message about stored procedures such as: The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator. This is because

04
Sep

Things to check when your data warehouse loads take too long‏

If you ever find yourself suffering from slow data load problem, let me say without a doubt…. there is no situation where the loading of data into a data warehouse should go beyond a couple of hours. If a data warehouse here at WhippsX NHS Trust can load 2 years worth of data from 3 different trusts in less than 2 hours, any data warehouse should be able to load a days worth of data in a few

30
Mar

If you ever struggle to backup, process or use a database only to find that somebody (somewhere) is using it, then this collection of SQL commands will help. They will list the processes and will then allow you to either shout at the people, or if needed, kill the processes.  If you KILL them, SQL will take care of making sure its a clean kill - in that any updates will be rolled back, and

26
Mar

Whenever you INSERT data into SQL tables, as each row is added, it also updates the index data on a row by row basis.  So a table with 15 indexes will actually update 16 rows (1 data and 15 index rows) for each insert. Where you are inserting thousands of rows, this can make for MASSIVE log files and LONG processing times. So, to make insertion super fast, always a good idea to DISABLE non-clustered indexes (those indexes which