SQLGeordie's Blog

Helping the SQL Server community……where i can!

Why would you never use SSIS Fast Load…? — November 14, 2016

Why would you never use SSIS Fast Load…?

We all know that if you want SQL Server to push data into a table then you want to batch the inserts / use a bulk insert mechanism but is there a time when performance isn’t everything?


Although it has its critics, SSIS is a very powerful tool for Extracting, Transforming and ultimately Loading data from and to various systems. I kind of have a love / hate relationship with SSIS, I love it but it seemingly hates me with a passion.

During a recent data migration project we had a series of packages using a stored procedure as the source and a SQL Server table as the destination. By using the OLE DB Destination task you have a series of options Data Access Modes which can provide various additional configurations. I won’t delve into all of these but have a look at the msdn link provided at the end for further information.

The ones I want to concentrate on are:

  • Table or view
  • Table or view – Fast Load

In short, fast load does exactly what it says on the tin, it loads data fast! This is because it is optimised for bulk inserts which we all know SQL Server thrives on, it isn’t too keen on this row-by-row lark.


Now, I won’t be providing performance figures showing the difference between running a package in fast load compared to row-by-row, this has been done to death and it is pretty much a given (in most cases) that fast load will out perform row-by-row.

What I do want to bring to your attention is the differences between the two when it comes to redirecting error rows, specifically rows that are truncated. One of the beauties of SSIS is the ability to output rows that fail to import through the error pipeline and push them into an error table for example. With fast load there is a downside to this, the whole batch will be output even if there is only 1 row that fails, there are ways to handle this and a tried and tested method is to push those rows into another OLE DB Destination where you can run them either in smaller batches and keep getting smaller or simply push that batch to run in row-by-row to eventually output the 1 error you want. Take a look at Marco Schreuder’s blog for how this can be done.

One of the issues we have exerienced in the past is that any truncation of a column’s data in fast load will not force the package to fail. What? So a package can succeed when in fact the data itself could potentially not be complete!?! Yes this is certainly the case, lets take a quick look with an example.

Truncation with Fast Load


I have provided a script to setup a table where we can test this. I will attempt through SSIS to insert data which is both below and above 5 characters in length and show the output.

USE tempdb;

DROP TABLE IF EXISTS dbo.TruncationTest;
DROP TABLE IF EXISTS dbo.TruncationTest_error;

CREATE TABLE dbo.TruncationTest
TruncationTestID INT IDENTITY(1,1),
TruncationTestDescription VARCHAR(5)

CREATE TABLE dbo.TruncationTest_error
TruncationTestID INT,
TruncationTestDescription VARCHAR(1000) --Make sure we capture the full value

This code will set up 2 tables, one for us to import into (TruncationTest) and another to capture any error rows that we will output (TruncationTest_error).

I set up a very quick and dirty SSIS package to run a simple select statement to output 3 rows and use the fast load data access mode:

SELECT  ('123') AS TruncationTestDescription UNION ALL
SELECT  ('123456789');

The OLE DB Source Editor looks like this:


the OLE DB Destination data access mode:


Finally, this is how the package looks:


Note the truncation warning. This is easy to see when viewing a package in Visual Studio, not so easy to pick up when you are dynamically generating packages using BIML.

Let’s run it……


Great, 3 rows populated into the TruncationTest table, everything worked fine! So let’s check the data:

SELECT * FROM dbo.TruncationTest


Eh? What happened there???? Where’s my ‘6789’ gone from row 3???

From this example you can see that the package succeeds without error and it looks as though all rows have migrated entirely but by querying the data after the package has completed you can see that the description column has indeed been truncated.

Let’s try the same test but changing the Data Access Mode to non-fast load (ie. Row-By-Row)

Truncation with row-by-row

In this example you can see that the row with truncation is in fact pushed out to the error pipeline as you would hope and expect.



We now have 3 rows being processed but one row pushing out to the error pipeline which is what we would expect and hope for.

Let’s take a look at the output:

