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
The Integration Services server does not support instances, but is aware of
SQL Server instances and can be configured to reference a particular instance.
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
<?xml version="1.0" encoding="utf-8"?>
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]
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]
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]
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]