SSIS and SQL Server Instances

by Guest 9 Jun 2005 14:00
This is something you may run into, especially if you're running Integration Services on a machine with multiple instances and at least one of them is a SQL 2K instance. The Integration Services server does not support instances, but is aware of SQL Server instances and can be configured to reference a particular instance. Here's how. There is a file called MsDtsSrvr.ini.xml in the %PROGRAM FILES%\Microsoft SQL Server\90\DTS\Binn folder. If you open that file you'll see something like this: <?xml version="1.0" encoding="utf-8"?> ... [More]

What is Validation?

by Guest 9 May 2005 14:00
Some questions keep popping up about validation and I thought I'd try to clarify it a bit. What is Validation I've never seen a good definition for what validation in IS is supposed to be or what it's supposed to do anywhere. There is a lot of discussion about what components do when validating, but before launching into a discussion about it, I thought it would be helpful to give a little history and explain a little of the philosophy behind validation. This will likely be better than a straight definition because, hopefully, you'll understand the evolution a... [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]

Easy Package Configuration

by Guest 30 Nov 2004 14:00
(By Jamie Thomson) One of the age old problems in DTS is moving packages between your development, test and production environments. Typically a series of manual edits needs to be done to all the packages to make sure that all the connection objects are pointing to the correct physical servers. This is time consuming and gives rise to the possibility of human error, particularly if the solution incorporates many DTS packages. Many companies have provided their own custom solutions for managing this problem but these are still workarounds for a problem that was inherent in DTS. Happily, Inte... [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]