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

SSIS Data Flow "Sequence Containers" are Here!


OK - maybe too much of a celebration... and I do have meatier topics in the article pipeline.  However, I just stumbled across a feature in SSIS that I didn't realize had been included - due to a Connect item I'd raised a long time ago (in early 2009). …

Read more

1 comments, 1,191 reads

Posted in Todd McDermid's Blog on 1 December 2016

Reading a SharePoint 2007 List from SSIS 2016

It's been a while, but since I now get to play with SQL 2016 full time, I have new things to talk about!  One of major difficulties that any technology pro has to deal with is integrating different systems.  Vendors really do try to make this as easy as they can…

Read more

0 comments, 512 reads

Posted in Todd McDermid's Blog on 24 October 2016

Control Flow Package Parts in SSIS 2016

Control Flow Package Parts are a new feature in Microsoft SQL Server 2016 that attempts to enable code reuse within SQL Server Integration Services packages.  The intent is great, but like with many first iterations, there are limited use cases, and rough edges.  I'll provide an overview of those here, and in…

Read more

4 comments, 2,876 reads

Posted in Todd McDermid's Blog on 14 October 2016

Consolidating Multiple Data Marts

One of the challenges that I've worked on in the past months involved adapting our technical architecture to address issues exposed by business re-engineering.  The BI group at CHC used to assign specific people to specific business units in order to deliver solutions to business needs.  The upside of that arrangement was that each…

Read more

0 comments, 539 reads

Posted in Todd McDermid's Blog on 3 August 2015

Data Flow Optimization - Collapse Sparse Buffers

I'll warn you straight off that this is an advanced technique for a fairly small use case - it is NOT a "try this for all data flow performance problems" type of technique.  (Much like the typical "adjust your buffer size" useless advice.)  I ran into errors and warnings in my data…

Read more

1 comments, 1,361 reads

Posted in Todd McDermid's Blog on 22 October 2012

Reading Excel into SSIS via OpenXML Scripting

Reading Excel data isn't easy.  It should be, but there's a lot of unfortunate barriers in the way that I've discussed before.  They boil down to one main reason: Excel wasn't created for IT, it was created for business users.  We're quite obviously an afterthought.  That said, we're much more…

Read more

11 comments, 4,507 reads

Posted in Todd McDermid's Blog on 31 July 2012

Basic SSIS Equivalents to T-SQL's LIKE

There are some things that we do in one SQL Server tool, but can't seem to figure out how to do in another.  One of those for you might be the LIKE keyword in T-SQL.  LIKE is fairly powerful in T-SQL - it does rudimentary regular expressions.  How do you do that in…

Read more

5 comments, 13,374 reads

Posted in Todd McDermid's Blog on 22 May 2012

The Effective Permissions Tool is Handy

An recurring issue with Integration Services packages that read or write to file systems is permissions issues.  It's quite difficult to have a package run properly when it can't see the source files, or can't write results to a particular network share.  But it's insanely hard to get a package…

Read more

0 comments, 983 reads

Posted in Todd McDermid's Blog on 14 May 2012


In some database designs, you may encounter SQL Server columns with a TIMESTAMP or ROWVERSION data type.  What are these, and how do you deal with them in SQL Server Integration  Services?
Time?  Version?
First, let's talk semantics.  There are several unfortunately bad aspects to these names. First, the name "TIMESTAMP"…

Read more

6 comments, 7,475 reads

Posted in Todd McDermid's Blog on 8 May 2012

Quick Reference: How Do I Read an Excel XSLM (Macro-Enabled) File in SSIS?

Excel 2010 (and presumably the next version of Excel) have taken some security precautions with regards to macros in Excel files.  If a spreadsheet has macros in it, you can't save it as an XLSX.  Excel forces you to save it with an extension of XLSM, indicating that the file…

Read more

3 comments, 2,534 reads

Posted in Todd McDermid's Blog on 9 April 2012

