Search results matching tags 'SSIS' and 'Katmai'

HDC08 Omaha Demos Posted

ktegels — Fri, 17 Oct 2008 20:17:00 GMT

Slides and demos for my hdc08omaha talks are now available for download from http://tinyurl.com/5oykgj Thanks to all who attended!

The Top Five New Features in SSIS 2008 for Developers, Part 2

ktegels — Tue, 26 Aug 2008 12:25:00 GMT

Lookup. For many SQL Server Integration Services Developers, it's the transformation we most love to dislike - especially if you are using it against data living on remote server not on the same local network as the host running your package. This combination of circumstances sometimes drives us to some very inventive things. In this post I want to look at a situation I ran into a few months ago, how I addressed that situation with SSIS90 and how SSIS100 improves on that.

For the sake of keeping things simple, let us start with a scenario. You have a list of 5,000 email addresses in an XML file. You need do a look-up of those email address and get the person's name and mailing address. You will write that to a CSV file. This is trivial task using SSIS. Where SSIS90 runs into a problem is when the server you are performing the look-up against is on the far-end of a network connection. Following the best practices of SSIS development, supposed your look-up task was based on a SQL statement that specified only the columns of interest. However, what do you do about the rows of interest? In SSIS90, there is not much you can actually do easily.

Remember that unless you use a memory restriction, SSIS90 fully populates the look-up cache with all of the records in the look-up query before processing. That is a double-edge sword: it definitely increases the "wait time" before a data flow task starts processing records. This is especially true if you are pulling data from a remote source with a slow network connection. Yet, the once the data flow starts, the records are processed at blistering speeds.

A straightforward solution to this situation is to use SSIS's ability to serialize buffers to a file - also known as RAW files. In this case, an initial streaming of the lookup data in made into a RAW file. That RAW file is then used as a data source in a second package. The RAW file is "join merged" with the XML source. This effectively provides the same functionality as the look-up did in the previous package. Take care not assume that the RAW file will have all of the needed information - after all, people can move and new email addresses could be added at any time. Therefore, you should when you are designing the package, you should handle unmatched records using the lookup transformation. In addition, you should update the RAW file with the updated data.

In SSIS100, the idea is fundamentally the same, but the tasks are slightly different. The problem being addressed is that there is no obvious way to serialize a look-up cache to a file (aside from the aforementioned technique that may not be at all obvious.) Neither is using a "merge join" as a way of doing a "look-up." The SSIS team has added a new transformation - the Cache Transform -- and modified another - the look-up - to make it more obvious how to serialize the cache.

The Cache Transform component itself is simple enough: within a given Data Flow, you connect this component to a path and it writes the buffers from that path to a new version of the raw-file format know as a Cache-Raw file (or CAW, its file extension.) Like a normal RAW file, the output contains the binary version of the data. It also contains an index covering one or more of the columns. This indexing helps the look-up component efficiently use the cached data. When using this to solve our slow data problem we would run a one-time process to initialize the cache from a data source.

The Look-up component in SSIS100 is expanded to accommodate using the Cache-Raw file. As before, this component can be configured to use an OLE-DB data source. However, it can also be configured to use a Cache-Raw file instead. This can dramatically improve the performance of data flow since it eliminates the start-up delay in acquiring the data. However, there is still the possibility that they cached data may be stale or missing desired matches. Another new feature in the SSIS100 look-up is the ability to direct rows that were not found in the look-up to a new data path. You could "kind of" do this in SSIS90 if you assume that the only row-level error was a failure to find a match. In the new design, rows not matching from the Cache-Raw file could be redirected to another look-up configured to read recently updated data from the remote database.

One thing to keep in mind about the new look-up transform is that it does not seem to maintain the Cache-Raw file automatically. What you should do is re-direct the no-match rows to a traditional look-up. As your complete processing in the data flow, add a second Cache Transform that writes the union of the matched rows and the not-matched but by then looked-up rows. You will need to name that CAW file something other than what you used as source, of course. The File Task can be used to delete the old version of the Cache and rename the new file to the name you used when configuring the first look-up.

Examples of how to build the packages discussed in the post can be downloaded from http://gosqlserver.net/downloads/ttfnfssis100part2.zip .

The Top Five New Features in SSIS 2008 for Developers, Part 1