SELECT * FROM dbo.TruncationTest ORDER BY TruncationTestID
SELECT TruncationTestDescription FROM TruncationTest_error


The results highlighted in red are those from the fast load, in green are the results from the row-by-row indicating that the error row was piped out to the error table.


You have a few different options here:

  1. Not really care and push the data through in fast load and suffer the concequences
  2. Run in row-by-row and suffer the performance hit
  3. Amend the OLE DB Source Output to be the same length as the destination column and redirect error rows from there.
  4. Probably loads of others involving conditional splits, derived columns and/or script tasks
  5. Apply option #1 and make sure that relevant (automated or otherwise) testing is applied

During the recent data migration project we were involved in we chose option #5. The reasons for this are:

  1. We wanted to keep the BIML framework, the code and the relevant mappings as simplistic as possible
  2. Performance was vital….
  3. …..but more importantly was the validity of the data we were migrating

We already had a series of automated tests setup for each package we were running and table we were migrating and we had to add to this a series of additional automated tests to check that no data itself was being truncated.

NOTE: Option #4 was also a very valid choice for us but due to the nature of the mapping between source and destination this was not something that was easily viable to implement.

I will leave the how we implemented these test this for another blog post 🙂


Taking a look at the error redirect in the OLE DB Destination we can clearly see that Truncation is greyed out and no option is provided so I have to assume that it simply isn’t an option to configure it here.


