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]

Looping over files with the Foreach Loop

by Allan Mitchell 31 May 2005 14:00
In SQL Server 2000 Data transformation Services (DTS) it was a bit of a hack to be able to loop over files of a given type in a certain directory and import them into your destination. It involved a lot of "Glue Code" and a certain amount of fooling the package into going back to a previous task because it still had work to do. Well thankfully in SQL Server 2005 Integration Services (SSIS) that has all changed and this article is going to show you how. The image below shows us how incredibly simple and clean the package will look when finished. There are some things worth point... [More]

Handling different row types in the same file

by Allan Mitchell 26 May 2005 14:00
Sometimes source systems like to send us text files that are a little out of the ordinary. More and more frequently we are seeing people being sent master and detail or parent and child rows in the same file. Handling this in DTS is painful. In SSIS that changes. In this article we'll take you through one file of this type and provide a solution which is more elegant than what we have today, and hopefully will give you ideas as to how you can handle your own versions of this type of file. As mentioned this is a simple solution but we could extend it to include more powerful processing tech... [More]

SQL Server Integration Services - Thinking Outside of the DTS Box (Presentation)

by Darren Green 19 May 2005 14:00
Download the slides and sample code from our SQL Server Integration Services - Thinking Outside of the DTS Box session at the 2005 PASS European Conference. Presentation & Samples (521KB) SQL Server Integration Services - Thinking Outside of the DTS Box

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]

Using Parent Package Variables in Package Configurations

by Allan Mitchell 6 Dec 2004 14:00
Package configurations are now the prescribed way of being able to set values within your package from an outside source. One of the options for the source is Parent Package Variable. The name is perhaps a little misleading so this article is meant to guide you through this slight confusion and into using them. It also helps to explain a key concept in SQL Server Integration Services Setting Up Your Packages This example is very simple. We have two packages. One is called Caller and the other is named Called. In the Caller package we have an Execute Package task which calls the other packag... [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]

When to use ProcessInput or PrimeOutput in your Component

by Allan Mitchell 22 Nov 2004 14:00
When building our own custom pipeline components two methods in particular are very important and it may become a little confusing as to which one is used in which situation. The two methods are: ProcessInput PrimeOutput Depending on the type of component you are building will depend on whether you need to use one or both of these methods. Let's take a look now and identify how to use them correctly. All components need to have the DtsPipelineComponent attribute, and in particular the ComponentType property. As we detail each type of component we will tell you w... [More]

How to add an icon to your component

by Darren Green 8 Nov 2004 14:00
When developing your own custom components for that more professional, and also practical, finish you may want to use your own icon rather than relying on the default system icon. You set this through the IconResource property of the appropriate component attribute; DtsTask for a control flow component or task, DtsPipelineComponent for a data flow component, and DtsConnection for your own connection manager. The DtsConnection IconResource functionality is not fully implemented, and you will always get the default icon. To set an icon for your component the first thing you need to do is to pr... [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]

For Loop Container Samples

by Darren Green 10 Aug 2004 14:00
One of the new tasks in SQL Server 2005 is the For Loop Container. In this article we will demonstrate a few simple examples of how this works. Firstly it is worth mentioning that the For Loop Container follows the same logic as most other loop mechanism you may have come across, in that it will continue to iterate whilst the loop test (EvalExpression) is true. There is a known issue with the EvalExpression description in the task UI being wrong at present. (SQL Server 2005 Beta 2). Timer Loop This example shows how to create a simple timer loop, such that all tasks inside the loop cont... [More]

Introduction to Expressions on Workflow

by Allan Mitchell 2 Aug 2004 14:00
SQL Server 2005 gives us loads of flexibility in our workflow management to decide how and if the following task should be executed. In this article we are going to introduce you to one of those ways and that is putting an expression into the workflow constraint itself. The Situation is as follows: We have processes that run every week day night and those processes are different based on the day of the week. What we want to do is build one package whereby we calculate what day of the week it is and our package flows through the correct steps for that particular day. Here is how ... [More]

OLE-DB Command Transformation

by Allan Mitchell 2 Aug 2004 14:00
In the newsgroups we have been asked on a number of occasions how to execute a stored procedure once for every row of input data. There's currently no really good way of doing this but in SQL Server 2005 DTS that is about to change. This article is going to show you how to take values from a source table and for every row execute a stored procedure which inserts values into another table. This package is really quite simple. It involves only one Data Flow task in the Workflow and two components in the Data Flow, an OLE DB Source adapter and the OLE DB Command Transformation. As alway... [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]

Workflow Constraints - Logical OR

by Allan Mitchell 2 Aug 2004 14:00
To do some of the stuff we wanted in SQL Server 2000 DTS we had put together some serious glue code and to be honest it wasn't pretty. One of the things that we used to have to do this way was if we wanted to implement using workflow constraints a Logical OR. Well that has changed and it is now really simple and this article is going to show you how to implement it. In SQL Server 2000 if we had this in a package Then with no playing both of these conditions would have had to evaluate to true so the left hand side would have needed to succeed and the right hand side had to fail... [More]

Popular this month

No post views yet...