blogs.conchango.com

One of the great selling points of ETL tools such as SSIS is that is is possible to move data from source to destination without ''touching disk'' as they say. By this I mean that data can be extracted from source and inserted into a destination without having to stage it anywhere first. Transformations and aggregations can be applied to the data ...

Quite often you may want to process data from multiple files all at the same time. There are a number of options available to you if you want to do this. 1. Have a seperate source adapter for each file and amalgamate them together with the Union All component. Advantages of this approach: This works well if the files are of ...

In January of this year I posted instructions and some code to show how one could use the Recordset destination in conjunction with a source script component to negate the need for a raw file to pass data between data-flows in the same package. I didn't understand why anyone would want to do this given that raw files are much easier to ...

I have just arrived home from an excellent BI-flavoured meeting of Tony Rogerson's UK SQL Server User Group. Aside from some great content the thing I love about these events is the change to meet up with people from around the BI community - people that I've met both in person and online in the past few years.So Rastko Petrovic, Mat Stephen, ...

"I have a file that is a list of orders. Each record in the file contains the order number and the item that has been ordered along with information about that item (e.g. Quantity ordered). I want to split the file into an OrderHeader and an OrderDetail table with RI between those tables. How do I do that?" I have seen the above ...

Update below There has been, and still is, quite alot of conjecture in Conchango as to whether one should use SSIS data-flows or stored procedures for ETL. Traditionally we have implemented ETL solutions using stored procedures. DTS was used but as little more than a workflow engine, occasionally using the data-pump for importing data from ...

Whilst reading posts on the SSIS forum of late it has become evident that people are unaware of the power that expressions can provide within an SSIS package. This is understandable - alot of people are starting to use SSIS now but expressions aren't the most obvious feature. So what are they? Well the most generic explanation is that ...

Peter (sorry, don't know your surname) made a very good comment here: http://blogs.conchango.com/jamiethomson/archive/2006/02/20/2895.aspx#2903 that data-flows in a ForEach loop carry out repeated SELECTs against a data source and it would be good to be able to cache that data rather than executing the same query over and over. Its a great ...

Planning for Katmai (the next version of SQL Server) is well underway. Integration Services is still a version 1 product and as with all v1 products there are alot of improvements that can be made. I've listed a few suggestions here and here. In an attempt to try and get some of my hopes for Katmai actually into the product I've made alot of ...

It is a very common scenario to be able to load two tables in the same data-flow that have a foreign key relationship between them. The question of how to do this gets raised very often on the SSIS forum so I figured I'd try and address it here. Basically, its not possible to do this without using a workaround. There are two possible workarounds ...