I used to have a link to an article which mentions that truncation cannot be deemed an error in a bulk import operation via SSIS due to the mechanics of how it all works but for the life of me I cannot find it :(. I am hoping someone who reads this will be able to provide me with this but for now I will have to draw my own conclusions from this. The closest thing I can find is an answer from Koen Verbeeck (b|t) in an msdn forum question where he states:

The only thing you get is a warning when designing the package.

You get truncation errors when you try to put data longer than the column width in the data flow buffer, i.e. at the source or at transformations, but not at the destination apparently.

What I still don’t understand is why in tSQL you will get an error when trying to “bulk insert” (loose sense of the term……ie. using an INSERT….SELECT) data that will truncate data but SSIS does not. Hopefully someone far cleverer than me will be able to shed some light on this!

The idea behind this blog post was not to focus too much on the importance of testing any data that is moved from one place to another but I wanted to highlight how easy it is to believe that what you are migrating is all fine n dandy because the SSIS package told you so but in actual fact you could be losing some very very important data!!

You have been warned 😉


Windows 10 Upgrade on Surface Pro 3 – Sending email issue FIXED — August 2, 2015

Windows 10 Upgrade on Surface Pro 3 – Sending email issue FIXED

I very rarely blogabout anything other than SQL Server but felt that with a lot of the SQL Community using SP3’s that this may actually be helpful to some.

After playing around in a VM for a while now I decided to upgrade my SP3 to Windows 10 which comes as a free upgrade. I was surprised that after making the schoolboy error of believing my charger was actually charging the unit during the upgrade and the battery running flat 30% through the upgrade, it fired back up and started off from that point with no issues whatsoever! I honestly thought that was it and it would be a factory reset.

I won’t go on about what’s changed, what I like and what I don’t like, you can read plenty of that around on the internet and it is out of the scope of this blog post.

However, I did discover one issue which I know several others have had when upgrading their SP3. After the upgrade I could no longer send emails via (IMAP) outlook. I could send via every other device so knew it was specific to the upgrade. I managed to find a couple of others on the windows forums with the same issue and they provided the solution which I thought I would share.

The fix is actually very simple, all you need to do is open a command prompt, run sfc /scannow and wait ~10mins.

The System File Checker will scan your Windows system files for corruption and attempt to repair them, below is the output from my run:


I won’t go into details of the log file but the repaired file was related to the “Multilingual User Interface” (mui) files, which are translation files used to support different languages within windows. Mine in particular was mlang.dll.mui.

So, after a mild panic the fix was relatively straight forward……once I knew what the problem actually was!

SQLNorthEast Usergroup 2015 dates announced (preliminary) — January 13, 2015

SQLNorthEast Usergroup 2015 dates announced (preliminary)

Mike and I have been extremely busy over the Xmas period and we’ve finally sorted dates for our 2015 instalment of the SQLNorthEast SQL Server UserGroup (@SQLNE) in Newcastle. The great news is that after much negotiation we have managed to get agreement in principal to use the same venue for our events which is fantastic news!

Please see www.sqlne.com for info on our next meeting and registration. Due to sqlpass website restrictions we cannot display all the dates for 2015 but a quick search on Eventbrite will give you the relevant details.

The dates are as follows:

Feb: Tue 10th (with Chris Adkin – double session)
March: Tue 24th (with Erin Stellato from SQLSkills and Peter Shaw)
April: Tue 28th (with Neil Hambly)
June: Tue 2nd (with Steve Powell)
July: Tue 7th (with Annette Allen)
Sept: Tue 8th (TBC)
Nov: Tue 24th (TBC)

As you can see we have already lined up a number of fantastic speakers including a special remote session from the world class sqlskills Principal Consultant Erin Stellato!

2014 was a great year for us and due to the success of our second SQLRelay we’re going to be sending out a survey to find a bit more information about the needs and wants of our delegates to help us set our content for 2015. We have a very mixed bag of experience as well as SQL Server areas so any help or ideas we can get is a great help. If you wish to complete this now then you can find it at SurveyMonkey.

If you have any thoughts or ideas regarding how we can improve then please get in touch via email or twitter and we’ll endeavour to incorporate it.

Merge csv files – quick PowerShell snippet — July 14, 2014

Merge csv files – quick PowerShell snippet

csvDuring a bit of work I’ve been doing this evening for SQLRelay, I used something I have in my arsenal of PowerShell scripts which I thought I’d share because I love it’s simplicity. It’s nothing big and fancy but something that is extremely useful. Tasked with merging a large number of csv files there (as always) is a quick and easy way to do this with PowerShell:

Get-ChildItem *.csv | ForEach-Object {Import-Csv $_} |

Export-Csv -NoTypeInformation WhateverYouWantToCallTheFile.csv


There are ways to make this a little more dynamic which I will update the post with in the coming weeks….



2013 in review — December 31, 2013
Output SQL Server data from multiple tables to Tab Delimited text files using Powershell — December 20, 2013

Output SQL Server data from multiple tables to Tab Delimited text files using Powershell

tabI had a request this morning for something I though was actually very simple:

Client: “Can you extract all data for these particular tables including column headers to a tab delimited .txt file?”
Chris: “Sure, no problem, I’ll just run bcp querying sys.tables using a COALESCE loop to output the statements”
Client: “Top stuff, let me know when it’s done”

So, away I went generating my script which took a matter of minutes and run it…….where’s the column headers? Bugger, forgot that bcp doesn’t output column headers without doing some funky stuff by creating a header record in a separate file and merging that with the file of data.

With this in mind I knew creating a SSIS package (or using export data to generate – very manual unless I delved into the realms of BIML) could do this but I thought I’d have a look at powershell invoking sqlcmd.

Again, this all seemed to be going very well until I came to outputting the data to a tab delimited .txt file. As far as I’m aware Powershell does not have an Export-Txt so I had to look into how I can use the Export-Csv to actualy output to .txt tab delimited as opposed to comma separated and found the parameter -delimiter “`t” – Excellent!!! Added this in and run the script…………and the first row consisted of “#TYPE System.Data.DataRow” – wft!?!?!?!?!

Quick search on my search engine of choice showed that there is a parameter that you can pass in to remove this from the export -NoTypeInformation.

Run it again with -NoTypeInformation and everything worked as expected apart from all column headers and data had quotes (“) around them which was not part of the requirement. Unfortunately (as far as I know) there is no switch, parameter or the likes that does this so I had to change the Export-Csv to ConvertTo-Csv and run a Replace on ‘”‘ with ” which managed to do the trick.

I’ve included the script below which can be tailored to your needs:

$server = 'ServerInstanceHere'
$database = 'DBNameHere'
$path = 'c:\work\ToDelete\'
$query = "SELECT name FROM sys.tables WHERE name in (
 --etc etc
$queryToOut = "SELECT * FROM $TableName"

#Get list of table names to output data
$Tables = invoke-sqlcmd -query $query -database $database -serverinstance $server
foreach ($Table in $Tables)
 $TableName = $Table["name"]
 write-host -ForegroundColor Green "Creating File $TableName.txt"
 invoke-sqlcmd -query $queryToOut -database $database -serverinstance $server | `
 #Convert as opposed to Export to replace quotes if required
 ConvertTo-Csv -NoTypeInformation -delimiter "`t" | `
 ForEach-Object {$_ -Replace('"','')} | `
 Out-file $path$TableName.txt
 #Export-Csv -NoTypeInformation -delimiter "`t" -path $path$TableName.txt

Apologies for the formatting but the powershell script tag doesn’t seem to format it the way I’m wanting it to so here is a screen shot of the code:


SQL Server NorthEast – New Usergroup!! — November 19, 2013

SQL Server NorthEast – New Usergroup!!

SQLBits in Nottingham was where it all began. A short conversation on whether there were any plans for a SQL usergroup in Newcastle with Richard Douglas (@SQLRich) and whether there was scope for me to begin setting one up quickly moved onto conversations with Chris Testa-O’Neill (@ctesta_oneill ) and eventually Jonathan Allen (@fatherjack). This became quite a lengthy chat regarding the ins, outs, ups, downs of setting up such a thing.

This was back in May. Four months of venue hunting, speaker negotiations and marketing led to the first ever SQL Usergroup in Newcastle – #sqlnortheast 🙂

The schedule was set up for Gavin Campbell and Neil Hambly to make their merry way up to the north east and give two fantastic sessions. So with venue sorted, speakers sorted, food sorted, attendees sorted, we were all set. Boooooom! Then the bombshell hits, a few days before the UG Neil anounces he can’t make it 😦

With the first SQL UG in the north east hanging in the balance, up steps a very good friend of mine Chris McGowan (@ckwmcgowan), who was willing to make the trip from Manchester at such short notice and save the day! With 18 people regstered, this was about 17 more than I was expecting. With no initial indication as to the level of interest we could generate in the Northeast I was over the moon with the uptake. The integration between the group was phenominal and there was such a broad range of knowledge and skills ranging from hardcore sql internals DBA types to developers to Azure – made for great conversations.

So, September 3rd came along and surprisingly all seemed to be going well. Both speakers turned up on time, food turned up and most importantly 15 people turned up on the evening which was gobsmacking. All in all it turned out to be an extremely good evening / night. Few beers with everyone afterwards on the Quayside led to far too many beers with Chris and Gavin back at the hotel bar – wasn’t a pretty sight the next morning!

What an experience and from the excellent all round feedback received from the attendees, this is something that they hope will continue.

Michael Robson (@heymiky) and myself are currently trying to work out dates for next year and organise speakers. We’ve had a bit of a break from the UG due to taking on a leg of sqlrelay in Newcastle (November 25th) but we do have a “SQL on the Lash” evening session set up for December to end the year on a high.

I’ll be reporting back with the how it all goes with sqlrelay and with any further anouncements on dates for sqlnortheast UG in 2014.

Few pics from the session:

photo (4)

photo (5)

photo (6)

Making sure your Triggers fire when they should — March 4, 2013

Making sure your Triggers fire when they should

As some of you may be aware, triggers are not my favourite thing in the world but like most things, it does have its place.

Whilst onsite with one of my clients, one of the processes fires a trigger on insert which ultimately runs a SSRS subscription to email a report. All sounding fairly feasible so far. However, this process is also used as part of a batch process overnight which would run a separate insert statement (actually another stored procedure in another job step) instead of the “onDemand” insert. Ok, still doesn’t sound like too much of an issue.

Now, they started experiencing occasional failures of this job during the day with the error relating to the fact that the SSRS subscription job was being called when it already was running. Interesting, this in theory shouldn’t ever happen because the process either ran the jobs based on the batch process or the one off onDemand.

Stepping through the process, it led me to an AFTER INSERT trigger. Upon opening it I spotted the issue straight away. Something that as I’ve found over the years as a consultant, a lot of DBA’s and developers have failed to understand that (from MSDN ):

These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected.This is by design.

So, the issue was that step 3 ran a procedure which ultimately ran an insert statement for the onDemand insert, step 4 ran a procedure to insert for the overnight batch process which as it happens doesn’t have any records to insert but will in fact fire the trigger to run the SSRS subscription again! There is a number of ways to fix this but I’ve tended to stick with a basic check of the “inserted” table for results and RETURN out if no records are there to process.

I’ve supplied a bit of test code below for people to try this out.

Lets create a test table and an audit table:

USE tempdb

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestTable]') AND type in (N'U'))
DROP TABLE [dbo].[TestTable]
CREATE TABLE [dbo].[TestTable]
	TestTableID INT IDENTITY(1,1),
	TestTableDescr VARCHAR(20)

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AuditTrigger]') AND type in (N'U'))
DROP TABLE [dbo].[AuditTrigger]
CREATE TABLE [dbo].[AuditTrigger]
	AuditTriggerID INT IDENTITY(1,1),
	AuditTriggerDescr VARCHAR(20),
	DateCreated DATETIME