PASS Community Summit 2011 Day 3 Keynote

Dr. DeWitt has spokento the PASS Summit for the past two years – turning what was otherwise a veryforgettable “day 3” keynote slot into a must-see event for anyone going to theSummit.
The past two years Dr. DeWitttook us through the internals of columnstore databases and queryoptimization.  They were…

Read more

0 comments, 748 reads

Posted in Todd McDermid's Blog on 14 October 2011

What I Learned at #SPC11

This week, I attended the Microsoft SharePoint Conference 2011 (#SPC11) in Anaheim.  Not my usual beat, to be sure, but our company is starting to use SharePoint more, and we have no internal resources trained on it.  We have a "wild west" set of sites already, plan to add more…

Read more

0 comments, 695 reads

Posted in Todd McDermid's Blog on 7 October 2011

A "New Guy" Joins Our Team

It's a great day for me and Moulding and Millwork (the company I work for).  A few weeks back, I let you all know that I'd been given the green light on finding someone to help me out with Data Warehousing.  One of the first people I turned to for…

Read more

0 comments, 642 reads

Posted in Todd McDermid's Blog on 3 October 2011

Parallelizing Slow Parts of the Data Flow - Part 1 - Preparation

It's quite common to have parts of your Data Flow that are slow, and there are some techniques you can use to improve performance of those parts.  One of them is to try to parallelize the slow operation - but that only works for operations that are parallelizable.  Sort isn't…

Read more

0 comments, 793 reads

Posted in Todd McDermid's Blog on 6 September 2011

Inserting Records AND Getting The Identity in SSIS - Part 2

Since I posted a ways back on retrieving the identity value for inserts in SQL Server Integration Services, I've learned many things - one of which is an inline solution for that problem I didn't know back then.  Check out that previous post for the business problem, then come back here…

Read more

1 comments, 6,241 reads

Posted in Todd McDermid's Blog on 29 August 2011

Presenting Upserts to PASS AppDev VC

Hopefully this crowd knows a little about what they're getting into!  John Jakubowski (blog|twitter) has graciously asked me to present on upsert strategies with SSIS.  What are "upserts"?  A not-so-fancy mashup of the words "update" and "insert".
If you struggle with incremental table loading performance -…

Read more

0 comments, 1,051 reads

Posted in Todd McDermid's Blog on 18 July 2011

What is Pipeline Backpressure?

First - I'll get the "what's a pipeline got to do with Integration Services" question out of the way.  The "pipeline" I'm referring to here is the word commonly used to describe the flow of data in the Data Flow Task.  That term doesn't seem to show up in any…

Read more

3 comments, 2,908 reads

Posted in Todd McDermid's Blog on 7 July 2011

Code Snippet for an SSIS DataFlow Performance Wiretap

I was recently tweaking a few Data Flows, and settled on a very small, but very useful script to help record execution timing inside the flow. 
Decomposition Is Time Consuming
You may know that I do recommend the decomposition technique to help diagnose performance problems in SSIS data flows.  That's…

Read more

0 comments, 850 reads

Posted in Todd McDermid's Blog on 20 June 2011

Use Connections Properly in an SSIS Script Task

There are lots of scenarios in SSIS where you'd want to use a connection within a Script Task.  Unfortunately, the closest help documentation - the comments inside the Script Task itself - give you exceptionally poor advice:
To use the connections collection use something like the following:
ConnectionManager cm =…

Read more

0 comments, 23,458 reads

Posted in Todd McDermid's Blog on 31 May 2011

How To: Use the Same Data Flow Column Twice As a Parameter in SSIS

Every once in a while, you'll have a slightly more complex UPDATE statement in an OLE DB Command or Destination.  You'll use an UPDATE statement or a destination table that needsto use data from one column in your data flow several times.  A typical example is a range update:
UPDATE table1
SET column1 =…

Read more

4 comments, 1,142 reads

Posted in Todd McDermid's Blog on 18 May 2011

Older posts