Dougbert.com

See all the new SSIS articles, videos, & samples from Microsoft

dougbert — Tue, 18 Nov 2008 22:16:00 GMT

Have you seen all the recent articles, videos, & samples for Integration Services? In the future, we hope to offer you an RSS feed for this purpose, but we're not there yet. For now, I hope that you find this list useful, especially since it was painfully tedious to create! (Note: When I look at this blog posting in Internet Explorer, I get an unexpected warning from the IE Phishing filter...probably because of all the hyperlinks set to open in a new window. I can reassure you that the only...(read more)

Your vote on Microsoft Connect influenced SQL Server 2008 Service Pack 1

dougbert — Fri, 14 Nov 2008 23:22:00 GMT

Your vote counts on Microsoft Connect! The SQL Server product team looked closely at Connect bugs from our customers to plan the fixes that will be included in SQL Server 2008 Service Pack 1. After setting aside feature requests (DCRs), they started at the top of bugs with the most votes, and worked their way down (in number of votes) until they reached the limit of what can realistically be fixed in time for SP1. It was time and resources that decided the number of bugs to fix, not a predetermined...(read more)

Add logging to the Data Profiling Task

dougbert — Tue, 11 Nov 2008 22:27:00 GMT

Yesterday I was on a call where we looked at a few usability requests for the Data Profiling Task that didn't make it into RTM. One such request was task-specific logging. Surprise! The attentive developer at Microsoft Research had added it at the last moment, and few people had noticed. So this feature is not mentioned in BOL. Enabling task-specific logging for the Data Profiling task There's a DataProfilingTaskTrace logging event that's unique to this task that you want to enable, to...(read more)

Learn more about using Change Data Capture (CDC) in SSIS 2008

dougbert — Tue, 11 Nov 2008 00:11:00 GMT

SQL Server 2008 Enterprise includes the new Change Data Capture feature, or CDC, largely intended for use by ETL developers performing incremental loads of a data warehouse. With CDC, you can easily identify rows that have been inserted, updated, or deleted -- no more need for complex queries and other custom solutions! The other day I promised links to some resources, and now they're finally all online. Try the 2 new end-to-end samples on Codeplex Sandra Ward, the developer of CDC, has done...(read more)

InfoWorld praises Lookup caching, CDC in SSIS 2008

dougbert — Wed, 05 Nov 2008 18:03:00 GMT

Read what Sean McCown of InfoWord says about the benefits of Lookup caching and Change Data Capture for the enterprise. "Microsoft has taken some big steps to ensure that SSIS (SQL Server Integration Services) can compete directly with competitors such as Informatica by offering lookup caching and CDC. ...both lookup caching and CDC can improve the speed of loads by orders of magnitude." InfoWorld praises Lookup caching, CDC in SSIS 2008 How can you learn more about the new features in...(read more)

Check out the new Integration Services portal page on MSDN

dougbert — Tue, 04 Nov 2008 00:31:00 GMT

When you visit the Integration Services page on MSDN or TechNet, you now get fresh and useful links that include community content, rather than a stale set of links to Books Online topics. Here on the Integration Services team, we're happy to be the first SQL Server feature to showcase the new look. Please check it out! SQL Server 2008 – Integration Services on MSDN This page is for you and fellow ETL developers. Use the Feedback link in the upper right to tell us what you like and don't...(read more)

ETL in the cloud, or just for the cloud?

dougbert — Mon, 27 Oct 2008 21:48:00 GMT

Lots of comments today about PDC and the Microsoft executives discussing Windows Azure, the new platform for cloud computing. "Everyone" - that is, Matthew Roche and Jamie Thomson - noticed (without even being present!) the "ETL" on Bob Muglia's slide, which it's now my turn to borrow: Do folks really want ETL *in* the cloud? That will be interesting to see. If you're interested in ETL *to or from* the cloud today, don't miss the SQL Server Data Services Connectors...(read more)

Didn't make it to the recent Microsoft BI Conference?

dougbert — Tue, 21 Oct 2008 23:33:00 GMT

