It’s the second Tuesday of the month, which means it’s time again for #TSQL2sday! It is the 42^nd installment of this blog post series and the subject is not about the Answer to the Ultimate Question of Life, the Universe, and Everything as I hoped it would be, but about the long and winding road that is our work life and which technologies were key to our interests and successes. In this blog post I’ll talk about how I got where I am now and how SSIS played a major role in it.

About exactly 4 years ago I suddenly found myself out of a job during the financial crisis. I was an IT consultant with no specialization and little experience. Luckily I quickly found a new job through one of my ex-colleagues (always maintain your network people!) at a local BI consulting firm. I didn’t know a thing about Business Intelligence (I had a bit of data mining and data warehousing in a course at university, but I can’t really say it sticked), but fortunately the company was willing to invest in junior people. This is a really amazing thing to do, especially in times of crisis and I’m very grateful for the opportunity they gave me. So the first thing they did was sending me to a couple of Microsoft trainings. I followed one on Analysis Services and I was really amazed by the product. I took another one about Integration Services and I was … not so amazed. I don’t really recall why, but the product just didn’t strike a chord with me.

A few weeks later I was on my first BI project and my first assignment was: make some SSIS packages. I dreaded the task, but trying to make the most of it I dove into SSIS. And after I made my hands dirty with it and got over the initial learning curve, I suddenly fell in love with SSIS! I saw the possibilities the product has to offer and what amazing stuff you can do with it and I lost all the prejudices I had earlier. After some time I really wanted to expand my knowledge on SSIS, so I had the idea of answering questions about SSIS on forums. Because you learn the most about a subject by “teaching” it. By chance I noticed the SQLServerCentral newsletter in one of my colleagues’ inbox, so I thought: “Why not that forum? Let’s try it out.” I went to SSC, registered myself and started answering questions.

Fast forward a few years. I switched jobs in the meantime but still work quite a lot with SSIS. Thanks to my involvement in SSC, I got to learn a lot of interesting people in the SQL Server community. I started giving sessions at conferences about SSIS, writing articles about it, giving trainings about it and as of late, writing blog posts about it. In a way, my passion for SSIS made me what I am today (in a professional way I mean). I still love SSIS and I like very much what Microsoft did with it in SQL Server 2012, but recently I’m realizing writing ETL isn’t the only thing that matters. I’m slowly diving into the front-end of the BI solution, which is very interesting to work with. Right now I’m learning more and more about SharePoint BI – PowerPivot and Power View – and my focus of blog posts is shifting in that direction. My next session I will give at an event will not be about SSIS, but about data visualization. In other words, I might be in yet another bend in the long and winding road that is my career in business intelligence. I’m very curious to what the future will bring…

An error occurred while loading the model for the item or data source 'EntityDataSource'. Verify that the connection information is correct and that you have permissions to access the data source.

Not exactly what you want to encounter when giving a sales demo. So I had two options: either deal with Windows Token Claims / Kerberos or set up an unattended account to run the report. Since I’d rather jab myself in the eye with a rusty nail then dealing with Kerberos, I chose the last option. Setting up an unattended account is my preferred method for authentication, as it gives the least amount of troubles. I do it for Reporting Services reports and PowerPivot workbooks, so I’d also like to do it for Power View as well.

So how do you modify a Power View data source? Luckily it’s pretty straight forward if you know where to look. Go to the library where your Power View report is stored.

In the Library ribbon, set the Current View to All Documents. Click on the down arrow of the report you wish to edit and select Manage Data Sources.

Click on EntityDataSource.

Here we can finally modify the data source. You can choose a shared data source, modify the connection string (a PowerPivot workbook in my case) and set the credentials, which we are interested in. Fill in a user name and password under Stored Credentials to specify an unattended execution account. Don’t forget to check the Use as Windows credentials checkbox.

Note: my demo environment has only one user: the Administrator. This is OK because it is – in fact – a demo environment. Please do not specify administrator credentials in a production environment (especially if you pay attention to the nice red warning on top). Use a dedicated domain account.

Click OK and go back to your SharePoint page. The report now renders successfully! It is as easy as that.

During the installation of SQL Server on this machine, I got the following error:

Error while enabling Windows Feature: Netfx3

