Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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, 732 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

9 comments, 2,746 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

3 comments, 4,406 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, 522 reads

Posted in Todd McDermid's Blog on 14 May 2012

How To Use T-SQL TIMESTAMP (ROWVERSION) in SSIS

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

2 comments, 4,865 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, 1,615 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, 383 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, 311 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, 345 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, 314 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, 1,828 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, 361 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, 1,139 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, 547 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, 6,290 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, 461 reads

Posted in Todd McDermid's Blog on 18 May 2011

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, 1,100 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

9 comments, 4,467 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,155 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, 610 reads

Posted in Todd McDermid's Blog on 14 March 2011

Older posts