SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Runtime Interaction with SSIS Packages - Confirming Actions

In developing my data warehouse ETL processes, there are some packages and SQL Agent jobs that are both necessary to the process of deploying a new version of the ETL system - and yet are extremely dangerous.  These packages "reset" certain control infomation to a known state, which is a destructive…

Read more

3 comments, 2,227 reads

Posted in Todd McDermid's Blog on 10 May 2011

Iterating Over Columns in the SSIS Script Component

Just recently a question was posed in the MSDN SSIS Forums about how to create a "generic" script component that would read all columns for each row passed through it in the Data Flow.  The standard impetus for this kind of behaviour is a logging or auditing step in the Data…

Read more

10 comments, 12,229 reads

Posted in Todd McDermid's Blog on 3 May 2011

Transforming SSIS' sysssislog Entries Into Something More Readable

The format of sysssislog in SSIS 2005 and 2008 isn't great for human consumption, so what follows is an attempt to reformat it for easier digestion.  It isn't perfect - and here's hoping that the new Denali (the next version of SQL Server) server-side toolset will improve the situation it as…

Read more

2 comments, 1,888 reads

Posted in Todd McDermid's Blog on 26 April 2011

More Changes for the Dimension Merge SCD?

A while back, I ran a contest to find a new name for the SCD component hosted on CodePlex.  The results were the best "lemons into lemonade" situation I could have expected.  Now I've got some other news I can share that should extend the reach and capabilities of every Data…

Read more

0 comments, 887 reads

Posted in Todd McDermid's Blog on 14 March 2011

Say Hi to the Dimension Merge SCD Component

Last week was a great week all around.  It started with Canada's first SQL Saturday in Vancouver - packed with over 300 attendees watching five tracks of local speakers and MVP designated imports.  I had the privilege of hosting one of those rooms and watching Donabel Santos (blog|

Read more

0 comments, 974 reads

Posted in Todd McDermid's Blog on 9 March 2011

Changes to the SSIS Slowly Changing Dimension Component

Probably not the kind of change you're expecting.  I've recently been contacted by the Kimball Group, and they've asked me to stop using the word "Kimball" to identify the component.  They have every right to make sure nobody is confused about who they are and what they do, or…

Read more

20 comments, 3,023 reads

Posted in Todd McDermid's Blog on 25 February 2011

How To: Load Dimension Tables with Integration Services - Introduction

When I first started down the business intelligence and data warehousing road, I needed to learn the foundations of dimensional design.  Ralph Kimball's methods seemed appropriate at the time, and they still are for me.  The next step was learning how to make Microsoft's BI tool stack follow that methodology,…

Read more

0 comments, 1,140 reads

Posted in Todd McDermid's Blog on 16 February 2011

Performing a Cross Join (Cartesian Product) in SSIS

There are a few times where you might want to perform a cross join/cartesian join/cartesian product operation in SSIS.  One of those scenarios could be performing some kind of a double-sided "range join" where you can't line up a specific key in your data flow to a specific key in your lookup table.  Quite often…

Read more

0 comments, 3,359 reads

Posted in Todd McDermid's Blog on 28 September 2010

Parallelism in SSIS - Multiple Lookups

This question just came up in the forums, and it highlights how difficult it can be to try to optimize Integration Services.  It's difficult because the SSIS concept of the Data Flow - an in-memory pipeline of buffers - isn't the paradigm that a great many of its users are…

Read more

1 comments, 1,844 reads

Posted in Todd McDermid's Blog on 6 September 2010

Convert Several Columns in SSIS With Fewer Clicks and Confusion

I've had this one in my toolbox for a while now, decided to publish it on CodePlex a while back, and am now getting around to blogging about it.  If you've seen this Connect submission, you've got an idea as to why I've constructed it.  Jamie Thomson (blog

Read more

0 comments, 623 reads

Posted in Todd McDermid's Blog on 30 August 2010

Use ReadOnlyVariables and ReadWriteVariables properties in Scripts

For those of you just starting out with SQL Server Integration Services, you'll find a LOT of information out there on the internet.  This series (posts tagged with "Best Practice") is intended to bring the focus back to fundamentals.  I've found that it's easy to get confused with advanced…

Read more

0 comments, 2,359 reads

Posted in Todd McDermid's Blog on 24 August 2010

Integration Services vNext Coming Soon?

That title may be a little premature - but I've recently seen a (relative) boatload of Connect submissions getting resolved, either positively or negatively.  Some submissions of mine have been "fixed", and several of the other MVPs have had movement on their issues, like Simon Sabin's (blog|twitter

Read more

0 comments, 454 reads

Posted in Todd McDermid's Blog on 16 August 2010

Conversion Between Unicode and Non-Unicode Data Flow Pattern

I don't think I'm alone when I find myself having to convert several columns in my Data Flow from Unicode strings to non-Unicode strings, or vice versa.  Reading from text files, Excel sources, ODBC drivers, or lookups resulted in my Data Flow being populated with one string type or the other. 

Read more

0 comments, 808 reads

Posted in Todd McDermid's Blog on 12 July 2010

Feature Packs for SQL Server (and SSIS)

Some of the best extensions for Integration Services (and SQL Server itself) are found in what Microsoft has released as "Feature Packs".  They aren't very well advertised, but include some of the essential tools for certain scenarios - like the DB2 connectors, PowerPivot, and Report Builder.  Most of those tools…

Read more

0 comments, 1,901 reads

Posted in Todd McDermid's Blog on 21 June 2010

Using the Data Viewer Intelligently in SSIS Data Flows

If you've ever needed to debug a fairly complex Data Flow, you've probably wanted to take a peek at what data is moving through the flow at various points.  The data viewer in Integration Services is how you do that - but it has a major drawback.  You may be processing…

Read more

0 comments, 1,855 reads

Posted in Todd McDermid's Blog on 31 May 2010

The SSIS Data Flow is Like... an Automotive Assembly Line

When I describe (or rather, attempt to describe) technical subjects to people that aren't familiar with them, I tend to fall back on automotive metaphors.  No, I'm not a gearhead, and no, it doesn't work every time, but it does seem to be the first thing that pops in my…

Read more

0 comments, 413 reads

Posted in Todd McDermid's Blog on 27 May 2010

Newer posts