A quick search on the Web taught me this is the activation of .NET 3.5. I knew this was a prerequisite for SQL Server. In the past you had to enable this before you started the SQL Server set-up, but nowadays the set-up does this for you. Aren’t we spoiled? Also keep in mind enabling .NET 4.5 isn’t enough: the 3.5 version has to be explicitly enabled.

Why did it crash now and not in all my previous installations of SQL Server? It seemed the SQL Server set-up could locate the sources to install .NET 3.5. Normally it would try to download them, but since the machine was not connected to the Internet this failed as well, resulting in the error.

Luckily it is very easy to enable .NET 3.5 using the GUI. In your Server Manager, click on Add roles and features.

Choose for Role-based or feature-based installation.

Choose the correct server, skip the Server Roles and click Next until you’re on the Features page. Select .NET Framework 3.5 Features and click Next.

On the installation page you’ll be confronted with a warning that the source files are missing. Fortunately you can specify an alternate source path at the bottom.

Click on the link and specify the path to the sources\sxs folder on your Windows Server 2012 media. In my case this was on the D: drive, which is the DVD drive.

Hit OK and on the Installation page, click Install. The .NET framework 3.5 will now be installed.

After the set-up, you can now re-launch the SQL Server set-up!

For the command line geeks amongst us, read the following blog post on how to enable .NET 3.5 using dism.

If you want to run the package in a SQL Server job, you’d need to set the job step to use the 32-bit version by selecting the checkbox Use 32-bit runtime in the Execution options in SQL Server 2008 or later, or by using the 32-bit version of DTEXEC in the command line arguments in SQL Server 2005. (For an overview, check out this excellent blog post by Todd McDermid (blog | twitter): Quick Reference: SSIS in 32- and 64-bits)

But what if you want to run the package in 64-bit mode? Easy, you install the 64-bit ACE OLE DB provider which you can download here. Note that there isn’t a 64-bit version available for the 2007 redistributable and that at the time of writing the 2013 version hasn’t been released at all. With the 64-bit provider installed, you can run your packages on a 64-bit server.

If you don’t install the provider, you get the following error when you try to run such a package on a 64-bit SQL Server:

The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode. … Description: “Class not registered”

However, if Office is installed in the 32-bit version, the installation of the 64-bit ACE OLE DB provider fails with the following message:

You cannot install the 64-bit version of Microsoft Access Database Engine 2010 because you currently have 32-bit Office products installed.

It basically tells us to uninstall Office before you can proceed with the installation. The funny part is that when I try to install the 32-bit version of the ACE OLE DB provider, I’m greeted with the same message:

Apparently a teensy-weensy part of Office was installed in 64-bit, so I can’t install the 32-bit provider either. Normally you wouldn’t have these problems on a production server, as no Office components are installed. But what if you want to test your SQL Server Agent job on your development machine? Or what if you are reading the Excel file using an OPENROWSET command in SSMS? You need that 64-bit provider badly!

Luckily I came across a solution offered by Lowell in this thread: apparently it works when you install the provider through the command line. All you need to do is add the /passive switch and the installation runs without an issue.

Let’s test if this solves our issue. I created a simple SSIS package reading from an Excel file. Nothing too fancy.

When I deploy it to the SSIS catalog and execute it in 64-bit, I see the package has ran successfully:

What’s interesting is that on my new laptop, which has Office 2013 64-bit installed, I could install the 32-bit provider without an issue. So maybe the Office team fixed the issue in their latest release.

Conclusion

This blog post shows a very simple solution on how to solve a very common problem with SSIS and Excel. I’d like to thank Lowell for pointing me out to this solution in the thread I mentioned earlier.

Update: Installing both the providers on the same machine with Office components installed doesn't mean your life is issue-free from now on. Apparently this set-up can cause issues in other places. Check out the comments for more details.

The MCSE certification exists of 5 separate exams, of which the first 3 grant you the MCSA SQL Server certification. These are the following:

• 70-461: Querying Microsoft SQL Server 2012
• 70-462: Administering Microsoft SQL Server 2012 Databases
• 70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012
• 70-466: Implementing Data Models and Reports with Microsoft SQL Server 2012
• 70-467: Designing Business Intelligence Solutions with Microsoft SQL Server 2012

