Integration Services Data Flow Transformations Tips

The Conditional Split transform is one of the most common transforms used in package development. Although using this transform is very simple from a technical perspective, there are some very simple points which are easy to overlook which could cause issues with data. In this tip we will discuss two of these common mistakes.

The SQL Server Integration Services (SSIS) Merge Join transformation requires input data streams to be sorted for its operation. If during the design time you try to join two non-sorted inputs, it won't allow you to do so. One of the options is to sort the data in the database rather than using the Sort transformation in SSIS, which is a more speedier option. But if the sort order is changed unknowingly, SSIS would not raise an exception and the results would be unpredictable.

I have some sales forecast data that I get from the business users in an Excel spreadsheet. I need to load this data into a SQL Server database table. The forecast contains product categories on the rows and the sales forecast for each month is on the columns. I'd like to use SQL Server Integration Services (SSIS) to perform this recurring task. How can I transform this data into a table that has Category, Month and Sales Forecast columns?

Sometimes we need to import thousands of images to SQL Server. This example shows how to import a list of images to SQL Server using SQL Server Integration Services.

Conditional Split is one of the most commonly used transforms in any SSIS Package development routine. The limitation with conditional split is that any record that satisfies the first condition from the conditions list is routed to its corresponding path exclusively. In a logical sense, a record might satisfy more than one condition and one might want the record to be routed to all paths for processing, but with the Conditional Split transform this is not possible. We need a more intelligent conditional split where we can selectively route the records to more than one output path. In this tip we will look at how to facilitate this intelligent conditional split.

One of the new SQL Server 2008 features in SSIS is an enhanced Lookup transform that includes the ability to cache the lookup data locally. Can you provide the details of this new functionality and an example of how to use it?

When 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. 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?

In a data warehousing scenario, it's not rare to replicate data of a source table to multiple destination tables, sometimes it's even required to distribute data of a source table to two or more tables depending on some condition. For example splitting data based on location. So how we can achieve this with SSIS?

Sometimes we need to import thousands of images to SQL Server. This example shows how to import a list of images to SQL Server using SQL Server Integration Services.

When 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. 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?

Conditional Split is one of the most commonly used transforms in any SSIS Package development routine. The limitation with conditional split is that any record that satisfies the first condition from the conditions list is routed to its corresponding path exclusively. In a logical sense, a record might satisfy more than one condition and one might want the record to be routed to all paths for processing, but with the Conditional Split transform this is not possible. We need a more intelligent conditional split where we can selectively route the records to more than one output path. In this tip we will look at how to facilitate this intelligent conditional split.

In a data warehousing scenario, it's not rare to replicate data of a source table to multiple destination tables, sometimes it's even required to distribute data of a source table to two or more tables depending on some condition. For example splitting data based on location. So how we can achieve this with SSIS?

One of the new SQL Server 2008 features in SSIS is an enhanced Lookup transform that includes the ability to cache the lookup data locally. Can you provide the details of this new functionality and an example of how to use it?

I have some sales forecast data that I get from the business users in an Excel spreadsheet. I need to load this data into a SQL Server database table. The forecast contains product categories on the rows and the sales forecast for each month is on the columns. I'd like to use SQL Server Integration Services (SSIS) to perform this recurring task. How can I transform this data into a table that has Category, Month and Sales Forecast columns?

The Conditional Split transform is one of the most common transforms used in package development. Although using this transform is very simple from a technical perspective, there are some very simple points which are easy to overlook which could cause issues with data. In this tip we will discuss two of these common mistakes.

The SQL Server Integration Services (SSIS) Merge Join transformation requires input data streams to be sorted for its operation. If during the design time you try to join two non-sorted inputs, it won't allow you to do so. One of the options is to sort the data in the database rather than using the Sort transformation in SSIS, which is a more speedier option. But if the sort order is changed unknowingly, SSIS would not raise an exception and the results would be unpredictable.

Sometimes we need to import thousands of images to SQL Server. This example shows how to import a list of images to SQL Server using SQL Server Integration Services.

The Conditional Split transform is one of the most common transforms used in package development. Although using this transform is very simple from a technical perspective, there are some very simple points which are easy to overlook which could cause issues with data. In this tip we will discuss two of these common mistakes.

Conditional Split is one of the most commonly used transforms in any SSIS Package development routine. The limitation with conditional split is that any record that satisfies the first condition from the conditions list is routed to its corresponding path exclusively. In a logical sense, a record might satisfy more than one condition and one might want the record to be routed to all paths for processing, but with the Conditional Split transform this is not possible. We need a more intelligent conditional split where we can selectively route the records to more than one output path. In this tip we will look at how to facilitate this intelligent conditional split.

In a data warehousing scenario, it's not rare to replicate data of a source table to multiple destination tables, sometimes it's even required to distribute data of a source table to two or more tables depending on some condition. For example splitting data based on location. So how we can achieve this with SSIS?

The SQL Server Integration Services (SSIS) Merge Join transformation requires input data streams to be sorted for its operation. If during the design time you try to join two non-sorted inputs, it won't allow you to do so. One of the options is to sort the data in the database rather than using the Sort transformation in SSIS, which is a more speedier option. But if the sort order is changed unknowingly, SSIS would not raise an exception and the results would be unpredictable.

I have some sales forecast data that I get from the business users in an Excel spreadsheet. I need to load this data into a SQL Server database table. The forecast contains product categories on the rows and the sales forecast for each month is on the columns. I'd like to use SQL Server Integration Services (SSIS) to perform this recurring task. How can I transform this data into a table that has Category, Month and Sales Forecast columns?

One of the new SQL Server 2008 features in SSIS is an enhanced Lookup transform that includes the ability to cache the lookup data locally. Can you provide the details of this new functionality and an example of how to use it?

When 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. 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?