background preloader

Data Flow Transformations

Facebook Twitter

Lookup cache modes - SSIS Team Blog. Over the past couple of months I've been putting together a presentation on the Lookup Transform.

Lookup cache modes - SSIS Team Blog

I presented most of it as a Chalk Talk at the MS BI Conference last week, and from the evaluation scores, it seems like it was pretty well received. I'll be splitting up some of its content into a series of blog posts over the next little while. If you're interested in seeing the whole talk, it will also be shown at the SSWUG Virtual Conference in November. The most important setting of the Lookup Transform is the Cache Mode - it can greatly impact your data flow performance, and affects overall package design. Because of its importance, we made it the first thing you see in the new 2008 Lookup UI. This blog post describes the three cache modes, how they work, and best practices around using them.

Full Cache The default cache mode for the lookup is Full cache. When to use this cache mode Keys to using this cache mode Partial Cache No Cache. Andy Leonard : SSIS Expression Language and the Derived Column Transformation. Introduction This post is part of a series titled An Introduction to the SSIS Expression Language.

Andy Leonard : SSIS Expression Language and the Derived Column Transformation

In this post, I demonstrate the SSIS Expression Language and the Derived Column Transformation. To build the demo project described in this article you will need SSIS 2005 and the AdventureWorks sample database (which can be downloaded at Add To The Demo Project If you have not already done so, create an SSIS project named ExpressionLanguageDemo. Once the project is created, open Solution Explorer and rename Package.dtsx - change the name to DerivedColumn.dtsx. Drag a Data Flow Task onto the Control Flow. Set the Data Access Mode to SQL Command and enter the following T-SQL statement into the SQL Command Text textbox: Select ContactID ,Title ,FirstName ,MiddleName ,LastName ,EmailAddressFrom Person.Contact Click OK to close the editor. Learning To Derive Drag a Derived Column Transformation onto the Data Flow canvas and connect the OLE DB Source Adapter to it using a Data Flow Task:

Derived Column Transformation. The Derived Column transformation creates new column values by applying expressions to transformation input columns.

Derived Column Transformation

An expression can contain any combination of variables, functions, operators, and columns from the transformation input. The result can be added as a new column or inserted into an existing column as a replacement value. The Derived Column transformation can define multiple derived columns, and any variable or input columns can appear in multiple expressions. You can use this transformation to perform the following tasks: Concatenate data from different columns into a derived column. You can configure the Derived column transformation in the following ways: Provide an expression for each input column or new column that will be changed. Merge multiple data sources with SQL Server Integration Services. ProblemWhen loading data into SQL Server you have the option of using SQL Server Integration Services to handle more complex loading and data transforms then just doing a straight load such as using BCP.

Merge multiple data sources with SQL Server Integration Services

One problem that you may be faced with is that data is given to you in multiple files such as sales and sales orders, but the loading process requires you to join these flat files during the load instead of doing a preload and then later merging the data. What options exist and how can this be done? SolutionSQL Server Integration Services (SSIS) offers a lot more features and options then DTS offered. One of these new options is the MERGE JOIN task. With this task you can merge multiple input files into one process and handle this source data as if it was from one source. Let's take a look at an example of how to use this.

Here we have two source files an OrderHeader and an OrderDetail. OrderHeader source file. OrderDetail source file Orders table. Merge Join Transformation. The Merge Join transformation provides an output that is generated by joining two sorted datasets using a FULL, LEFT, or INNER join.

Merge Join Transformation

For example, you can use a LEFT join to join a table that includes product information with a table that lists the country/region in which a product was manufactured. The result is a table that lists all products and their country/region of origin.