Before I started my certification spree on SQL Server 2012, I already had the three MCTS certifications for SQL Server 2008 (BI, DBA en database developer) and the MCITP: Business Intelligence Developer 2008. So I only had to refresh the older material - especially the DBA stuff – and learn all the new features. Why didn’t I took the upgrade exams? Because I like to challenge myself and I wanted to make sure I learned as much as possible in the progress.

70-463

This is the first exam that I took. It deals mostly with SSIS, but has also some sections about DQS, MDS and dimensional modeling. I took this exam a year ago in beta, so there were no preparation materials available. Luckily I consider myself quite proficient in SSIS, so I didn’t need much preparation. After all, if you are going for one of the MCSE certifications, you should have at least one specialization in the SQL Server stack.

I learned about the new SSIS features (which was Denali CTP1 at the time) by giving a session on the Belgian SQL Server Days in 2011:

Working with the New Project Deployment Model in SSIS for Dummies/Smarties

You learn a lot about something by trying to teach others about it. Of course I also read a lot of blog posts about the subject, of which most of them are mentioned in the video. I gave the same presentation again for the SQLLunchUK user group, but this time through a LiveMeeting.

A few years back I read The Data Warehouse Toolkit by Ralph Kimball. Every serious BI professional should read this book, even if you are an adept Inmon follower. This book gives you all the details you need about dimensional modeling: star schemas, slowly changing dimensions, table grains et cetera. Microsoft relies heavily on the Kimball approach, in the data warehouse design but also in Analysis Services Multidimensional.

Regarding Data Quality Services (DQS), I also mastered the basics by giving a session. This time a webinar for the Belgian 12 Hours of SQL:

An introduction to Data Quality Services (DQS)

Most information about DQS I got from MSDN articles, such as these How-To videos, and from TechEd videos, like this excellent one from Elad Ziklik: Using Knowledge to Cleanse Data with Data Quality Services.

The last item on the list for this exam is Master Data Services (MDS). Since it only takes a small percentage of the questions and I’m not really convinced by the product – to be honest – I didn’t put much investigation to it. I read the free e-book Introducing Microsoft SQL Server 2012 to get me up to speed with all the changes of MDS since the previous version. I also did a tutorial from the SQL Server 2012 training kit, so I at least knew how to handle the product.

The exam itself went pretty well, although I struggled a bit with the Repeated Answer Choices questions, since it was the first time I ever saw those type of questions and took a few seconds before I realized what was going on J

70-461

Every person working closely with SQL Server, be it a DBA, a database developer or a BI developer, should know at least the basics of T-SQL. So this exam shouldn’t be too hard for most people. To prepare myself, I read the book SQL Server 2012 High-Performance T-SQL Using Window Functions by Itzik Ben-Gan to learn more about the windowing functions introduced in SQL Server 2012. I can absolutely recommend this book to everyone to get up to speed with Windowing Functions in SQL Server. Itzik really does a great job by explaining all the concepts very clearly and uses a lot of practical examples. A must-read.

Next I read blog posts and MSDN articles about the new T-SQL functionality, such as TRY_PARSE, THROW and LAG/LEAD. This MSDN page gives a nice overview. Since the exam might contain questions that require you to write code, I refreshed my knowledge on the syntax of the several TSQL statements. Finally I re-read the chapters about T-SQL of the training kit for the 70-433 exam, especially the one about XML. Because you know, I use this everyday so I’m an expert at it (uh-hum. Irony).

I passed the exam on the first try, but I have to admit that the code writing questions are harder than it seems when you have no MSDN pages available to check your syntax.

70-462

This was the exam I feared the most. I’m certainly no DBA and although I already had the MCTS for administrating SQL Server 2008, I had to relearn almost everything again as I didn’t have much real-time practice regarding administration. So I took my time preparing myself for the exam, going over the training kit a few times and watching some TechEd videos on Channel9.

I did all the exercises of the training kit by setting up a virtual environment with a few servers using HyperV and doing all the DBA stuff such as mirroring, replication database, taking back-ups et cetera. The training kit briefly describes how to set this environment up, but it really doesn’t go in too much detail, so you’re pretty much on your own. If you have never heard of differencing disks for virtual machines before – like I did – prepare yourself to do some research. There was especially no information on how to set-up the networking, which appears to be crucial when you went to set-up clustering, as you need to have multiple networks. I briefly go over my set-up in this forum thread. The training kit and the matching practice tests also have some errors, so make sure to check out the Errata page of the book.