INSERT INTO dbo.TestTable (TestTableDescr)
VALUES ('Test1'), ('Test2'), ('Test3');

SELECT * FROM dbo.TestTable;

Now lets create the trigger with no checking:

USE [TempDB]

IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[trTestTable]'))
DROP TRIGGER [dbo].[trTestTable]

CREATE TRIGGER [dbo].[trTestTable] ON [dbo].[TestTable]

	--Log the fact the trigger fired
	INSERT INTO [dbo].[AuditTrigger] (AuditTriggerDescr, DateCreated)
	SELECT 'Trigger Fired', GETDATE()


Test Inserting a record that exists:

--Valid Insert
INSERT INTO dbo.TestTable (TestTableDescr)
SELECT TestTableDescr
FROM dbo.TestTable
WHERE TestTableDescr = 'Test1';

FROM    [dbo].[AuditTrigger];

Test Inserting a record that doesn’t exist:

--Not a Valid Insert
INSERT INTO dbo.TestTable (TestTableDescr)
SELECT TestTableDescr
FROM dbo.TestTable
WHERE TestTableDescr = 'Test4';

FROM    [dbo].[AuditTrigger];

You’ll now see that there are 2 entries in the AuditTrigger table due to the fact that the trigger fired even though no records were actually valid to insert.

