Kirk Haselden

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  

Next CTP...

The next CTP is close and I wanted to give you all a preview of some improvements that will be available there.

I'm happy to write that the new transfer tasks will be available. Through a series of planning changes and priority shifts, these tasks were pushed off until the very last minute, but they will be available in the next CTP. They will be backward compatible and integrated with the migration wizard to support migrating the DTS 2000 transfer task incarnations.

Another exciting development in the next CTP will be property expressions in the dataflow task. I'll blog more about this later, it deserves it's own dedicated blog. But, in a limited manner, some components will support property expressions on certain key properties. This will vastly simplify the solution to some difficult dataflow task configuration issues.

Look for these and other improvements in the next CTP.

Thanks,

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

Out parameters in the Execute SQL Task...

One of the more strident complaints we've been receiving is the fact that the SQL Task didn't support out parameters on stored procedures. We listened.

Our crack developer, Mohammed spent some substantial time and effort on the issue and isolated the problem.

As it turns out, the SQL Task _did_ support out parameters. But, in some situations, the SQL Task had a bug in the portion of the code that retrieved the result into the parameter. Because of the way OLEDB returns multiple result sets, if the code doesn't consume all the results, and the out parameter value is one of the results, the out parameter won't get correctly set. For simple cases, stored procedures with only one result for example, the out parameters worked fine.

The bottom line is, ultimately, you should see this begin to work in the near future!

Thanks,

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

Next CTP available soon...

We're really close to sending out the next CTP. There are a few notable changes in Integration Services.

• Package Reader/Writer roles enabled in Management Studio. You probably haven't heard much about the security features in Integration Services because not a lot of people know about them. I'll blog on this soon, but this is part of that set of security features that have recently come online.
• Better access to DTS 2000 packages node
• You can launch the migration wizard directly from the DTS 2000 packages node in Management Studio.
• Important bug fixes
• There are also some known issues.

These are all noted in the CTPNotes (readme) but my guess from the questions being asked on newsgroups etc. is that few people actually read that document because:

1. People don't know about it. Now you do.
2. It's very long. It is very long, but it's also separated into product interest areas so that if you're only interested in replication or reporting services etc., you can read just the portions that pertain to those products.
3. Didn't know it had this kind of information. The product specific information is toward the end of the document. Scroll down until you see the product name in which you're interested to get the latest changes.

It's worth a quick runthrough even if you don't read everything. There are probably one or two people in the world who read the entire document. But if you take a few minutes and breeze through it while you're waiting for it to install etc. I think it will be worth your time.

Thanks,

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

Fleeting issue with debugging in the designer in the February CTP

If you attempt to debug a package and the package will not run in the designer. Also, if you attempt to execute the package by selecting Debug | Start Without Debugging and it correctly executes, you may be seeing an issue with setup that we've seen on some machines.

If you're experiencing this problem, look the registry value under:

HKEY_CLASSES_ROOT\TypeLib\{3ED4D7CC-1ECC-4D11-9AC7-FFA71CE45705}\1.0\0\win32

It should look something like this:

C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\DtsDebugHost.exe

If it has quotes around it, remove the quotes. That should fix the problem.

Thanks to Michael Entin on the SSIS dev team for figuring this out for one of our customers. Hopefully you haven't seen this, but if you do, this should fix the problem.

Thanks,

Universe.Earth.Software.Microsoft.SQLServer.SSIS.KirkHaselden

Raw adapters to use variable access mode

We received feedback that the raw adapters were difficult if impossible to configure. We've modified the raw adapters so that it's possible to easiliy configure them. They now use a variable access mode. The variable points to the location of the raw file and we all know how easy it is to configure variables, right?

This should make it easier to deploy packages with the raw file adapters in them.

Thanks,

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

Transfer SQL Server objects tasks

Many have commented to me about the lack of transfer tasks or asked about the Integration Services story for the transfer tasks.

I'm happy to say that we will have tasks that are compatible with and will upgrade from the DTS tasks.

They will use a new SMO connection manager.

• Transfer Database Task
• Transfer SQL Server Objects Task
• Transfer Stored Procedures Task
• Transfer Logins Task
• Transfer Jobs Task

The Transfer Database Task transfers an entire database either by attach/detach or online mode. The Transfer SQL Server Objects Task is the new replacement for the Copy SQL Server Objects Task and allows you more flexibility to move objects such as schema, tables, primary keys, etc. along with the other types of objects.

I'm not sure when these will make it to your desktop, but it should be soon.

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

Next CTP... Ah! There it is.

http://www.eweek.com/article2/0,1759,1772177,00.asp

Finally, I've been talking about it for a while.

Oh, and IDW?

Internal Development Workstation - This has its roots in the NT group. Basically, IDW builds were builds that developers would pick up and use internally for dog fooding etc. They were considered of a certain quality bar.

In SQL Server, IDWs are essentially mini-milestones before a major milestone.

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

CTP huh?

For those who've asked. CTP stands for “Community Technology Preview”

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

Next CTP : Variable mappings in the ForEachLoop...

There were some problems with the way early incarnations of the ForEach Loop passed around the current enumerated value from its configured ForEachEnumerator. For example, there are some ForEachEnumerators that return more than one value per each iteration and there was only one system variable for holding the current enumerated value.

Although variable mappings in the ForEachLoop aren't new, in the next CTP ForEachPropertyMappings have been removed completely. If you were using them, you may need to convert your packages to using variable mappings.

Here's how:

Here are some different combinations and how to upgrade.

Current Enumerated Value mapped to a Variable

• If you already have the variable, and you mapping to the Value property, just select the existing variable in the “Variable“ column. Done.

Current Enumerated Value mapped to a Property on a Task

• Create a new variable in the “Variable“ column in the ForEach Loop UI.
• The variable can be at any scope.
• Close the ForEachLoop and select the task that has the property to which you mapped.
• Click on the Expressions property in the property grid.
• Click on the button with “...“ on it.
• That opens the Property Expressions Editor.
• Select the property you wish to modify in the “Property“ column.
• Click on the “...“ button in the expression column.
• That opens an expression editor.
• Drag the variable you created above from the top left pane into the expression window.
• You're done.
• Now the property on the task will have the value the ForEachLoop puts into the variable that you mapped.

Current Enumerated Value mapped to a Connection String (or other property) on a Connection Manager

This is the same as above for tasks, except the variable must be created at package scope because those are the only variables visible to connection managers.

Current Enumerated Value mapped to a Log Provider property

This is also the same as for tasks, except, to get at the expressions you need to view the log provider in the package explorer view.

Most folks, I believe, are no longer using ForEachProperty Mappings, but if you are I hope this is clear and helps you if you work through any problems. 

Thanks,

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