SSIS Sample Data Flow Transformations

Every few months I stumble across this old blog post by Euan Garden[1] that links to a bunch of sample transformation components that the SSIS team put out in the 2005 release timeframe. Check it out: http://blogs.msdn.com/euanga/archive/2006/02/14/IsTransforms.aspx

Now obviously this isn't new news, but I can never find it when I'm looking for it, so hopefully it will help me and you the next time one of us is looking for sample transformations...

 

[1] Always when I'm looking for something else entirely.

New SSIS Article Online on MSDN

I have a new article on the SSIS Developer Center on MSDN, focusing on Data Sources and Configurations as tools for connection reuse across sets of SSIS packages. I wrote it months ago (looking at it now it seems even longer[1]) but it just made it online today.

Check it out here: http://msdn.microsoft.com/en-us/library/cc671619.aspx

This is actually one of a set of articles by SSIS-focused SQL Server MVPs that will be published over the next week of so - I just could not wait to let people know that it was out there. I'll post links to all of the articles once they're all online (and I probably won't be the only one to do so) but for now you can get started with this one. Enjoy!

[1] For example, the "about the author" blurb at the bottom lists me as still working at Configuresoft, even though I have not been working with Configuresoft since the end of July.

Who Runs The Cloud?

Cloud Computing is a very popular phrase these days, and it's been creeping its way into SQL Server circles with the introduction of SQL Server Data Services and other service offerings from Microsoft and other companies. It seems to be what many IT managers and some IT professionals want to talk about.

But it's always struck me as being a little odd. After all, I've never met either:

A) Software that runs on water vapor and ice particles suspended in air,

or

B) A cloud that's made up of high-end computer hardware.

Yes, I know - that's not really fair. Everyone knows that the "cloud" in Cloud Computing refers to the Internet, which is usually drawn as a big fluffy cloud on whiteboards because it's too complex and dynamic in structure to be diagrammed accurately at more detailed levels of abstraction. But still, it seems to me that too many people fail to look deeper, to force the "cloud" abstraction to leak, as it were.

Until it leaks on them.

Obviously, having your software run "in the cloud" means that it's running in a well-connected data center, probably one that's managed by a 3rd party. But the cloud is still made up of hardware and software, and regardless of who owns and operates it, it still needs to be managed by someone. And what happens when that fails?

Well, bad things, right? I came across this article on The Register[1] today:

http://www.theregister.co.uk/2008/08/28/flexiscale_outage/

Ouch.[2]

What's the moral of the story? I'm not going to claim that I know, but I'm sure that it is something related to "good for the goose, good for the gander" or the like. The same bad things that can happen to your own data center can happen to others' data centers as well, even when your applications and services are running there.

So is it time to look at your SLA again?

[1] My favorite source for bitter and sarcastic IT news

[2] Ouch and a half.

Buy This Book, or Your Database Gets It!

At last night's VDUNY meeting I talked about a whole bunch of new features in SQL Server 2008 for database developers. And because this is a favorite topic of mine[1] a lot of the discussion revolved around Transact-SQL.

And you can't really talk about Transact-SQL without the name Itzik Ben-Gan coming up at least once or twice. Itzik is arguably the person who knows more about using T-SQL than anyone else on the planet, including the people who develop the language at Microsoft.[2] And of course, you can't mention Itzik at a user group without recommending this book:

Inside Microsoft SQL Server 2005: T-SQL Querying

If you develop applications using SQL Server - any version, not just SQL Server 2005 - then you absolutely must have this book in your library. It doesn't matter if you are brand new to SQL Server or (like me) you have been a SQL Server consultant and trainer for over ten years - you will find information in this book that you never knew you didn't know, and you'll find techniques that will make you a better SQL developer every day.

Yeah, it's that good.

Just to make sure no one thinks that Itzik is here behind me with a gun, this is a purely unsolicited recommendation. I'd just gotten a follow-up email from an attendee, which made me remember the presentation, which made me remember the discussion, which made me remember just how darned good this book is. Buy it and see.

[1] And because they were foolish enough to say "talk about whatever you'd like." ;-)

[2] Yes, this is a powerful statement, but I feel comfortable making it because A) I did qualify it with the word "arguably" and B) I've heard enough people on the SQL Server team say the same thing without using the word "arguably."

Working Around AT&T's "Help"

I own an AT&T Tilt smartphone. I use it for all sorts of things, including voice calls, web browsing and email, and it's very rare that I leave home without it. But one thing that I have not used it for before today was connecting to the Internet from my laptop. Earlier this month I upgraded my AT&T plan to include "tethering" to allow me to do just this. I spent half an hour or so on the phone with a very helpful young lady at AT&T tech support who walked me through what I would need to do to set up and use tethering, including this and this. Life was good.

Except for the fact that it didn't work.

