Dougbert.com

SSIS product team is looking for a senior developer (Redmond, WA, USA)

dougbert — Wed, 10 Mar 2010 23:26:00 GMT

For more information, see https://careers.microsoft.com/JobDetails.aspx?jid=13164 . -Doug...(read more)

Thank you for following me. Comments are now disabled.

dougbert — Sun, 07 Feb 2010 00:13:00 GMT

Thank you all for following my "Dougbert on SSIS" blog. As I mentioned in a recent post , I am no longer working on SQL Server Integration Services at Microsoft. While I still expect to spend time with SSIS, I won't have as much time or as much fresh information. So this blog will probably not be very active. But no harm in staying subscribed! I'm sure that I'll never escape from Excel driver issues. (And there are so many custom tasks and data flow components that need writing...(read more)

You could be doing ETL or BI at Microsoft!

dougbert — Mon, 18 Jan 2010 17:15:00 GMT

If you enjoy using the features of SQL Server for ETL or business intelligence projects, consider working close to the source right here at Microsoft. Check out our public careers page at: https://careers.microsoft.com/ . The other day I just happened to be clicking around on our internal job-listing site. (See previous blog posting). I found a fair number of job openings for many ETL and BI-related terms and phrases: ETL, 25 SSIS, 16 Integration Services, 9 OLAP, 33 SSAS, 8 PowerPivot, none Analysis...(read more)

SSIS: Now it's play and not work!

dougbert — Thu, 07 Jan 2010 23:48:00 GMT

For me, that is. I hope. No, I haven't written any magic code that fixes the tedious parts of Integration Services. But in 2010 I will no longer be working on SSIS documentation at Microsoft; I am moving to a new and different role on the SQL Server documentation team. So my theory is that I can now play with SSIS for fun in that "spare time" that we all fantasize about. Don't be too hasty to unsubscribe from this blog! I hope that I will still have information to contribute from...(read more)

Importing from Excel: IMEX and mixed data types

dougbert — Sat, 21 Nov 2009 22:15:00 GMT

When we talk about using import mode, "IMEX=1," to resolve a common problem when importing from Excel, we sometimes give or get the impression that setting IMEX=1 automatically imports all data as text. This is an over-simplification. In fact, IMEX=1 causes the value of the Registry setting, ImportMixedTypes , to be applied. ImportMixedTypes commonly has a value of Text . Therefore, if and only if you have a column that contains both numbers and strings, all the values in that column are...(read more)

Updated component: Refresh SSRS reports directly from an SSIS package

dougbert — Mon, 09 Nov 2009 21:29:00 GMT

My German's a little rusty, but I still try to follow the German-language SSIS blog by Tillman Eitelberg of Bonn, Germany. In yesterday's posting , he talks about his Report Generator Task project on Codeplex (in English). Many of you have probably discovered this custom task already on your own. The Report Generator task lets you load and transform a data set in your SSIS package, then regenerate a (pre-existing) SSRS report with that data. You can save the report output in Excel or PDF...(read more)

New book: SSIS Problem - Design - Solution

dougbert — Mon, 09 Nov 2009 18:32:00 GMT

Microsoft SQL Server 2008 Integration Services Problem-Design-Solution by Erik Veerman, Jessica Moss, Brian Knight, and Jay Hackney ISBN: 978-0-470-52576-0 Paperback, 480 pages Published by Wiley/Wrox in November 2009 List price: US $49.99 Amazon price US $31.49 as of 11/9/2009. Also available from Wiley as an Adobe E-Book. Not yet available from Amazon for Kindle, as of 11/9/2009. In Microsoft SQL Server 2008 Integration Services Problem-Design-Solution , a new book from Wiley/Wrox, 4 recognized...(read more)

CDC for heterogeneous sources with SSIS, from Attunity and Microsoft

dougbert — Tue, 03 Nov 2009 18:20:00 GMT

See the press release from yesterday (11/2/2009): Attunity extends its partnership with Microsoft to enable heterogeneous Change Data Capture Some excerpts: The solution, based on Microsoft’s SQL Server Integration Services (SSIS) and Attunity’s low-impact change data capture (CDC) technology, enables efficient and real-time integration of heterogeneous data with significant cost savings. ... The integrated offering of Attunity’s CDC with Microsoft’s SSIS is now available...(read more)

Get the new SSIS Expressions cheat sheet from PragmaticWorks

dougbert — Tue, 03 Nov 2009 16:59:00 GMT

Available as both a Web page and a downloadable PDF at http://www.pragmaticworks.com/cheatsheet/ . ...and more! -Doug...(read more)