The new types of exam questions (check the section Exam formats and question types) also include drag-and-drop questions, so I focused on learning the correct sequence to do something. For example, how to set up mirroring, replication or Availability Groups, how to do a partial restore, how to upgrade a cluster et cetera.

All in all the training kit did a good job preparing me for the exam, as I passed it on the first try, but my score really wasn’t that impressive. But hey, what do you expect from a BI guy J Finishing this exam gave me the MCSA certification.

70-466

After a few months of well deserved “non-studying for exams” I decided to pick up the pace again and to start preparing for the 70-466 exam. I was in between projects at the time, so it was excellent timing to get some studying done.

I started by reading the excellent book Microsoft SQL Server 2012 Analysis Services: The BISM Tabular Model by SSAS Maestros Chris Webb, Alberto Ferrari and Marco Russo, which comes at a whopping 656 pages. It’s a great book and it will teach you everything you need to know about tabular models and DAX, but actually it’s much too detailed for just exam preparation. But hey, a little too much knowledge doesn’t hurt anyone, right? So if you just want to get up to speed with SSAS tabular you can skip a few chapters, such as DAX Advanced for example.

For SSAS multidimensional and Reporting Services, I just re-read the relevant chapters of the 70-448 training kit, giving more attention to features I don’t use a lot, like pro-active caching, MDX (I know, sorry Chris Webb) and administrating SSAS and SSRS servers. I skipped the data mining chapter, as it apparently didn’t make the exam Skills Measured section. I digged deeper into SSAS and SSRS administration by reading relevant blogs and MSDN articles, such as Reporting Services Execution and Trace Logging.

The exam went pretty well on the first try and thus I had only one exam left for the MCSE certification!

70-467

The exam was really interesting to study for. You can compare it a bit to the MCITP – Business Intelligence exam. What makes it so interesting is that everything comes together in this exam. You need to know about SSIS, SSAS (multidimensional and tabular), SSRS (Report Designer, Report Builder, Power View, PowerPivot and PerformancePoint) and SharePoint integration in order to succeed. Everything just comes together.

To prepare myself I went over course 20467A: Designing Business Intelligence Solutions with Microsoft SQL Server 2012, which is a fascinating read. I revisited some chapters about administration for SSRS and SSAS and I dug a bit deeper into SharePoint integration (but let’s face it, I’ll never get that Kerberos authentication thingy).

The exam itself wasn’t that easy, but I managed to get through, so now I’m the proud holder of the MCSE – Business Intelligence certification!

Conclusion

This was my entire preparation. I’m done now with exams for a while J

If you have interesting reading materials or videos that can help other preparing themselves for their exams, please share them in the comments!

Recently, I had to create a demo for showcasing PowerPivot and Power View in Office 2013. As with all demo’s, the hardest part is to figure out which compelling story you will tell. I had downloaded a publicly available Excel file with Olympic data from 1900 until 2008.

This is very useful for a demo, but I wanted to create some maps in Power View, so I also needed a list of countries and their location. And this is where Data Explorer came into the picture. One of the niftiest features of the product is to search online for datasets. In reality a lot of results come from Wikipedia, but we’re not picky.

As you can see in the first screenshot, countries are identified by their NOC code (National Olympic Committee) so the first thing we need to do is find a list of countries and the corresponding NOC codes. To make future matching a bit easier, I also include ISO codes.

With one click on the USE button, the entire dataset is imported into a new Excel sheet:

Next I import similar data sets containing list of countries with their location and also some lists with unemployment rates, population and gross national income. All I need to do some serious analyses on what are the key contributing factors in winning a gold medal at the Olympics.

After I imported everything into PowerPivot, I did some rudimentary data cleansing. The hardest part was to match countries from different lists. For example, is it North Korea or Democratic People's Republic of Korea? Finally I have the following model:

Now I can build some nice Power View reports directly in Excel 2013 and my demo is ready:

The point of this blog post is that it’s fairly easy to create a demo data set using Data Explorer; it took me a bit more than one hour to create this model. However, Data Explorer is more than just an online search tool for data sets, so if you want to learn more about this tool check out the following resources:

