Using the Script Component as a Conditional Split

by Darren Green 12 Nov 2010 13:46
This is a quick walk through on how you can use the Script Component to perform Conditional Split like behaviour, splitting your data across multiple outputs. We will use C# code to decide what does flows to which output, rather than the expression syntax of the Conditional Split transformation. Start by setting up the source. For my example the source is a list of SQL objects from sys.objects, just a quick way to get some data: SELECT type, name FROM sys.objects type name S syssoftobjrefs F FK_Message_Pa... [More]

Optimising Lookups with the Cache Transform (Video)

by Allan Mitchell 14 May 2010 15:40
How to optimise doing the same lookup multiple times. [More]

The Pipeline Limiter in SQL Server Integration Services (Video)

by Allan Mitchell 14 May 2010 11:51
Not many people understand that the Integration Services pipeline can be intentionally throttled back under the covers to stop you from running out of memory.  In this video we show you how to detect this is happening and explain more about the limiter itself. More...

Extending SSIS with custom Data Flow components (Presentation)

by Darren Green 6 Mar 2008 14:00
Download the slides and sample code from my Extending SSIS with custom Data Flow components presentation, first presented at the SQLBits II (The SQL) Community Conference. Abstract Get some real-world insights into developing data flow components for SSIS. This starts with an introduction to the data flow pipeline engine, and explains the real differences between adapters and the three sub-types of transformation. Understanding how the different types of component behave and manage data is key to writing components of your own, and probably should but be required knowledge for anyone bu... [More]

Log Events and Pipeline Events

by Guest 6 May 2005 14:00
I was about to write about data flow buffer size configuration properties but need to get this one out first so that we can refer to it. The Data Flow task (internally and in the object model also called the 'Pipeline') logs some pretty interesting information that describe the internal scheduler. To see these messages, one way is: 1. In a package you want to get detailed pipeline logs on, select the 'Logging...' menu option on the control flow. 2. Check the Data Flow task of interest on the tree on the left. Switch over to Details and se... [More]

Parallel Execution Properties

by Guest 5 May 2005 14:00
Adjusting the following properties can have an impact on parallelization during execution: 1. MaxConcurrentExecutables This is a property on the Package. It defines how many tasks can be run concurrently. A value of -1 means the number of processors and when hyperthreading is turned on, it counts the number of logical processors, not physically present ones. In the little testing that I (Ash) have done, I've not noticed significant difference (<5 percent) in performance between turning on hyperthreading or leaving it off. It'd be interested to know if you see better resu... [More]

Generating Surrogate Keys

by Guest 4 Apr 2005 14:00
(By Jamie Thomson) Introduction Surrogate keys are generally considered fundamental building blocks of a data warehouse. They are used as identifiers for dimensional members and enable us to manage slowly changing dimensions. SSIS does not contain a built in component for generating surrogate keys but there is still a mechanism for doing it – the Script Component. The Script Component allows us to modify the data in a data flow path using managed code and we can use it to generate surrogate keys. The Row Number Transformation can be used to help generate surrogate keys... [More]

Get all from Table A that isn't in Table B

by Guest 1 Sep 2004 14:00
(By Jamie Thomson) A common requirement when building a data warehouse is to be able to get all rows from a staging table where the business key is not in the dimension table. For example, I may want to get all rows from my STG_DATE table where the DateID is not in DIM_DATE.DateID. There are 2 ways to do this in conventional SQL. -- Method #1 INSERT DIM_DATE SELECT s.* FROM STG_DATE s LEFT OUTER JOIN DIM_DATE d ON s.DateID = d.DateID WHERE d.DateID IS NULL -- Method #2 INSERT DIM_DATE SELECT s.* FROM STG_DATE s WHERE D... [More]

The Script Component as a Transformation

by Allan Mitchell 2 Aug 2004 14:00
In this article we are going to assume a few things about the reader as we want to concentrate as much as possible on the Script Component itself and not the peripheral stuff The things we will assume are: You are happy with adding a Data Flow task to the designer in Workflow You are happy to configure an OLE-DB Source adapter You are comfortable with configuring mappings in the Destination adapter As usual we will show you a graphic of the Data Flow once it is completed so we know that if yours resembles ours at the end then chances are we are at the same place. ... [More]