Preview of SSIS in SQL Server 2008 (Presentation)

by Allan Mitchell 15 Oct 2007 14:00
Download the slides and samples from the Preview of SSIS in SQL Server 2008 presentation, first presented at the SQLBits 2007 Community Conference. In this session we looked at some of the new and improved features of SSIS coming in SQL Server 2008. Highlights include: Looking at the new pipeline limiter The new threading engine The arrival of C# as a scripting language option Change Data Capture(CDC) Presentation & Samples (360KB) Preview of SSIS in SQL Server

Multicast Transform and Threading in SQL Server 2008

by Allan Mitchell 19 Sep 2007 14:00
The Multicast transform in SSIS 2005 enables us to take 1 input dataset and from it generate n output datasets that are an exact copy of the input dataset which is extremely useful at times and we have used it on a number of projects. The downside is that those outputs are synchronous outputs and therefore are on the exact same thread as the input dataset. Normally a Synchronous output is good as no memory has to swap buffers and it is exceptionally quick. A problem arises if we have on one or more of those outputs a blocking Asynchronous transform. When this happens processing of the other ou... [More]

Pipeline Memory Limiter

by Allan Mitchell 10 Sep 2007 14:00
Michael Entin has blogged about something that you see now, well you feel the effects anyway, in SQL Server 2005 but it isn't as visible as it should be but is really important. Backpressure is an interesting internal memory limiter inside SSIS and in SQL Server 2008 you get to see when the limiter kicks in. For a detailed explanation see Michael''s blog entry here but we wanted to show you how to reproduce it very simply. Here is a package we are going to use to show the limiter kick in and how SQL Server 2008 shows us it taking effect: Inside the Script Component a... [More]

Where To Find Integration Services Packages in SQL Server

by Allan Mitchell 27 Aug 2007 14:00
We see this coming up quite a bit on newsgroups and even have people eMailing about it so we though we would put up an article explaining where to find packages when saved to SQL Server. If you want to view them visually in SQL Server Management Studio then you will need to connect to the Integration Services Service for the Server on which you have stored the packages. Below is an example of us connecting to the service by first opening up Object Explorer and then connecting to the Service by choosing it from the dropdown menu and then using Integrated security (no choice here) to... [More]

Script Task Breakpoints in SQL Server 2008

by Allan Mitchell 23 Aug 2007 14:00
One of the things coming from our look at SQL Server 2008 Integration Services is that you are not going to see a lot of change visually. What you are going to see is things going on underneath the covers that makes SSIS perform better, and improved informational messages from the components. Just such a change is seen in the Script task. In SQL Server 2005 we drop onto the designer two Script tasks and join them with workflow. We can then go into the Scripts of the tasks and set breakpoints in the code of each. The Control Flow should now look something like this.   &... [More]

The Execute SQL Task

by Allan Mitchell 9 Oct 2005 14:00
In this article we are going to take you through the Execute SQL Task in SQL Server Integration Services for SQL Server 2005 (although it appies just as well to SQL Server 2008).  We will be covering all the essentials that you will need to know to effectively use this task and make it as flexible as possible. The things we will be looking at are as follows: A tour of the Task. The properties of the Task. After looking at these introductory topics we will then get into some examples. The examples will show different types of usage for the task: Returning a single value from a SQL q... [More]

Comparing Overhead on the Execution Methods

by Allan Mitchell 7 Oct 2005 14:00
So we got to playing with some of the ways that you can execute an SSIS package and got kind of curious about any overhead associated with the execution methods. From these thoughts came a few tests and here are our findings. We decided to look at performance over two different pipelines. We also had to decide on a "Control" so that we had something against which to measure. In the tests the "Control" was achieved by executing each pipeline in the Business Intelligence Development Studio with debugging (F5). Pipeline 1 This pipeline is a straight Source ... [More]

Meaning of the On Success Workflow Constraint

by Allan Mitchell 3 Oct 2005 14:00
What do you think happens in the following trivial package (The first task is disabled)? Nothing. Error. Everything except for the first task executes. Let's take a look Suprised? Well if like us you are coming from a DTS background then it most likely will shock you. In DTS this package would not have got off first base. Explanation Follows: The meaning of the On Success Constraint should not be read as such. It should be read as No Errors Occured. If the task does not execute i.e. if it is disabled then the TaskHost posts a result of No Err... [More]

Shredding a Recordset

by Allan Mitchell 15 Jun 2005 14:00
Doing what to a recordset? Shredding a recordset in this instance means that we are going to show you how to take a recordset produced in your SSIS package, loop over the rows in that recordset, break apart the columns and do something with them. This is really useful when you want to preform an action on a row of data for every row of data just like we are going to do here. Sure we could use an ExecuteSQL task to get the recordset as well but that does limit our choices of source data whereas doing it in the pipeline does not. Something useful we hope. Retrieving the Recordset There ... [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]

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]

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]

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]

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]

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]


Comment RSS