Data Explorer hits full preview
Exploring Data Explorer
Access the Windows Azure Marketplace from Data Explorer
Installing Data Explorer Preview & Demo with IMDB Data
Calling A Web Service From Data Explorer, Part 1
Finding Shakespeare’s Favourite Words With Data Explorer

Every time I do something “stupid”, which happens from time to time, I’ll do a little blog post on what happened and how I solved it. The reason for this is twofold: I’ll have a solution online I can consult if it happens again and other people can benefit from my mistakes as well. Because remember the ancient Chinese proverb: “It’s only stupid if you don’t turn it into a learning experience”. Okay, I might have made that last one up…

The problem

Some time ago, I had developed an SSIS package which made a part of a much larger project. The package loaded some reference data needed in the data warehouse to a table. Nothing fancy, just a straight load. I had deployed the package to the development server, tested it there and after everything seemed OK, I deployed the package to the user acceptance environment (UAT).

I needed to run this package so an initial load of the table could be done. I didn’t need to run the entire ETL, just the package, so I logged into the SSIS service and I execute the package. I checked the table and … it was empty. Strange. I checked the log files and I saw the package indeed transferred the correct number of rows. Double strange. As I implement best practices in my SSIS packages, I have indirect package configuration set up. Basically this means I have an environment variable on each machine telling my SSIS packages where the package can find the server and database with the configuration tables. The package uses these configuration tables to configure variables, connection strings et cetera inside the package. So I checked the environment variable and the configurations on the UAT server. Everything was fine. Triple strange. Then I checked the table on the development server. Suddenly, the number of rows had doubled! This means I was updating the development server instead of the UAT server. (Quadruple strange?) So what happened?

The solution

The answer was right under my nose: I had log into the SSIS server of the UAT environment and I executed the package manually by right clicking it and selecting “Run Package”. What I forgot however is that this doesn’t mean the package is executed on the UAT server, but it is indeed executed on my local machine! Indeed, an SSIS package is not executed where it is saved, but on the machine that calls DTEXEC. In other words, if I have server A storing SSIS packages, but I have a SQL Server Agent job executing them on Server B, the SSIS packages are executed on Server B, not Server A. Take this into account when you want a dedicated SSIS server: you’ll need to install the SQL Server engine as well just to have SQL Server Agent.

The embarrassing part is I tell people about this a lot in the forums, but when it happened to me I completely forgot about it. I blame this on the lack of caffeine and sleep

Related articles:

Stupid me #1 – Locking myself out of SQL Server

edit: I forgot to mention it in the article, but it was my colleague Valentino who had a moment of clarity and pointed out my foolishness

Methodology

I created two easy packages, called SimpleTest and AdvancedTest. SimpleTest reads a flat file, adds a timestamp and writes the results to another flat file.

The AdvancedTest package reads the same flat file, but uses a term extraction component to do a term based analysis and it writes the results to a flat file. This package is used to see if there’s any difference when an Enterprise-only  component is used (see Features Supported by the Editions of SQL Server 2008 R2).

The server is a Windows 2008R2 64-bit server. The edition of SQL Server being installed is SQL Server 2008R2 Developer edition.

To test if DTEXEC is installed and if it works correctly, the following commands are used in a command window:

dtexec /F “c:\SimpleTest.dtsx” > SimpleLog.txt

and

dtexec /F “c:\AdvancedTest.dtsx” > AdvancedLog.txt

These commands execute a package located on the file system and output the results to a text file.

Client Tools only

The first install is the client tools, with which I mean BIDS (Business Intelligence Development Studio) and SSMS (SQL Server Management Studio). Later on I learned the question asker meant everything listed under Shared Features, which is a whole lot more than just the client tools. First I installed the Management Tools only.

I took a look in the Program Folders, but I only found a 32-bit DTEXEC. Is this enough to run a package?

When the DTEXEC statements are executed in a command window, we are greeted with the following error message:

Description: To run a SSIS package outside of Business Intelligence Development Studio you must install Standard Edition of Integration Services or higher.

So no luck with this method. When DTEXECUI is launched, everything is grayed out and another straightforward error message tells us we really do have to install Integration Services.

However, the Import/Export wizard is also installed. When I import a flat file to another flat file – the workflow used by the SimpleTest package – the wizard executes successfully:

