SSIS – Unexpected error from external database driver – MS Jet database engine

A recent Windows 2012 OS roll up patch causes the execution of any SSIS package to fail under the following conditions:
1. The package is using Microsoft.Jet.OLEDB.4.0
2. The Package is executed from a SQL job.
3. The OS is Windows 2012 on which the SQL is installed.

Snapshot of the error message:






MS has acknowledged the issue. Below link with details and workaround.

Try and try again – SSMS

Came across this very interesting article which talks about connection resiliency logic in the SQL Server management studio. What this basically does is try to re-connect in case of a time out after sleeping for 10 seconds. The default value for retry is 1 and default interval is 10 seconds. This article from

White Paper on connection resiliency

I hope this article was useful. Have a good day ahead.



End of Days – CodePlex

Microsoft will be shutting down codeplex come December. The official date seems to be the 15th of December 2017. All source code has been moved to GitHub. Codeplex was started in 2006 and was a repository for any developer who wanted to share code with the community.

Link below for more details..


I hope this article was useful. Have a good day ahead.

SQL Server 2016 – End of an Era – So long AdventureWorks..Welcome World Wide Importers

End of an era if I should say so. It all started with SQL Server 2005 with a database named AdventureWorks. A sample database to help learn different aspects of the product. Take a bow AdventureWorks.

Welcome [WideWorldImporters]!!! This is a new database developed by MS targeting SQL server 2016 and Azure. Links below for download. Loads of goodies to download.


I hope this article was useful. Have a good day ahead.

SQL Server – How to compare execution plans

One of the challenges for DBAs and Developers was to compare execution plans effectively. In the latest SSMS released by MS we have an option of comparing execution plans in a better readable format. This short article will explain that.

Step 1: Download the latest SSMS from MS website or If you have installed SQL server 2016 then this feature will be available.

Step 2: Install the downloaded package

Step 3: Execute a query and save the execution plan somewhere.

Step 4: Execute another query and wait till the plan is displayed.

Step 5: Right click on the displayed plan and choose the following option highlighted.

This will ask for the saved execution plan. Choose the saved plan.

Step 6: Click on open
The actions opens 2 windows which shows the difference between the 2 execution plans as depicted below. This is a very handy feature when we need to compare the execution plans for a SP or query pre and post change to gauge the effect in terms of performance.




I hope this article was useful. Have a good day ahead.

SQL Server 2012,2014,2016 – When a CU patch can break an existing functionality!!!

Recently MS released the following patches for SQL Server. The product team has identified that the patches can be potential CDC functionality breaker. The link below for the note from the product team. Worth a read before applying them on production boxes specially if CDC is active.

  • SQL 2012 SP3 CU8
  • SQL 2014 SP1 CU10
  • SQL 2014 SP2 CU4
  • SQL 2016 RTM CU5
  • SQL 2016 SP1 CU2


I hope this article was useful. Have a good day ahead.


How To Flatten Xml Data in SQL Server

In this article we will see how we can flatten a xml data and make it more readable. Let us understand the scenario first and then dive into the solution. To understand the scenario we will create a xml file. Load the data in the file into a table in SQL Server.

Step 1:  Create a xml file and store it in the C drive. Below is the content of the file

<?xml version="1.0" ?>

Step 2 : Save the file as XmlSource.xml on the C drive. You can choose a different location also.

Step 3: Run the following script and create a table which will store the xml data.

IF EXISTS (SELECT 1 FROM sys.objects WHERE name = 'XmlTable')

Step 4: Run the below script to insert the contents of the xml file into the table

INSERT INTO XmlTable(XmlData)
AS ImportSource

Step 5: Check if the data has been inserted correctly or not.



If we observe we will see that the contents of the xmlData row are in xml format. So how do we make it something like below

Here you go. The below query will make it happen

SELECT pref.value('(Name/text())[1]', 'varchar(50)') AS Name ,
pref.value('(Age/text())[1]', 'varchar(50)') AS Age,
pref.value('(Hobbies/Hobby/text())[1]', 'varchar(50)') AS Hobby1,
pref.value('(Hobbies/Hobby/text())[2]', 'varchar(50)') AS Hobby2 
XmlData.nodes('//Person') AS People ( pref )

The quick way to extract the xml data is to use the CROSS APPLY operator which uses a function
against each row and then adds the data to the result set. We then combine this with a function
that can be executed on the XML data type called NODES



%d bloggers like this: