June 2005 - Posts

Data Sources, Data Source Views and Deployment

In an earlier blog I covered some aspects of configurations that I think help make them amenable to deployment and also make them easier to manage for multiple packages and for multiple machines. That blog entry is here.

Another way to make packages easier to move is to use data sources and data source views. For those not familiar with data sources, they are a way to specify a connection that can be shared between packages, machines and even project types, RS, IS and AS etc. If you create connection managers in a package they are specific to the package and cannot be shared. If you modify the connection manager, it will not be reflected in the connection managers found elsewhere in other packages that point to the same server with the same connection string etc. Data sources on the other hand may be shared.

This gives several distinct advantages that should be considered by anyone wishing to make their packages more portable:

1) Modifying one file, one data source, modifies all connections that use that data source. With connection managers, if you don't configure them, you must modify each and every connection manager every time something about the connection string changes. In this way, data sources are very similar to configurations. They're a central location that can configure or modify package behavior without modifying the package directly.

2) Data source views use data sources. Data source views provide a way to cache and reduce the complexity of the metadata for the objects found at the data source. For example, this makes it simpler to focus on the tables that are important for a given solution by eliminating those that aren't necessary or related to the solution. For example, when you open the editor for an OLEDB connection manager created from a data source view, only those tables in the data source view will show up in the tables drop down. Data source views may be updated to reflect changes in the data source and speed up offline use in some cases by providing metadata to work against when the server isn't available.

3) Data sources are machine resident, much like the practice I recommend for configurations. One deploys packages into an environment described by configurations, data sources and data source views.

4) Data sources are the common link between different project types. If you're using the same server for AS and IS, it's possible that you may also only need one data source to represent that connection.

5) Multiple data source views may be created per data source. This allows for even more flexibility by providing different views into one server, you can essentially isolate the parts of a database or databases of interest and only see problem or package specific tables.

Ultimately, data sources provide a level of indirection similar to configurations. Where there is a level of indirection, there is an opportunity for abstracting away the environment which makes it easier to move packages around.

Take a look at data sources anytime you need to create a package that references an OLEDB connection. Chances are, the more you use them, the more you'll like them.

Universe.Earth.Software.Microsoft.SQLServer.IS.KirkHaselden

Project REAL TechNet Webcasts Available

Project REAL is a project the BI Systems Team at Microsoft is doing. It's an effort to build a reference implementation of BI Tools applied to a real world BI problems and gives an example of how things should be done.

This is worth your time if you'd like to see how the Microsoft BI stack can be applied to non-trivial BI implementations.

The following links you to a self expanding executable that contains the compressed webcasts in WMV format.

http://download.microsoft.com/download/d/8/4/d846080e-1109-46d3-a4fa-cce76d8c217d/ProjectREAL_Webcasts.exe

Please take a look,

Universe.Earth.Software.Microsoft.SQLServer.IS.KirkHaselden

 

Doctors, Engineers, mechanics and Software

This is a ramble... Stray thoughts really...

So, I had one of those paradoxical moments when the parallels between disciplines become, in this case, sadly obvious. Since March I've been dealing with some sort of really nasty lung disfunction. The doctors have been calling it bronchitis, but at this point I don't think they know. In March it came on so strong that I could hardly breath. Lately, it just makes me sound like I've been chain smoking 5 packs a day since I was 2. :)

Yesterday I go into the doctor's office for the third or fourth time in as many months to see what this one has to say. Nothing new, really. The venerable doctor starts enumerating the causes of such problems. You either have a cold (post nasal drip), heartburn, bronchitis or ... (And this is where I'm listening for something like lung cancer or emphazeema) ...allergies or asthma. Well, yes, I have all 4 of those. She notices on my record that I have allergic reactions to strong perfumes. Then she looks at me and says, "Are you serious?" This is where my confidence level starts to drop. There are a _lot_ of people with perfume allergies. You'd expect a medical doctor to be aware of that, right? This whole time she's updating her laptop which ostensibly has my medical records. She repeatedly stops typing and starts slapping the screen of the laptop. I mention that, "It really won't help to slap the computer". She says, yeah but, "It keeps locking up". Hmmmm "If you just wait, the laptop will probably catch up." I get the  "You're just a patient, what do you know" look. After some more interesting reparte like this, she disappears behind the door and returns with boxes full of stuff. Antibiotic, inhaler, steroids, and other pills. I need to remember to wash my mouth out after taking some of it "So you won't get a growth in your mouth". Hmmmm, this is really getting interesting.

What's this all got to do with engineers and software? Well, it occurred to me as I was being diagnosed for my bug that there are some parallels between medicine and doctors and computers and engineers and other disciplines. Now, this isn't something new. I've heard the analogy before. You know, the jokes about engineers are like doctors, the only difference is if doctors make mistakes, someone dies or the wrong kidney gets removed. But, sitting in a room with a doctor taking guesses as to what my issue was, brought it home to me with new force. She doesn't have a clue! She knows there's something wrong. There are a lot of symptoms, but none are conclusive. I've had heart burn for years, my cold just started this week and I've had this perfume allergy for years too. Bronchitis is a reasonable diagnosis, but then, why prescribe antibiotics when the poster on the wall right behind her says antibiotics won't help colds, flus or bronchitis. I seriosly doubt they help heartburn or allergies... So, basically, she's dropped a bunch of medicine on me to try. And, I'm supposed to go back and see her in two weeks if things don't get better. In talking to a friend of mine who happens to be an MD, he says his best medicine is time. "The body will heal most illnesses on its own given enough time." he says. "I usually prescribe stuff just so people will wait a little longer to give their body time to heal." he says.

I read somewhere in a coding book early in my career that, Try is the worst word we can use as engineers. Trying different solutions to problems or bugs without understanding the causes first, leads to a long chain of trials and errors. If one of them works, we feel lucky, but we don't know why or if the problem is truly solved. We just know that it went away. We don't even know if what we were doing has anything to do with the problem going away. It could just be happy circumstance for all we know.

The better way is to identify the reason something is happening. This is usually the most difficult problem when debugging code, or apparently, diagnosing sicknesses. Find out exactly why the problem is happening and you're likely 9/10s of the way to fixing it. My dad's a long time electrician and mechanic. A lot of what I know about isolating bugs I learned from him teaching me how to isolate problems with the electrical circuits in my car etc. Disconnect this wire, test that wire if it's hot, isolate and verify... It's funny how these same skills transfer across disciplines. Most MDs would probably take umbrage with the statement that they could learn a few things from mechanics. :)

Bottom line is, there are good doctors and not so good doctors. There are good electricians, mechanics and software engineers and there are some that aren't so good. What's interesting is that many of the skills are so easily interchangeable and how easy it is to spot bad practice even if you are "just a patient". What's worse is, this may be common medical practice. Kind of makes you wonder when the medical community will quit practicing and start performing.

I guess, now that I'm pretty sure it isn't lung cancer, I'll be waiting a little longer for things to clear up. But definitely will be seeing a different doctor. I still haven't decided yet if I'm going to try all the medications she gave to me though.

What do you think?

Universe.Earth.Software.Microsoft.SQLServer.IS.KirkHaselden

DevTeach Day Four

Well just gave two more talks back to back. The first one was about the Dataflow task and some approaches to how to optimize it. The second was a general introduction to Integration Services. Both went well I thought, except for my tricky laptop that seems to do something different everytime I hook it up to a video feed.

I really enjoy giving the "What's New in DTS?" talk. There's a goldmine of things to talk about. There's no shortage of features and most of all, it's really fun to watch people's faces as you describe what this product can do. Folks are constantly looking at each other and whispering things with a surprised look on their faces sometimes. I think it's also a bit overwhelming for people. Especially when I put up the slide that gives line item listings of all the individual features that are new in SSIS. The sheer scale of the differences and the effort put into SSIS is apparent and folks in the classes seem to get that, even if they don't catch all the changes or remember certain details.

It's been a fun conference, now I get to go see some other speakers. I'm looking forward to that.

Universe.Earth.Software.Microsoft.SQLServer.IS.KirkHaselden

DevTeach Day Three

Configurations and Deployment

Today I gave the Configurations and Deployment talk. Very few of the attendees had even installed SSIS. I was struck by the fact that SSIS is still new to most people. There are a lot of folks out there that are still active and consistent DTS users. I tend to forget that, since I've been working on SSIS, talking about it, and writing about it for literally years now.

I talked a lot about what I've been calling two stage configuration and I thought I'd get some of those ideas down here. The tools lend themselves to a simple single stage configuration, that is, configurations that modify properties on tasks, connections etc. directly. The configuration gets deployed along with the package and the user changes the configuration when it's deployed to force the package to work on the destination machine. There are numerous problems with this method of configuring packages namely, configuration files propagate with each package, configuration management becomes a headache and the power of configurations is only being partly utilized. We've been criticized for those limitations and even had a few bugs filed. But, I would recommend that folks take a second look. Configurations are very flexible and may be used in a wide variety of ways. Single stage configuration is useful for certain cases, but for the general case, when you want to make your packages portable and easy to maintain, there is two stage configuration.

Single Stage vs. Two Stage

Two stage or environment configuration is using configurations to describe the environment into which a package is deployed. The way it works is a configuration is created per machine. Any of the configuration types will work, the differences are only mechanical. For example, an XML configuration would be named the same, and for added flexibility for differences between environments, use an indirect configuration. For SQL Server Configurations, you can use a different filter for different machines. Since SQL Server Configurations use connection managers to access the server, the connection manager is configurable. So it's possible to configure the configurations in this way. For example, you could have an environment variable configuration that configures the SQL Server Configurations connection manager to access a different server.

Those machine specific configurations describe the machine in terms of the resources, ie. hard drives, folders, data sources, package locations, drop file paths, and so on. The configuration describes the environment by configuring variables, not properties. That's the first stage. Then the variables may be used inside of property expressions to configure the properties. That's the second stage. Now, each machine in the package development lifecycle has a resident configuration like this. Deploying a package is a simple matter of moving the package. The package will use the machine resident configuration when it is deployed "into" that environment to configure the variables. 

Variations

There will be variations and tuning you can do with this basic method. For example, you may wish to have one configuration for generic machine dependant values and a few different configurations for each of the different classes of package you may have in your organization. Perhaps you have some packages that are used for loading dimensions and they have a set of unique configuration requirements. Then, you can have a configuration that meets those requirements that only those packages reference.

Summary

The point is that there are many ways to use configurations. If you choose to propagate numerous configurations to the point where it becomes a management nightmare, you should rethink your strategy. What I've suggested here is one way to make configurations and package deployment both manageable and simple. Your configuration policy should be factored and designed to reflect your package development lifecycle. Just like tasks, transforms and other components in SSIS, configurations are one piece of a well factored platform that you can use to build solutions. How you choose to use those pieces to build your solutions is the fun part, and it's really up to you.

DevTeach Day Two

Well the speakers got together and took a tour of the town. This is a first for me in Montreal, so it was very interesting. I couldn't help but think that everything looked the same as in Seattle. Old houses next to new ones, overcast and rainy, and lots of people walking around. Montreal is a very pedestrian city.

One of the stops was, well, I can't remember the name of it, but a place up on the hill in the middle of the city where you can look out and see half of Montreal. I managed to get Rushabh, Kevin and Roman together for a shot:

 

Handsome devils, aren't they. Love that goat, Kevin! :0

Later we had dinner at a nice restaurant somewhere in Montreal... Or, so they say, we just followed Jean-Rene around pretty much. There was some sort of festival going on so that even though it was midnight by the time we walked home, it seemed to just be getting interesting. A lot of very attractive scenery in Montreal, I must say.

 

So, that was last night. Good food, good company. Rick Heiges and his wife on the left there and John Kane right behind them. Kevin looking very excited to have his picture taken sitting next to Rushabh. Adam Machanic right behind Rushabh there and Roman is hiding behind the planter.

Thanks, I'll post later about the actual conference...

Universe.Earth.Software.Microsoft.SQLServer.IS.KirkHaselden

DevTeach day One...

Well, 12 hours later I'm finally settled in. We tore the house apart looking for my passport and/or birth certificate to no avail. I decided to go with just the driver's license as "proof of me". Had a scare at the ticketing counter. The American Airlines guy said, "You know they may not let you on the airplane in Chicago, and if they do, they may not let you through customs with just a driver's license. If you are unable to board, it will be at your expense." ... Hmmmm ... Hmmmm ... Well, OK, I said. I'll take my chances. I've been through the border to British Columbia many times with just my license and no problem, but the tone that guy used really got me worrying. Apparently, since 9/11 things aren't so casual. So I called home, "Please look some more for it". My good wife, sweetheart, and eternal partner searched high and low, pulled stuff out of the attic. Still no luck. So she ended up calling the Alabama (where I was born) office of records to have it overnighted to my hotel room.

I get to Chicago, not even a second glance. In fact, they didn't even ask to see ID. Get to customs in Montreal and the customs guy says, "Your passport please?" "Well, I don't have it", "Your birth certificate then?", "Um, I couldn't find it", "Where do you work?", "Microsoft", "Why are you here?", "DevTeach conference in Montreal". "Thank you, have a good day" He says... That's it? That's what I was worrying about?  Sheeesh.

Later, as I finally got through the crouds and the lines leading to the sorte, that's exit for those of you who don't speak french. It's the only french I know. I was looking for it all over the airport. :) I see the customs guy come walking out in his civies. That's civilian clothes for you non-military folk. Then it's all clear to me. He was about to get off work! :) Well, I'll have my birth certificate to get back into the states if I need it. Phew! Thanks Babe!

Got to meet Rushabh and Roman tonight. Went to a nice little restaurant with a great BBQ sauce. Thanks guys, that was enjoyable. It was nice to finally put a face to a name. Roman told us his "How I ended up in the states" story, his defection and other history. It was fun to sit and chat with these guys who are such SQL Server enthusiasts. Their passion for the product is obvious and they're just downright good folk. I'm looking forward to the conference, but I must admit that a lot of the fun is getting to rub shoulders with guys like Roman and Rushabh.

Well, I'm beat. Time to hit the sack.

Universe.Earth.Software.Microsoft.SQLServer.IS.KirkHaselden

Off to DevTeach

I'm off to DevTeach in Montreal this weekend and part of next week. It looks to be a great conference with some very respected speakers and a wealth of content.

I'm giving three presentations:

Configuring and Deploying SSIS Packages
Kirk Haselden - SQL236 – Sunday 19/06/2005 1:30-2:45 Room: Musset
Moving packages around is perhaps one of the most problematic issues users must face when using DTS. Integrations services has introduced infrastructure for simplifying this process. This talk will describe what that infrastructure looks like, how to use the deployment, configuration and other features it provides to simplify package deployment. We’ll also touch on various best practices, tips and tricks.

Data Flow in SSIS
Kirk Haselden - SQL237 – Tuesday 21/06/2005 8:00-9:15 Room: Musset
In this talk I will give an overall architectural review of the Data Flow capabilities of Integration Services. I will also drill down a little on specific features Integration Services provides for data flow. We’ll look at several important adapters and transforms including the flat file adapter, OLEDB adapter, Lookup, Conditional Split and others.

What’s new in DTS
Kirk Haselden - SQL235 – Tuesday 21/06/2005 9:30-10:45 Room: Salon B
Everything has changed and DTS is now called Integration Services. The new name reflects Microsoft’s expanded approach to the ETL and integration space. While covering a few key philosophical changes, the more obvious feature changes and many of the new features, I’ll also cover what hasn’t changed so that experienced DTS users will have a starting point from which to begin their exploration of Integration Services.