The only reason DTEXEC was installed is to run the Import/Export wizard. Installing BIDS as well doesn’t solve the issue, as expected. You can run packages within BIDS, but I guess nobody is excited about staying up all night in order to manually kick off SSIS packages. So basically we have installed a lightweight development environment, but not a server.

SQL Server only

Stubborn as I am, I ignore the logical error messages I got earlier and I install the SQL Server database engine, without any shared feature whatsoever. This time the 64-bit DTEXEC is installed. We’re making progress. When I start the SimpleTest package, the log shows the following:

The component "(DER) Add timestamp" (38) cannot run on installed (64-bit) of Integration Services. It requires Standard Edition (64-bit) or higher.

The error doesn’t make much sense, since I haven’t installed Integration Services at all and Developer Edition is a higher edition than Standard (in functionality, not in price). Running the AdvancedTest package results in the following:

Warning: 2013-01-16 14:49:02.56  Code: 0xC0048000
   Source: (DFT) Throw data around (DFT) Throw data around (SSIS.Pipeline)
   Description: The registry key "SOFTWARE\Classes\CLSID\{119D450D-E2A3-4DB0-A7BC-ACDE2536673E}\DTSInfo" cannot be opened.
End Warning
Warning: 2013-01-16 14:49:02.56   Code: 0x8004801E
   Source: (DFT) Throw data around (DFT) Throw data around (SSIS.Pipeline)
   Description: Cannot find the "CurrentVersion" value for component {119D450D-E2A3-4DB0-A7BC-ACDE2536673E}. The CurrentVersion value for the component cannot be located. This error occurs if the component has not set its registry information to contain a CurrentVersion value in the DTSInfo section. This message occurs during component development, or when the component is used in a package, if the component is not registered properly.
End Warning
Error: 2013-01-16 14:49:02.56   Code: 0xC0048020
   Source: (DFT) Throw data around (DFT) Throw data around (SSIS.Pipeline)
   Description: The version of component "(TEX) Extraction FirstName" (77) is not compatible with this version of the DataFlow.
End Error
Error: 2013-01-16 14:49:02.56   Code: 0xC0048020
   Source: (DFT) Throw data around SSIS.Pipeline
   Description: The version of component "(TEX) Extraction FirstName" (77) is not compatible with this version of the DataFlow.
End Error
…
Error: 2013-01-16 14:49:02.56  Code: 0xC0048021
   Source: (DFT) Throw data around (TEX) Extraction FirstName [77]
   Description: The component is missing, not registered, not upgradeable, or missing required interfaces. The contact information for this component is "".
End Error
Error: 2013-01-16 14:49:02.56   Code: 0xC0047017
   Source: (DFT) Throw data around SSIS.Pipeline
   Description: component "(TEX) Extraction FirstName" (77) failed validation and returned error code 0xC0048021.
End Error

First we get a whole bunch of warning related to the registry, followed by errors related to the Term Extraction component, leading to a validation failure at the end. The Enterprise-only component leads to different errors, but the result is the same: we cannot run SSIS packages using DTEXEC.

So why is DTEXEC installed? First of all, to run the 64-bit version of the Import/Export wizard, but also to support maintenance plans, who use SSIS behind the scenes. Since SQL Server 2005 sp2 or SQL Server 2008 CU1/sp1, it is not necessary to install SSIS to create maintenance plans. Read more about this in the blog post Do maintenance plans require SSIS? by Tibor Karaszi (blog | twitter).

Integration Services only

Now let’s try to do the sane thing here and install SSIS, without any other option specified.

DTEXEC is installed, as expected of course, alongside the Integrations Services service. Running DTEXEC through the command line gives us the result we were hoping for:

Success at last! DTEXECUI isn’t installed, indicating this is a client component only. This is OK, because you probably won’t ever use it on a server.

One final question remains: do we need the SSIS service? The answer is short: no. According to Microsoft, the SSIS service only extends the functionality of SSMS, in the sense that it manages the storage of SSIS packages and that it monitors the running packages. So you can safely stop the service. However, you need to disable it as well; otherwise the service is started again once you run a package.

Conclusion

You only need to install Integration Services when you want a dedicated SSIS server, but you’ll need to store your packages on the file system. You can disable the Integration Services service; it is not needed to execute a package. You also might want to install the client connectivity tools as well and any other component you might need to connect to your sources.