Well, you saved a ton of money, but missed a lot of fascinating presentations. Each hour brought another panicked decision over which talk to choose, from the several available tracks. (Except when Donald Farmer's title promised to make me "cool, smart, and sexy" by hearing about the Data Mining Add-ins for Excel.) But all is not lost! Content from many of the Integration Services presentations is, or will soon be, available... Official SSIS team blogger Matt Masson presented on "...(read more)

MSDN publishes first 2 articles by Integration Services product vendors

dougbert — Tue, 21 Oct 2008 23:14:00 GMT

MSDN/TechNet has just published 2 new articles by third-party vendors of commercial products that work with, or complement, SQL Server Integration Services. In Extending Integration Services with the Script Task and Script Task Plus , Ivan Peev of CozyRoc talks about the built-in Script task, then discusses the additional features provided by Script Task Plus, a component that's included in the SSIS+ library from CozyRoc. For example, Script Task Plus lets you reuse the same script easily in...(read more)

Connectors now available for SAP BI, Oracle, and Teradata

dougbert — Tue, 21 Oct 2008 23:03:00 GMT

Both of the promised post-RTM packages of connectors for use with SQL Server 2008 Enterprise and Developer are now available for download, since last Friday's release of the October Feature Pack that contains the SAP BI connector. Each set consists of a connection manager, source, and destination, and is available in both 32-bit and 64-bit versions. In both cases, Microsoft took advantage of the existing expertise of third-party companies to develop these high-performance connectivity components...(read more)

New 10-minute how-to videos on Integration Services

dougbert — Thu, 16 Oct 2008 18:30:00 GMT

The Integration Services documentation team at Microsoft recently published its very first batch of 10-minute how-to videos. We hope that beginning to intermediate users will find these useful, and we're pleased to be taking some first steps beyond the written word of BOL. You can find the entire list of 5 Integration Services and 3 Analysis Services videos at this MSDN page for SQL Server Videos . The Integration Services videos are: Creating a Basic Package (SQL Server Video) (This could have...(read more)

Setting an expression dynamically

dougbert — Sat, 11 Oct 2008 23:53:00 GMT

Former Integration Services developer Michael Entin recently responded to an interesting question on the internal SSIS discussion list at Microsoft. The user was asking: How can I assign the expression [in this case, for the Derived Column transformation] dynamically at run time? The intuitive but less-than-obvious answer is...use an expression to set the expression! This lets you put your expression in a string variable such as @User::DerivationRule , and assign that by using an expression to the...(read more)

The first Integration Services 2008 book is now available

dougbert — Thu, 09 Oct 2008 22:53:00 GMT

http://www.amazon.com/gp/product/0470247959/ Professional Microsoft SQL Server 2008 Integration Services by SQL Server MVPs Brian Knight and Erik Veerman, our own broadly smiling Grant Dickinson, and industry experts Douglas Hinson and Darren Herbold At this writing, US $31.49 on Amazon.com and in stock. Although this is the 2008 update to the 2005 version of the book, it has been massively updated with new material. It has also been carefully tech reviewed by yours truly and several other members...(read more)

New "social bookmarking" features on MSDN & TechNet

dougbert — Wed, 10 Sep 2008 17:47:00 GMT

I thought you might want to take a look at this blog posting - Launched: Social Bookmarking V1 on MSDN and TechNet - although I haven't had the leisure to explore it yet myself. The stated goal is: " These new sites, which have been available in preview since May 2008, make it easy for Microsoft's enormous technical community to help each other discover the best resources on any topic from across the web. " The documentation team is frequently and painfully reminded that discoverability...(read more)

New MSDN articles on Integration Services deployment and performance

dougbert — Wed, 03 Sep 2008 17:41:00 GMT

The Microsoft MVPs and consultants who spend every day implementing real-world solutions with SQL Server Integration Services gain a wealth of practical knowledge that even the development team can't match. So we're very pleased to announce a first batch of articles in the MSDN Library from these experts, on the pressing issues of package deployment and performance. These aren't "buy our product" or "admire our benchmarks" whitepapers...these are bundles of best practices...(read more)

New SSIS samples from dev team and doc team

dougbert — Thu, 21 Aug 2008 16:57:00 GMT

While the Integration Services development team has been busy finishing the custom component and code samples that Matt Masson recently blogged about , my hard-working colleague Carla Sabotta has been wrapping up a couple new package samples along with a new tutorial that walks through the steps of using the Import and Export Wizard. The dev team's code samples are largely intended for developers, while the package samples and tutorials are intended to benefit beginning users of Integration Services...(read more)

Excel, the Office 12 ACE Provider, and performance

dougbert — Wed, 20 Aug 2008 17:39:00 GMT

Recently an internal Microsoft team ran into performance issues when they switched from using the Jet Provider and writing out Excel 2003 files, to using the Office 12/2007 ACE Provider and writing out Excel 2007 files from their Integration Services package. Their inquiries brought the following information to light: There are 3 reasons for reduced performance with the Office 12 ACE Provider when used with Excel 2007 files , either binary (.xlsb)or XML (*.xlsx) : The ACE Provider reads the entire...(read more)

Visual Studio SP1 requirement for SQL Server 2008

dougbert — Tue, 12 Aug 2008 15:42:00 GMT

SQL Server MVP Phil Brammer has already blogged about the fact that, if you have Visual Studio 2008 installed, you have to install VS 2008 Service Pack 1 before installing SQL Server 2008 "Katmai". Why? Because Katmai contains bits and pieces of SP1, and will refuse to install over VS 2008 RTM. (Just to make life more interesting, Microsoft didn't release VS 2008 SP1 until several days after SQL Server 2008.) You can read a good blog post that summarizes the entire issue, and explains...(read more)

Conversion should be your own decision!

dougbert — Sat, 19 Jul 2008 18:02:00 GMT

No, this isn't my first religious posting! We're talking about the SQL Server Import and Export Wizard , which was built upon Data Transformation Services (DTS) in SQL Server 2000, and is now built upon Integration Services in SQL Server 2005 and 2008. Some users have complained that the new wizard is more troublesome than the DTS wizard. " I never used to get errors in the good old days of DTS. " But life was not all good. You didn't get so many errors and warnings from the...(read more)

Some of the XML schemas used by Integration Services

dougbert — Sat, 19 Jul 2008 15:28:00 GMT

We have recently published some but not all of the XML schemas used by SQL Server Integration Services to http://schemas.microsoft.com . Most (or all?) of our .XSD schema files are never installed to your hard drive, but are embedded in our compiled code as string resources. Now you can view some of those schemas online. What we have NOT published The .DTSX package schema. The schema for the service configuration file, MsDtsSrvr.ini.xml . The schema for XML configuration files. What we HAVE published...(read more)

SQL Server 2008 Feature Pack and Integration Services

dougbert — Wed, 02 Jul 2008 20:14:00 GMT

Bloggers (like my pal Buck Woody on his Carpe Datum blog ) are mentioning the SQL Server 2008 Feature Pack . The so-called Feature Pack is a Web page that lists many individual downloads related to SQL Server 2008. Here are some of the features of the Feature Pack, especially as they relate to Integration Services: If you have SQL Server 2008, you already have many of the items listed on the Feature Pack page. The only item in the list that's specific to SSIS/DTS is the Microsoft SQL Server 2005...(read more)

Not for use with the SendMail task!

dougbert — Wed, 25 Jun 2008 22:33:00 GMT

Hey, someone finally told those Marketing folks that there's something other than coffee mugs and polo shirts! But I wasn't expecting...US postage stamps! Sure enough, now you can mail your bill payments with a SQL Server 2008 stamp... Click the Microsoft link at this URL . You'll notice that YOU can create your own stamps too ...only $17.99 for a sheet of 20 - $0.42 first class stamps , and the price goes down if you order more than 1 sheet. I'm using my widely-recognized artistic...(read more)

Speaking of writing Office XML...

dougbert — Sat, 21 Jun 2008 20:17:00 GMT

In my third installment on Excel issues , I proposed writing out Excel XML rather than binary Excel files as one way to avoid both the issues of the Excel driver, and the risks of Excel automation. Recently I came across a DTS custom task that I wrote in Visual Basic.NET in 2004 that does exactly that, in a very basic single-table "proof of concept" kind of way...in Excel *and* Word XML, thank you very much. I've uploaded the source code here . I suspect that the Excel XML standard...(read more)

Excel in Integration Services, Part 3 of 3: Issues and Alternatives

dougbert — Sat, 21 Jun 2008 20:05:00 GMT

This post is the 3rd and last in a series of 3 planned installments about using Excel data files with Microsoft SQL Server Integration Services. Connections and components Tables and data types Issues and alternatives Introduction Don't expect miracles . If you've been following this series, I hope that anticipation - and frustration with the Excel driver - have not led you to expect miracles. I do not have secret insider knowledge, and frankly, some of the issues with the Excel driver simply...(read more)

Where is Excel Part 3?

dougbert — Sat, 21 Jun 2008 16:57:00 GMT

Coming later today after proofreading. Please don't anticipate miracles - some of the issues with the Excel driver simply don't have solutions or workarounds that are satisfactory in all cases. You forgot something in one of your blog posts Please comment! I've enabled anonymous comments on the blog, rather than miss your valuable input by forcing you to "join". However I moderate comments to avoid spam or anything inappropriate. What's that on your face? The fat furry fuzzball...(read more)