18 Jan 2011 21:59
I have been thinking a lot recently about what it would be like to have StreamInsight and SSIS working together. Well the CAT team have produced a paper on some of our options here. Here are some of my thoughts. There is of course a slight mismatch in their types of usage. StreamInsight is an Event Stream processing engine capable of operating on new data in the sub second timeframe. The engine allows you to do real time analytics and take decisions on events that have potentially only just happened. SSIS on the other hand is a batch processing engine. In... [More]
28 Oct 2008 08:52
Here is an example of how to do something that in DTS we would have had to do some coding in Script. In our packages we often find the need to follow one path or another in the Control flow based on some condition that exists at runtime. In the example below we use a variable value to decide what path to take. Here is the control flow along with the variables pane. The icons on the workflow indicate we have expressions thereon. The expression on the workflow constraint between tasks 1 and 2 is shown here. As you can see we want the workflow to be followed if our variabl... [More]
28 Oct 2008 08:43
All Containers include a Disable property. This is what is used for the right-click Disable/Enable functionality you may have used when developing packages in the designer. Expressions are evaluated at run-time and can be used to set properties, so it follows that you could use an expression on the Disable property to control execution of the container. This will not always work. By design, the Disable property is evaluated quite early on during package execution. This may be before you have set a variable that is subsequently used within your Disable property expression. This may caused unex... [More]
3 Oct 2005 14:00
What do you think happens in the following trivial package (The first task is disabled)?
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.
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]
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]
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]
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]
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).
This example shows how to create a simple timer loop, such that all tasks inside the loop cont... [More]
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]
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]