I'm looking forward to meeting some of you there. If you get a chance, please stop by and chat.

Thanks,

Universe.Earth.Software.Microsoft.SQLServer.IS.KirkHaselden

 

ForEach From Variable Enumerator... What's it good for?

Someone on an internal alias asked this and I though it might be generally intersting:

Can anyone give me a real world use for the Variable Enumerator? It seems like the Item enumerator is generally more useful. What can I do with a Variable enumerator that I can’t with an Item enumerator and vice-versa?

The FromVariable enumerator is useful in cases when you wish to build a collection at execution time. It's never used by itself as it is really just an indirect enumerator that points to another enumerator or collection to be used. It is an oddball ForEach Enumerator that does not implement a collection or ForEach semantics, rather it just holds a collection or another ForEach Enumerator.

So the real world use is to build a ForEachItem Enumerator or a collection with the script task or some other component and then store it in the FromVariable Enumerator to be used in the ForEach loop.

Thanks,

Universe.Earth.Software.Microsoft.SQLServer.IS.KirkHaselden

Where'd the Wizard go...

Weather'ed DTS users will know it as _The_ Wizard and wonder from where it can be launched. The Import/Export Wizard is no longer on the main start menu. I don't know why, but for some reason the powers that be decided that it would be better to not have the Import/Export Wizard on the start menu. 

If you're one of those folks that uses the Import/Export Wizard a lot and would like to have it available instantly, do what I did and put it on your start menu or in my case the Quick Launch bar.

  1. Open the folder where IS is installed (Unfortunately, still called DTS). On my machine it's at : C:\Program Files\Microsoft SQL Server\90\DTS\Binn
  2. Right click and drag/drop it to the task bar in the quick launch toobar.
  3. When the context menu pops up, select Create a shortcut here.
  4. Done

You can do the same with the start menu. Also, the other IS binaries are there as well. DTExecUI, DTExec, DTUtil, and the Migration Wizard.

Universe.Earth.Software.Microsoft.SQLServer.IS.KirkHaselden  

SSL security error

In the CTP releases before June, you may have run into an issue with connecting to MSDB when attempting to enumerate packages there. You probably received an error message something like this:

The ExistsOnSQLServer method has encountered OLE DB error code 0x80004005 ([DBNETLIB][ConnectionOpen (SECDoClientHandshake()).]SSL Security error.). The SQL statement issued has failed.

IS will only communicate with the server through an encrypted connection to protect sensitive data in packages. What this error is telling you is essentially that IS is unable to get an encrypted connection to the server. Yukon provides encrypted connections using the "Encrypt Connection" setting. To find out more about this, take a look at books online here: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/e1e55519-97ec-4404-81ef-881da3b42006.htm
This is new in SQL 2K5. Because of the security policy with SSIS, and because SQL 2K does not support this feature, SSIS is unable to use SQL 2K as a storage location for packages and never will.
If you're seeing this problem in SQL 2K5 Pre-June CTPs, it's because you don't have a trusted certificate provisioned for your server. The above link talks about how to remedy that. If you don't have a trusted certificate, in the June CTP, the server will automatically provision a self-signed certificate and enable the encrypted connection. This is only available in the June CTP, however.

Resources:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/e1e55519-97ec-4404-81ef-881da3b42006.htm

http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q276553

http://support.microsoft.com/kb/318605

Universe.Earth.Software.Microsoft.SQLServer.IS.KirkHaselden

 

Server Instances and Integration Services Server

This is something you may run into, especially if you're running Integration Services on a machine with multiple instances and at least one of them is a SQL 2K instance.

The Integration Services server does not support instances, but is aware of SQL Server instances and can be configured to reference a particular instance. Here's how.

There is a file called MsDtsSrvr.ini.xml in the %PROGRAM FILES%\Microsoft SQL Server\90\DTS\Binn folder. If you open that file you'll see something like this:


There are a few interesting settings here, but this one determines which server the IS server will reference for enumerating packages in MSDB:.\YUKONB3


This tells the server to reference the local server instance named