Have you ever faced the challenge of dealing with an input file that contains columns with multiple types of content? For example, a single column may contain addresses, emails, names, or city state zip information. This can be a common problem when working with data from various sources.

In a recent project involving banking data, we needed to load data into a Customer Dimension and MDM Tool. The data was not properly organized, with each input column containing a different part of the address, name, company name, or email. Our goal was to send the data through an address correction tool, but simply concatenating all the columns was not a viable solution.

So, how did we tackle this problem? We used SQL Server’s capabilities to identify patterns in the data and dynamically map it to the appropriate columns. Here’s an overview of our approach:

Data Profiling and Pattern Identification

First, we needed to identify if a column matches a particular pattern (address, email, city state zip, etc.). We decided to use Regular Expressions in SQL Server Integration Services (SSIS) to accomplish this. Regular Expressions are powerful tools for pattern matching and can be easily implemented in SSIS using a Script Component.

In the Script Component, we wrote code to apply Regular Expressions to each input column and determine if it matches a specific pattern. For example, we used Regular Expressions to identify addresses, emails, and city state zip information. We also used Regular Expressions to handle special cases like PO Box addresses.

Dynamic Mapping with Derived Columns

Once we identified the patterns in the data, we needed to map it to the appropriate columns. We used a Derived Column Transform in SSIS to achieve this. The Derived Column Transform allows us to create new columns based on conditions and expressions.

For example, let’s say we have columns ADDR_1_Address, ADDR_2_Address, ADDR_3_Address, and so on. We can use the following expression in the Derived Column Transform to populate the Address column:

ADDR_1_Address == TRUE && ADDR_1_CityStateZip == FALSE ? ADDR_LNE_1_TXT :
ADDR_2_Address == TRUE && ADDR_2_CityStateZip == FALSE ? ADDR_LNE_2_TXT :
ADDR_3_Address == TRUE && ADDR_3_CityStateZip == FALSE ? ADDR_LNE_3_TXT :
ADDR_4_Address == TRUE && ADDR_4_CityStateZip == FALSE ? ADDR_LNE_4_TXT :
ADDR_5_Address == TRUE && ADDR_5_CityStateZip == FALSE ? ADDR_LNE_5_TXT :
ADDR_6_Address == TRUE && ADDR_6_CityStateZip == FALSE ? ADDR_LNE_6_TXT :
ADDR_7_Address == TRUE && ADDR_7_CityStateZip == FALSE ? ADDR_LNE_7_TXT :
ADDR_8_Address == TRUE && ADDR_8_CityStateZip == FALSE ? ADDR_LNE_8_TXT :
"No Address"

This expression checks each column to see if it contains an address and if it is not a city state zip. If the condition is true, it populates the Address column with the corresponding value from the input columns. If none of the conditions are met, it sets the Address column to “No Address”.

Conclusion

Handling input files with multiple types of content can be a challenging task. However, by leveraging SQL Server’s capabilities, such as Regular Expressions and Derived Columns, we can efficiently organize and map the data to the appropriate columns.

By following a similar approach, you can handle complex data scenarios and ensure accurate data processing in your SQL Server projects.

Remember, “Do, or do not. There is no try.” – Ira Warren Whiteside