Following AT&T's "help" allowed me to connect my laptop to my phone, but not actually to the Internet. And today, since I'm preparing to be at the Rochester Microsoft offices for tonight's WDUNY meeting, I figured the time was right to figure out what was going on. I was getting ready to call AT&T support again to see what they had to say when I realized that this might not be the best approach. So instead I fired up my favorite search engine and found this and this. And by following the instructions on the second page, I was up and running - connected to the Internet from my laptop via my phone, and connected to my VPN from there, and talking on the phone at the same time - within 10 minutes.

Thank goodness for the Internet. But why did it need to be this hard? Why is the "official" solution obtuse and backward and (to top it all off) non-functional?

I don't have an answer, but with a little luck the next time someone is trying to tether his Tilt, he'll have one more resource to help him get going. And hopefully this solution will keep working, since I'm going to be on the road a lot over the next three or four weeks...

VDUNY Meeting Tonight

Just as a quick reminder, I will be presenting on new features in SQL Server 2008 at the Visual Developers of Upstate New York user group tonight at 6:00 PM in Rochester, New York. If you're in the Rochester area, be sure to attend.

And remember - as an added bonus I will be giving away a set of post-conference DVDs from this year's TechEd conference. This is a set of nine DVDs with all of the breakout sessions and keynotes from both the TechEd Developers and TechEd IT Professionals conferences, with a retail value of $195. It could be yours!

One More Reason to Attend

I've posted a few times already[1] about the SSWUG Business Intelligence vConference that I have been helping to organize. Well, the conference is now less than a month away, and there is more news to share:

We're giving away a copy of Microsoft Visual Studio Team System 2008 Team Suite with MSDN Premium to one lucky attendee.

That's right - the big one. This is the ultimate version of Microsoft's MSDN subscription, with a suggested retail price of $10,939. If you're a software developer or BI professional, this package has everything that you need to develop for the Microsoft platform, and then some.

If you'd like a chance to win this MSDN subscription, just register for the SSWUG Business Intelligence vConference. For just $100 you get:

• 30 amazing technical sessions from 10 amazing speakers.
• The ability to watch the sessions either during their multiple scheduled timeslots, or on demand for up to 14 days after the conference is over.
• Online chat and Q&A with the speakers during the scheduled sessions.
• Downloadable slides, transcripts and demo files so you can continue the learning once the session is done.
• And here's the one that blows my mind - a $150 discount on the Fall SQL Connections conference in Las Vegas. So if you're planning to attend the SQL Connections conference, the SSWUG Business Intelligence vConference more than pays for itself.

So what are you waiting for? This vConference is going to be amazing, and we'd love to see you there!

[1] For example:

• BI for the Virtual Guy
• Check Out This Lineup!
• And also a mention in What Happens in BIDS, Stays in BIDS

SQL Server Sample Install Epiphany

