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
14 May 2010 15:40
How to optimise doing the same lookup multiple times. [More]
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...
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.
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]
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]
5 May 2005 14:00
Adjusting the following properties can have an impact on parallelization during execution:
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]
4 Apr 2005 14:00
(By Jamie Thomson)
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]
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
FROM STG_DATE s
LEFT OUTER JOIN DIM_DATE d
ON s.DateID = d.DateID
WHERE d.DateID IS NULL
-- Method #2
FROM STG_DATE s
WHERE D... [More]
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]