ktegels — Mon, 18 Aug 2008 20:56:00 GMT

You have to give credit where credit is due, and I certainly have to give credit to Kirk Haselden et al and their book Microsoft SQL Server 2005 Integration Services. That book -- and some of trial and error -- taught me a lot about how to tune data flow tasks for better performance. The folks at Simple Talk have part of the book online and specifically about tuning data flows (http://www.simple-talk.com/sql/sql-server-2005/sql-server-2005-ssis-tuning-the-dataflow-task/). If you aren't all that familiar with the tuning of SSIS 2005 data flows, it's a great piece to read before going at the rest of this article.

I'd love to say that you don't need to worry about data flow tuning in SQL Sever 2008 Integration Services (SSIS100), but I can't. What I can say is you need to focus less on CPU allocation and utilization. As noted in the aforementioned article, in SSIS 2005 CPUs are allocated statically as the data flow task initializes. Basically, this means that if the optimizer for SSIS 2005 determines that it only needs one or two CPUs to execute the data flow, that is all it would ever use even if more CPUs were available to work the data flow. What change for SSIS100 is that CPU are now dynamic scheduled based on amount of "flow pressure" and all available CPUs are utilized. "Slower" tasks can now get more CPU time.

Why did the SSIS team decide to use static scheduling in SSIS90 but dynamic scheduling in SSIS100? The answer is simple: back in 2002 to 2004 when SQL Server 2005 was being planned and developed, multiple CPU hosts were not as common as they are today. When you only have one or two CPUs to work with, static schedule is easier and has the least execution plan generation time and cost. However, now that most production-level hosts have four or more CPUs to work with, dynamic allocation is a more efficient solution. On multiple CPU machines, this change enhances performance considerably in many cases. On the downside, on single CPU machines, performance may actually degrade some, so it is certainly something you want to know about and consider.

The Top Five New Features in SSIS 2008 for Developers, Part 0

ktegels — Thu, 14 Aug 2008 17:17:00 GMT

I am going to get myself in trouble (again) by saying this but SSIS isn’t a Business Intelligence tool as much as it is a developer tool. If you are like me, you have written a lot of code does, basically, the following:

Extracts the data from some place;
Transforms that data somehow;
Loads the transformed data into database or some other store.

Sure, my toolset has changed over the years from COBOL to Perl, Expect and QuickBasic to VBScript to C#, but the basic tasks have not. That is probably why I never warmed up to SQL Server 2000 DTS. The idea of extract, load and transform never really worked for me. I wanted streams, not tables.

So when SSIS debuted with SQL Server 2005, I decided to make the effort to learn it to the best of my abilities. Yes, it is a great tool but like any tool, it does have some shortcomings:

Optimizing data flows is somewhat of a black art;
Using the linear lookup with a remote server is slow... unless you "cheated";
Transact-SQL lacks an UPSERT command (at least prior to 2008);
Calling a Web Service from a script required building an external assembly and making it available to the Script runtime;
I am back to writing scripts in BASIC. Compiled BASIC with the full range of the CLR, sure, but it is still, well BASIC.

Over the next few posts, I want to talk about the new features in SSIS 2008 that help address these shortcomings in SSIS 2005. However, if you saw my recent talk in Omaha, you already know the story. But as of this morning, you can get the bits too! Just browse to http://www.4shared.com/dir/7670149/a1b13c97/Omaha_SQLBI_User_Group.html and, when prompted for a password, enter "SQL4You" (sans quotes, of course). My presentation and bits are in the file labeled "ug_wnissis100.zip"

Sudhir Gajre’s excellent performance tuning for SQL Server 2005 deck is also available from that site.

Come learn more about SQL Server 2008 Integration Services

ktegels — Tue, 22 Jul 2008 12:55:00 GMT

On 6 August 2008, I will be giving a presentation to the Omaha SQL/BI User Group on the new features in SQL Server 2008 Integration Services. We will talk about improvements in scripting, how the redesigned pipeline improves performance and how to leverage the new Cache Transform to improve package performance. Quinn Jones from Farm Credit Services America will also be giving us a great SQL tip. The talk will start around 1800 local time. We will be meeting at the Creighton University West Campus, located at 11111 Mill Valley Road (roughly 41.267786° north, 96.086289° west).

The "3P requirements" for a successful meeting -- Pizza, Pop and Prizes --- will be available as usual.

Please leave a comment if you would like more information.

Call For Action: Spatial "geeks," please speak up!

ktegels — Tue, 08 Jul 2008 15:11:00 GMT

I've had a lot of "fun" working the with new spatial types in SQL Server 2008. Fun like your first root canal sometimes, fun like a great first date other times.

One of the "root canal moments" for me has been around Geographic Markup Language (GML) support. I had spent a good chunk of time generating GML for use in class to subsequently learn that SQL Server's support for GML is "limited." Isaac Kunen was kind enough to point me to http://schemas.microsoft.com/sqlserver/profiles/gml/.

Folks, please learn from my mistake -- understand that schema before you go about generating or consuming GML for the construction of geometry or geography instances.

Speaking of best practices, another frequent pendant on the MSDN Forums/SQL Server Katmai/SQL server Katmai Spatial forum has started a thread (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3586982&SiteID=1) addressing the differences between geometry and geography types.

I think he's off to a great start but I'd like to have the "the rest of us" who are interested in the spatial bits chime in on the tread.I'll even come out and say it. Please.

July is going to be a busy month for me. On week of the 20th, I'll be teaching our Essential SQL Server class in Boston (see http://www.develop.com/us/training/course.aspx?id=180) and then doing a private engagement in the Sacramento area the next week. If you know of any user group/PASS group meetings in those areas around those areas, please let me know.

Upcoming talks on SQL Spatial and SSIS

ktegels — Thu, 05 Jun 2008 12:42:00 GMT

Yesterday I drove down from Sioux Falls to Omaha so that I could catch a talk being given by Sudhir Gajre to the Omaha SQL Server/BI Interest Group. I first got to know Sudhir when he, I and Luke Schollmeyer were restarting a SQL Server Users Group in Omaha. Sudhir is a stud at SQL Server performance tuning and helped write one of the best papers on it (see Microsoft SQL Server 2005 Tuning Tips for PeopleSoft8.x.

I am the next scheduled speaker for that group, so I took a couple of minutes last night to ask them what they would like me to talk about. Response was a little slow, so I suggest that I could give my Spatial Computing with SQL Server talk. Almost no reaction.

Somebody in the group suggest talking about the new MERGE statement. I liked that said, "What if I wrapped that into a talk about "What is new in Integration Services 2008." The room came back to life.

So here's an initial outline of what that talk will probably cover:

The changes in the SSIS pipeline architecture for back-pressure and thread scheduling The scripting environment changes
Working with the Cache transformation
Using T-SQL MERGE with SSIS
Using Change Data Capture with SSIS
(time permitting) Using the Data Profiler task

We clustered about for a while following Sudhir's talk discussing just how much BI is taking off for Microsoft and how useful a talk like this would be. I have to chuckle every time I get into a conversation like this: I my opinion, SSIS isn't a BI tool that developers just happen to be interested in, it's a Developer's tool that just happens to very helpful to the BI specialist. Really understanding and applying many of the changes for SSIS 2008 is made easier, I think, if you have a Developer mindset about it.

I am going to submit the "Whats new in SSIS 2008" for the Heartland Developer's Conferences to be held later in the year in Omaha and Minneapolis too.

If you are a user group leader and would be interested in having me come visit and give a talk, please feel free to contact me through this site.

DTS still alive in 2008?

RickHeiges — Wed, 22 Aug 2007 09:55:00 GMT

I was on a call with several other folks the other day discussing "Katmai". The topic of DTS came up. MSFT had indicated that support for DTS would not go beyond SQL Server 2005 in a variety of channels. I can remember the dozens of Upgrade Advisor messages that always appeared. I just revisited the "Advisory" text. It says that support for DTS will be discontinued at a future date.

Well, it looks like a bunch of folks out there still love those DTS packages. Why do I say this? In the July CTP of SQL Server 2008 BOL, there is a section on DTS. Basically, there is nothing different about DTS support for 2008.

No News is Good News? Yes and No. Yes - that means that many organizationis will not be forced to rewrite DTS packages into SSIS as they move forward. No - that means that many organizations will not take advantage of newer technology.

Also, no SSIS in Express edition in 2008. BOL still recommends using 2000 DTS components here.