Off topic: Seattle restaurant deals just in time for SQL PASS 2009

dougbert — Mon, 02 Nov 2009 20:53:00 GMT

If you're in Seattle for SQL PASS 2009, and you have an appetite for fine Seattle food but you don't have an unlimited budget, there are several November deals to consider. 3-course prix fixe dinners for $30 Restaurants in the downtown area (including Belltown) include: Andaluca (a favorite of mine) Art Barolo ( impossible choice between lamb and boar! ) Campagne Chez Shea Dahlia Lounge (Tom Douglas) Earth & Ocean Etta's (Tom Douglas) Flying Fish Hunt Club Lola (Tom Douglas) Serafina...(read more)

Updated product: SSIS+ 1.4 from Cozyroc - Visit CozyRoc at PASS

dougbert — Mon, 02 Nov 2009 01:17:00 GMT

Cozyroc is exhibiting at SQL PASS 2009 for the first time this year. From the Cozyroc Web site : CozyRoc will be exhibiting at the Premier Conference for SQL SERVER Professionals. Visit CozyRoc at booth #508 and say HI. Cozyroc has just released version 1.4 of their SSIS+ product, adding still more often-requested features to their add-on library for SQL Server 2005 and 2008. For details on the new and existing features, please see this blog entry, CozyRoc SSIS+ 1.4 has arrived . Here is the most...(read more)

Save 50% today only on new book, SQL Server MVP Deep Dives

dougbert — Wed, 30 Sep 2009 16:53:00 GMT

I got this reminder from MVP Phil Brammer: FYI - You can use the code, pop0928, at checkout from www.sqlservermvpdeepdives.com during the month of September (what's left of it anyway) to save 50% . That is a public code, so please feel free to spread the word. Thanks, Phil Please see my post from yesterday, New book: Learn some SSIS, help children affected by war , for more information about the book. -Doug...(read more)

New book: Learn some SSIS, help children affected by war

dougbert — Tue, 29 Sep 2009 16:04:00 GMT

" SQL Server MVP Deep Dives ," a result of collaboration among 53 SQL Server MVP's and others, is now available for pre-order. The authors are generously donating all their royalties to a charity for children affected by war. Find out more about the book here: http://www.sqlservermvpdeepdives.com . (9/29/09: If you were one of the very first readers this morning, sorry about the mis-typed URL. It's working now. ) The book contains the following 4 chapters on SQL Server Integration...(read more)

Updated product: EBCDIC Source component "Lysine" from Amino Software

dougbert — Wed, 23 Sep 2009 22:10:00 GMT

Patrick & Ben at Amino Software have announced a new version of "Lysine," an Integration Services source component for loading EBCDIC data into the data flow of an SSIS package. I wouldn't know an EBCDIC if one ran up and bit me, so I'll just quote their announcement: Lysine 1.3 Released We are excited to announce that our planned release for 1.2 was merged with features that had been planned for release in 1.3 at a later date. Due to increased international demand, we put a...(read more)

64-bit Excel Driver in Microsoft Office 14

dougbert — Fri, 28 Aug 2009 22:06:00 GMT

First, the good news... If you use SQL Server Integration Services to load data to or from Excel files or Access databases, then a 64-bit driver is headed your way. This is great news for SSIS users in a 64-bit world. Soon you'll be able to run your packages in 64-bit mode on 64-bit computers, rather than wrestle with obscure settings to force some packages to 32-bit mode. The next version of Microsoft Office, Office 14, will be available in a 64-bit version. This version will include a 64-bit...(read more)

Faking success in SSIS

dougbert — Fri, 28 Aug 2009 20:14:00 GMT

A SQL Server Integration Services package has a set of properties with similar names that let you: force the result of running the package return a custom value from the package Their similar names make it easy to confuse these properties. To make matters worse, their descriptions were mixed up in at least one place in Books Online (now fixed). I've just checked BOL one more time, so let's clear up the confusion before I forget which property is which. These properties apply not only to the...(read more)

Speed up your ETL with 4 new SSIS performance videos from Microsoft

dougbert — Thu, 30 Apr 2009 22:38:00 GMT

SSIS: Designing and Tuning for Performance SQL Server Video Series Learn the details of SQL Server Integration Services performance tuning from the people who know it best! Today (4/30/09) Microsoft published 4 new videos on SSIS performance, recorded by experts on the SSIS product team and the SQLCAT (Customer Advisory) team. The videos range from 12 to 16 minutes in length. Measuring and Understanding the Performance of Your SSIS Packages in the Enterprise (SQL Server Video) by Denny Lee, Senior...(read more)

