SSIS Talk

There is a bug where even if you have applied a patch or service pack to SSIS 2008, the version listed next to the “Integration Services” service in Management Studio continues to show 10.0.1600, which is the version number for RTM.

As always, if you are unsure, it is best to determine the actual version by looking at the files themselves, ie. dtexec.exe, or by confirming the version number in BIDS via Help->About.

While this isn’t a big deal, it is an annoyance. I’ve got the question out to the product team for their response, so stay tuned for more information as it becomes available.

EDIT – Please see Matt Masson’s (SSIS Product Team) comments below.

Way too often, we hear many misconceptions about what the Integration Services Service actually does. Some think it is required to execute packages. Others think it is used for checkpoint restarts. Others think it speeds up execution. So, which group is correct?

The answer is none of them. The SSIS Service, quite simply, is responsible for managing the Integration Services interface in SQL Server Management Studio. It enables the ability to import/export packages, view running packages, and view stored packages. It really doesn’t do anything more than that.

Disabling the service will not affect:

• package development (you can develop packages without the service)
• the ability to execute packages (DTEXEC and other executables are responsible for this)
• checkpoint restarts of failed SSIS packages
• the ability for users to query the msdb database for stored packages via SQL
• package execution speed
• SQL Server Agent’s ability to execute packages

The SSIS Service is not cluster-aware, and is in fact not recommended. http://msdn.microsoft.com/en-us/library/ms345193.aspx

EDIT – See Michael Entin’s comment below. The SSIS Service will also cache component/task metadata so that the SSIS runtime engine can poll the cache to see what is installed, which may help speed up package load times however small those gains may be.

“Why does my data flow take so long to execute when I use an OLE DB Command component?
“I need to update values in another table for every row in my data flow.”

These are common questions I see when dealing with “slow” performance of an SSIS package. Sometimes though, the developer of the package does not even understand which component is taking so long to process say, thousands of records in their data flow, so it is understandable why this component may proliferate in many shops. (Sometimes it is needed, of course, but you need to always ask the question – do I need the updates to happen RIGHT NOW?)

If you have a need to update data in another table, while still pushing data from a source to a destination, the OLE DB Command is often used because of the thought that it is part of the data flow, so why not use it.

Read on for more….
(more…)

A collection of 53 Microsoft MVPs (myself included) have wrapped up authoring one of the greatest SQL Server books yet to hit the shelves – SQL Server MVP Deep Dives.

This book brings together the experience from all corners of SQL Server: Design & Architecture, Development, Administration, Performance Tuning & Optimization, and Business Intelligence. Each section contains unique topics ranging from avoiding common query mistakes, running SQL Server on Hyper-V, to SSIS performance tuning.

The book is available to pre-order here: http://www.sqlservermvpdeepdives.com

(EDIT: Save 50% by purchasing the book from the above site by the end of September when you use the code, pop0928)

All author royalties – and this is what really makes this book stand out – are being donated to War Child International, a charity involved in bringing food, health, and happiness to kids across the world involved in war.

Many thanks to the editors for pushing this book through to completion: Paul Nielsen, Kalen Delaney, Greg Low, Adam Machanic, Paul S. Randal, and Kimberly L. Tripp

For more information on the book, continue reading:
(more…)

When using a Merge or Merge Join component in a data flow, your incoming data is required to be sorted. While it may be easy to drop a Sort component into your data flow, it may make more sense to perform the sort in your source query (if you are using a source such as an OLE DB Source component).

If you decide to use an ORDER BY in your source and want to tell the SSIS Data Flow that your data is sorted, follow the below steps:

(more…)

There are many, many resources out on the ‘Net regarding SSIS and the Lookup component and what each of its cache modes are and how to implement them in your own package. This is going to be a technical post, for those of you interested in what each cache mode does behind the scenes.

(more…)

Matt Masson of the SSIS team has announced publicly that there are no new features for SSIS being released in SQL Server 2008 R2. Think that’s bad news? Think again – the team is hard at work on the next release (the term I’ll use for now) and based on some discussions I’ve been involved in, it sounds promising. SSIS is only going to get better and more competitive, so stay tuned!

There seems to be some misinformation going around about how SQL Server works with ordered data, especially with how the data is *physically* stored, even with a clustered-indexed table.

Please see this post for more details on SQL Server ordering: http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx

To add to that list a bit:

• Updates are (almost) the same as select queries, internally, in that updates are turned into selects internally, which means the ordering rules of selects are also at play
• Updates can have parallelism, meaning that updates do not have to preserve update order
• Update order can’t be guaranteed because clustered indexes can be rebuilt with a different order
• Ordering generally happens at the operator level within a query plan; not at the top
• Unless you’ve seen it in BOL or in a MS Blog post, don’t assume anything with respect to SQL Server ordering – even if your queries operate on a given order today!

The PASS team has assembled a unique opportunity for those wishing to expand their SQL Server knowledge, be it in database administration, business intelligence (including, of course, SSIS), blogging, etc…, can attend one or more sessions (out of a total of 24) for free by registering here: http://24hours.sqlpass.org/

No matter where in the world you are, there should be a session available that will appeal to your knowledge craving noggin, so don’t hesitate, sign up today.

The session is officially on Sept 2nd, 2009, but depending on your time zone, it may be Sept 1st or 3rd, so please be sure to check out their time zone information.

It would seem that the DATAllegro acquisition is complete as Microsoft has shipped its first technology preview of the project code-named “Madison” to ten customers for their feedback.

Madison looks to be quite the offering, allowing for rapid growth of a data warehouse architecture because of its “appliance” model as well as integration with existing SQL 2008 instances, minimizing time-to-market. Customers have reported loading over a terabyte of data within an hour using Madison and over 1.5 terabytes of query processing per minute.

When you have a chance, stroll on over to the Madison Web page to read more about it: http://www.microsoft.com/sqlserver/2008/en/us/madison.aspx