A week ago I gave a session for the Belgian SQL Server user group, SQLUG.be, about how MSBUILD can be used to automate SSRS deployments. Thanks to a lovely strike of the taxi drivers in Brussels it took me only 2 hours to get at the location, but hey, the turnout was great, there was beer and pizza and the demo’s didn’t crash.

You can find the demo material from my session here and you can download the slides from SlideShare. My colleague Valentino did a session as well, you can find his material on his blog.

For everyone who attended: thanks for joining us and for being such a great audience!

Whoa whoah. Aren’t you supposed to increase your data flow buffer size in order to speed up your packages? If you have enough memory and you can process more rows at the same time because your buffer is larger, that’s what we want, right? Yes, this is confirmed by the old blog post Adjust buffer size in SSIS data flow task by the SQL Server Performance Team. But this is only true when your source is fast enough to fill those buffers. If you have very large buffers, the remainder of the data flow is just waiting for the slow source to fill a buffer, which is just time going to waste.

Rob Farley (blog | twitter) describes the concept in his excellent blog post The SSIS tuning tip that everyone misses. Basically, it’s about filling your buffers with data as soon as possible, so other data flow tasks can start working on it. Rob achieved his goal by specifying a query hint, but you can do the same by making your buffers smaller. Because, a smaller buffer takes less time to be filled with data and can be passed on the data flow much quicker. Jamie Thomson (blog | twitter) describes the effect in his blog post SSIS: A performance tuning success story. I also encountered a similar story in a forum thread.

I’ll share on of my success stories as well. In my recent Oracle migration, I was transferring a table from Oracle to SQL Server using SSIS. The table wasn’t really large, only about 90,000 rows, but one column contained XML files. These were stored in the Oracle database as a CLOB column (Character Large Object) and in SQL Server as a NVARCHAR(MAX) column. Some of these XML could be quite large, some up to 50MB. When I ran my package using the default settings, 10MB for DefaultBufferSize and 10,000 for DefaultBufferMaxRows, I had to store a long time at a yellow source, without any data being transferred.

After almost 35 minutes, the package finished loading all the rows into SQL Server.

However, when I changed the DefaultBufferMaxRows to 500, the package finished in a mere 11 minutes!

To make sure this incredible speed-up wasn’t the result of any caching on the source, I ran the package again with the default settings:

Possible caching seems to nibble 2 minutes off (or it just might be coincidence), but it isn’t responsible for making the packages run three times as fast.

Why the big difference? I created an Excel graph displaying the size of the CLOB column for the first 20,000 rows, which roughly equals 2 buffers when the default settings are used. I used the function DBMS_LOB.getlength to get the number of characters in a particular XML file in the CLOB column. Assuming every character equals one byte, this is the same as the size in bytes. I’m educated as an engineer, so g = 10, Π = 3 and my CLOB columns contains only singe byte characters and no multi-byte characters If there are multi-byte characters present, the following values in the graph represent the possible minimal size of the XML value. It might be bigger in reality.

We can see that around row 2800 a 20 megabyte CLOB value shows up, followed by several other large XML files. In the second buffer we have even larger XML files, one of 25MB and one of 40MB. Needless to say, it takes a while for SSIS can fill a buffer with this large data. Once a buffer is full, it is passed immediately to the destination. It’s possible the next buffer is very quickly populated if the next rows contain only XML files of a few kilobytes large. But because the destination is still processing the previous large buffer, we get an effect called pipeline backpressure, which is described in detail by Todd McDermid (blog | twitter) in his blog post What is Pipeline Backpressure?. When we combine these two effects – a slow source and an occasional pipeline backpressure – we get a very slow package.

When we use a much smaller buffer – 500 rows in my case – the source can already fill up a few buffers before the first large XML is reached. This keeps the destination busy while the source processes these big rows. Because the larger XML files are uniformly distributed over the table, we can take full advantage of this effect, cutting the total package runtime to one third.

Conclusion

When dealing with a slow source, it might be beneficial to lower the size of the data flow buffer in order to get better performance. Don’t do this all the time! Most of the time the default settings are good enough and if the source is fast you might benefit from bigger buffers. As always, test test test and then put it in production.