So, lets amend the trigger to check for valid inserts:

USE [TempDB]

IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[trTestTable]'))
DROP TRIGGER [dbo].[trTestTable]

CREATE TRIGGER [dbo].[trTestTable] ON [dbo].[TestTable]

	--Check to see if any records were inserted

	--Log the fact the trigger fired
	INSERT INTO [dbo].[AuditTrigger] (AuditTriggerDescr, DateCreated)
	SELECT 'Trigger Fired', GETDATE()


and test the inserts again:

Test Inserting a record that exists:

--Valid Insert
INSERT INTO dbo.TestTable (TestTableDescr)
SELECT TestTableDescr
FROM dbo.TestTable
WHERE TestTableDescr = 'Test2';

FROM    [dbo].[AuditTrigger];

Test Inserting a record that doesn’t exist

--Not a Valid Insert
INSERT INTO dbo.TestTable (TestTableDescr)
SELECT TestTableDescr
FROM dbo.TestTable
WHERE TestTableDescr = 'Test4';

FROM    [dbo].[AuditTrigger];

No record will have been inserted with the final insert statement!

Lets clean up our tempdb:

USE [TempDB]

--Clean up
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestTable]') AND type in (N'U'))
DROP TABLE [dbo].[TestTable]
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AuditTrigger]') AND type in (N'U'))
DROP TABLE [dbo].[AuditTrigger]