(Warning - this post has turned into a long drawn-out rant. If you want to skip to just the useful stuff, scroll down to the third and final bulleted list way down there at the bottom. I won't mind, I promise.)

One "new feature" of SQL Server 2008 that has always seemed of dubious value (at best) to me is the way that the product samples have been removed from the actual SQL Server installer. If my memory serves me correctly[1] the history of SQL Server samples (namely the sample databases) has gone something like this:

• SQL Server 2000 and earlier: Sample databases automatically installed with the RDBMS. Users must manually delete them post-install if they're not wanted.
• SQL Server 2005: Sample databases part of RDBMS install, but are not installed by default - they must be manually selected by the user.
• SQL Server 2008: Sample databases not included with the RDBMS installer at all. Users must wade through dozens of poorly-documented downloads on the CodePlex web site, hope they get the installers that include the databases that they need, install them, then struggle to find out what files the installers put where.

Ok, so perhaps that last bullet isn't particularly fair[2] but it does sum up my personal experiences with getting samples working with SQL Server 2008. If you go to the Releases page for the SQL Server 2008 samples project on CodePlex you'll see 28 (twenty eight!!) different MSI installers that you can download. And when you install any one of them, it's pretty much a mystery what files are installed and where you can find them. In my book this is quite a big step backward.

To be completely open, I realize and admit that I'm not the typical SQL Server user. I do a lot of training, presenting and writing on SQL Server topics, and the samples are a big part of these activities - because without them I'd have to build samples of my own. And of course once the SQL Server samples are installed, they're great - it's hard to find anything bad to say about the content itself.

Anyway, today I've been spending some time preparing for a few presentations that I have on my schedule in the next month or so, and have needed to go back out to the CodePlex web site to download (again) the SQL Server 2008 samples. When I was faced (again) with the 28 (twenty eight!) different installers, I groaned and hung my head. "Why?" I moaned, "Why can't they just give us the expletive expletive SQL scripts and source code instead of these accursed MSI files?!?!?"

Oh.

Oh yeah.

Oh yeah, one of the installers is named "SQL2008.AdventureWorks_All_DB_Scripts.x86.msi" - that sounds useful. How could I have missed this?

In fact, I've found that to get to where I need to be, there are really only two things I need from CodePlex:

• That SQL2008.AdventureWorks_All_DB_Scripts.x86.msi file, which you can get here.
• The "All Microsoft Product Samples in a Box" download, which includes "all Microsoft SQL Server product samples (except for the sample databases, due to size constraints) and does NOT include any community projects" and which you can get here.

The nice thing about this second download is that you can choose to download it as a zip file, which means you can extract it to wherever you want to put it. And that, for me, is key.

The DB installer MSI is another matter entirely. When you install it, there is no indication of where it's putting the DB scripts, nor does it give you the option to choose a destination directory. I looked in the C:\Program Files\Microsoft SQL Server\100\Samples folder - that makes sense, right? Wrong. There's nothing there but a license, a readme file which references the C:\Program Files\Microsoft SQL Server\100\Samples folder where the samples aren't located, and a shortcut to the CodePlex project.[3] Ugh.

Instead, the samples are installed in the C:\Program Files\Microsoft SQL Server\100\Tools\Samples folder (note the inclusion of Tools in the folder path) where, if you're like me, you'll never think to look.

Ok, this post has turned into a rant, which was really not my intent. Please let me summarize:

To get the complete samples for SQL Server 2008, perform the following steps:

• Download SQL2008.AdventureWorks_All_DB_Scripts.x86.msi from http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=16040.
• Download SQL2008.All_Product_Samples_Without_DBs.zip from http://www.codeplex.com/SqlServerSamples/Release/ProjectReleases.aspx?ReleaseId=16037.
• Run the MSI installer and then look in the C:\Program Files\Microsoft SQL Server\100\Tools\Samples folder to find the database scripts. Run them as you wish.
• Extract the zip file into the directory of your choice.

Hopefully this will help someone out there avoid the frustration I've felt from time to time when working with the "decoupled" samples...

[1] If you say this in the voice of Chairman Kaga it sounds like a cool pop culture reference, instead of just an admission that I have trouble remembering things that happened before I started typing this blog post - try it out and you'll see!

[2] Especially seeing the huge improvements that the samples owner David Reed has made over the last few months leading up to SQL 2008 RTM.

[3] No, I have not yet filed a Connect item on this readme file. Typing up this rambling blog post took so long that I didn't have time to actually file a useful bug as well...

Cake Wrecks

This has nothing to do with SSIS or SQL Server or technology at all, but I need to post it anyway. My friend Karla just shared this link with me:

http://cakewrecks.blogspot.com

You probably know that I am an avid baker and a general wise-guy[1] but I never thought to bring the two of them together quite like this. The Cake Wrecks blog is dedicated to... let's just call them "unfortunate incidents" in the cake decorating world. Funny pictures, amusing commentary and fun - what's not to like?

[1] And that most people who know me generally spell "guy" with an "a" and a repeating "s"

Waltham Code Camp 10: "Dev InTENsity!"

I just got the announcement today, so I guess I'm behind the times as usual - the 10th Code Camp event is going to be held next month at the Microsoft offices in Waltham, MA on September 20 and 21.

In case you missed Code Camp 9 in April, this is going to be a free two-day technical conference focusing on Microsoft developer technologies. The last time around there were over six hundred attendees who came from as close as Boston and as from as far as hundreds of miles way, and (if my memory serves me correctly) there were six or seven different tracks going on throughout each day. People presented on topics ranging from .NET to Silverlight to SQL Server Integration Services. (Guess who did the SSIS sessions? ;-)

Sadly, my travel schedule will not allow me to be in Waltham to speak at next month's event, but if you are in the northeast US, it's well worth the trip to come.

For more information, check out Chris Bowen's blog. He's one of the event organizers and he has all of the details, whether you want to attend or present, or both.

Visual Developers of Upstate New York

I apologize for the late notice (I've know for weeks, but have kept forgetting to post) but I will be speaking next Wednesday, August 27th, at the VDUNY user group in Rochester, New York. This user group meets on the 4th Wednesday of each month in the Rochester Microsoft offices, and generally has a great turnout of talented software development processionals.

This month I'll be presenting on some of my favorite new developer-centric features in SQL Server 2008. I realize that this is awfully vague, but that is intentional. I'm planning on going in with a loose agenda and lots of demos, and seeing where the session goes.

And to sweeten the pot a little, I will also be giving away a complete set of nine DVDs with all of the content from the TechEd Developers and IT Professionals conferences in Orlando this June. If you're in the Rochester area, you should plan on attending - it will be a lot of fun.

SSIS in Stockholm 2.0

Last autumn I visited the beautiful country of Sweden for the first time. I delivered a two-day SQL Server Integration Services "advanced topics" seminar in Stockholm and presented at the Swedish SQL Server User Group one evening as well.[1] I had a great time and based on their evaluations the seminar attendees did too.

So we're doing it again.

On October 1 and 2, I will be back in Stockholm for another SSIS seminar. The outline looks like this:

• SSIS development best practices
• SSIS deployment best practices
• Extending SSIS packages through custom .NET code using the Script Task and Script Component
• Using open source tools to enhance the SSIS development lifecycle
• Building a custom configuration solution to move beyond the built-in configuration features
• "Elegant solutions for common problems" utilizing SSIS expressions to build real-world packages
• Adding data mining to your SSIS packages
• Performance tuning the SSIS data flow
• New SSIS features in SQL Server 2008
• Lots of opportunities for Q&A and real-world SSIS discussion

It's always a real joy for me to speak on my favorite topic (SSIS) and this seminar is going to be doubly exciting. Not only do I get to return to Stockholm, I also get two full days to present on some of my favorite subjects, do lots of hands-on demos, share some cool code, and above all share my passion and excitement for the SSIS platform with everyone involved.

So if you're going to be in Stockholm at the beginning of October, you should definitely plan on attending this event. And if you're not going to be in Stockholm, you should plan on coming anyway. The city is beautiful this time of year, the food is amazing, and the seminar content will be even better.

[1] I also had some of the best food I've ever eaten, got to explore a truly beautiful city, and meet some really nice people.

SSIS Deployment at TechEd Online

The third and final "Tech*Talk" video interview I recorded during Microsoft's TechEd conference this June in Orlando is now online. I talked with INETA board member Mark Rosenberg about SQL Server Integration Services deployment. You can check it out on the TechEd Online video library site in various formats:

• WMV low quality video
• WMV high quality video
• MP4 high quality video
• MP3 audio only

Enjoy!

Building Packages Programmatically...

...just got easier.

One of the most common SSIS questions that most consistently gets the most consistently frustrating answers is "how do I build a package that can load data from an Excel spreadsheet into a database table when I don't know the layout of the spreadsheet until run time?"

No, the question is never phrased quite this succinctly, but there are dozens of variations that all boil down to this core. The source may be a text file or a database table and not an Excel spreadsheet, but the "I don't know the schema until I run the package" or "the source columns map directly to the destination columns, but I don't know exactly what they are" aspects of the question remain the same.

And the answer remains the same too: "SSIS doesn't do 'dynamic' data flow. You can work around this limitation by using the SSIS .NET API to dynamically build and execute a package, reading in the metadata about your source and destination columns to construct the data flow."

The problem with this answer is threefold:

1. The API for working with the SSIS data flow is a bit complex.
2. The documentation on the SSIS data flow API is a bit sparse.
3. The samples available that demonstrate this technique are a bit nonexistent.

This triumvirate of frustration has been a pet peeve of mine for quite some time. In fact, I've been known to say that such a frustrating answer to such a common question is a major barrier to the adoption of SSIS.

But this week the story just got better. The SSIS team has released additional functionality (see Matt Masson's blog post for more details on the new functionality, including SharePoint List Adapters!) as part of their SSIS Community Samples project on CodePlex. As part of the new release, the samples now include a package generation application that demonstrates how to solve this archetypal problem. Here's the intro blurb from the readme file for this sample:

"This sample demonstrates package generation and execution using the Integration Services object model. The sample can be used to transfer data between a pair of source and destination components at the command line.

The sample supports three types of source and destination components: SQL Server, Excel and flat file. You can choose to create a new destination based on the source component metadata. Alternatively, this sample supports mapping source and existing destination columns by using the same column names or manually, by using the command line.

You can modify the code in this sample to fit your own application."

This may not sound exciting when you read it, but it almost takes my breath away. Digging into this sample is high on my to-do list in the weeks ahead (July and August have been crazy months for me, as the lack of activity on my blog demonstrates) and if you have ever been faced with this "dynamic data flow" conundrum, you should check it out as well.

BIDS Helper 1.3 Release

Just a quick update on this hectic weekend day: The long-awaited 1.3 release of the best and most important add-in for Microsoft BI developers - BIDS Helper - has been released. It has a plethora of new features and supports both SQL Server 2005 and SQL Server 2008.

Download it here: http://www.codeplex.com/bidshelper/Release/ProjectReleases.aspx?ReleaseId=9557

Now.

SQL Server 2008 is Released to Manufacturing!

SQL Server 2008 went RTM this morning and is currently available from the MSDN and TechNet Subscriber download centers. I'm downloading now - are you?

I assume you're not, since I'm getting a sustained download rate of over 2100 KB/sec. Maybe I should wait until my download is complete to hit "Publish." ;-)