HTML Table Destination: An (incomplete) SSIS custom data flow component

dougbert — Tue, 28 Apr 2009 22:33:00 GMT

I had fun recently creating a custom data flow destination for Integration Services - an HTML Table Destination that writes out the data from the data flow to an HTML file as an HTML table. No applause, please - I copied SSIS developer Matt Masson's code from Codeplex to get started, including his handy helper functions for the tedious stuff like adding custom properties and caching input columns. Plus, it's not rocket science to output tabular data to a well-known text-based markup format...(read more)

Off topic? Pretty charts, and SSIS in Siberia

dougbert — Mon, 27 Apr 2009 21:11:00 GMT

I was crushed by the news that Microsoft AdCenter Analytics was being discontinued. As a beta user, I'd enjoyed the pretty charts about visits to my blog. Where was I going to get pretty charts now (without implementing BI of my own, at least)? So I added the free ClustrMaps widget to my page (you'll see it in the right-hand sidebar if you scroll down far enough), and I think the results are fascinating: Random observations... See that dot out there in Siberia, on the Mongolian border? Wouldn't...(read more)

Logging custom events for Integration Services tasks

dougbert — Mon, 13 Apr 2009 22:57:00 GMT

All Integration Services tasks and containers support a default set of events for logging. These events typically represent different stages in the "lifetime" of the object at run time, such as "OnPreExecute", "OnPostExecute", and "OnError". Many tasks also support custom log entries that may be of more interest to you than the generic events. You can find a list here in BOL (although I see that the list isn't 100% up to date): Custom Messages for Logging...(read more)

No need to call Support for these common challenges in SSIS

dougbert — Sun, 12 Apr 2009 23:16:00 GMT

My hard-working teammate and fellow technical writer, Carla Sabotta , recently scanned the records of an eye-crossing 900 Microsoft Support incidents involving SQL Server Integration Services 2005 and 2008. She was looking for issues that the customer could easily have answered with nothing more than the right Help topic from SQL Server Books Online. The percentage turned out to be quite small. I'm not certain how to interpret this small number, but these seem to be my options: "Glass is...(read more)

SSIS and clustering: What you should do instead

dougbert — Thu, 09 Apr 2009 23:25:00 GMT

Lots of customers ask about configuring SQL Server Integration Services in a failover cluster. We recommend that you DON'T configure SSIS as a cluster resource. There are almost no benefits to doing so, and you can gain many of the benefits that you want by simple configuration changes. By editing the configuration file for the SSIS service on each node of a cluster, you can manage the packages on any node from any other node. For more information, please see the Books Online topic, Configuring...(read more)

Understand how SSIS package configurations are applied

dougbert — Tue, 07 Apr 2009 21:06:00 GMT

This week I'm cleaning up our BOL documentation about how SSIS package configurations are applied, and how the behavior has changed between SQL Server 2005 and SQL Server 2008 Integration Services. These changes affect what you can and can't do to change your design-time settings at run time. My cleanup was prompted, as is often the case, by a kick in the butt from Microsoft Support and its customers. I admit that it has taken me a while to figure out all the ifs, ands and buts. SQL Server...(read more)

Setting the DataType of IDTSParameterBinding objects (Execute SQL Task)

dougbert — Tue, 07 Apr 2009 18:18:00 GMT

If you're writing Integration Services code that uses the Execute SQL task, you also have to create parameters programmatically. The ParameterBindings property of the ExecuteSQLTask returns an IDTSParameterBindings collection of IDTSParameterBinding objects, each of which has a DataType property. But the DataType is just an integer. Where do the values come from? Why doesn't it just get its values from an enumeration that's clearly documented? Well, the values for the DataType of an IDTSParameterBinding...(read more)

Import from a macro-enabled Excel 2007 workbook (*.xlsm) (CORRECTED)

dougbert — Tue, 31 Mar 2009 19:35:00 GMT

CORRECTED ON 4/2/09 FROM ORIGINAL POSTING ON 3/31/09 The Office 12 ACE Provider recognizes the .XLSM extension for a macro-enabled Excel 2007 workbook. However, the Excel Connection Manager (in SQL Server 2008 Integration Services) only recognizes the .XLSX file extension for Excel 2007 files. Therefore you have to use the OLE DB Connection Manager (and Source and Destination) with Excel 2007 files that have a file type and file extension other than .XLSX. Importing . You can connect to, and import...(read more)