Hopefully this will help point out the misconception that triggers only fire when records are actually inserted 🙂

As per usual, I’d like to hear peoples thoughts/experiences on this topic.

DBCC CheckTable, Spatial Indexes and incorrect compatibility mode….. — November 19, 2012

DBCC CheckTable, Spatial Indexes and incorrect compatibility mode…..

Just a very quick blog today regarding an issue that has arisen with one of my clients. During Integration it became apparent that one table in particular was failing during the weekly consistency checks, the error being output:

DBCC results for ‘sys.extended_index_1696529623_384000’.

There are 313423 rows in 1627 pages for object “sys.extended_index_1696529623_384000”.

DBCC results for ‘schema.Table’.

There are 312246 rows in 12192 pages for object “schema.Table”.

Msg 0, Level 11, State 0, Line 0

A severe error occurred on the current command.  The results, if any, should be discarded.

Msg 0, Level 20, State 0, Line 0

A severe error occurred on the current command.  The results, if any, should be discarded.

A bit of background. The server is running SQL Server 2008R2 SP1 CU2 and the database in question is still in compatibility 90 (SQL Server 2005). The table in question has a spatial index on a Geography column.

So, how do we fix this? Well there’s a couple of options.

  1. Change the compatibility to 100
  2. Install SQL Server 2008R2 SP1 CU3…

This is a documented issue (kb 2635827) and the fix can be found on Microsoft’s Support Pages.

FIX: Access violation when you run a DBCC CHECKDB command against a database that contains a table that has a spatial index in SQL Server 2008 or in SQL Server 2008 R2

As to which fix we deploy, well that’s for tomorrow’s fun and games 😉

Developing Microsoft SQL Server 2012 Databases (70-464) – My Thoughts — October 30, 2012

Developing Microsoft SQL Server 2012 Databases (70-464) – My Thoughts

I finally pulled my finger out and took the last exam of the MCSE SQL Server 2012 – Data Platform certification last week and passed with a score of 876 which is pretty respectable in my opinion 😉

The exam consisted of 3 sections:

  • 1 – 6 Scenario based questions
  • 2 – 7 Scenario based questions
  • 3 – 32 Generic questions
  • So those of you who are clever enough to work out that’s a grand total of 45 questions. I’m pleased to say that once again the quality of the questioning was to Microsoft’s usual standard, not in terms of difficulty but more in terms of complete irrelevance!

    I’m bound by NDA so can’t go into specifics but I’ll try and provide and example of this complete and utter irrelevance without giving the game away:

    Q: What is your favourite holiday destination?

    A (Select one of the following):

  • Green
  • Green with a bit of Yellow
  • Green with a bit of Blue
  • Sky Blue with Pink dots
  • Hmmmm, bit of a tricky one here. From what I can tell, none of the answers bear any relevance whatsoever to the question…….hmmmmm……..suppose I’ll have to take a random guess and hope that the answer I select is the one they’ve set as being correct!!!

    It was just a good job i wasn’t on the threshold of pass/fail as this could potentially have been the difference. From the actual question, it was to be a simple answer but I suppose I’ll never know whether or not my random guess worked out or not :(. I just hope the comments I left regarding this issue are taken up and fixed so others don’t have the same issue.

    All in all I felt comfortable throughout the exam but do know for a fact that my knowledge of Assemblies / CLR’s has slipped significantly and I need a refresher.

    So that’s it. SQL Server 2012 MCSE done and dusted and no more exams……well for the next 3 years at least. So whats next? Not sure, there is the MCSM exams but I have to be honest, I’ve got no formal certifications in SQL Server 2005/2008 but I know for a fact that I have far more knowledge and experience of its features than I do for SQL Server 2012 so I suppose I kind of backed up my original issue I have with these exams. Yes I studied and yes I did learn a lot from doing so but in no way shape or form would I class myself as an “Expert” in SQL Server 2012 – I don’t believe anyone could!

    As per usual, any thoughts or comments are welcome.