Expert SQL Server™ 2005 Integration Services

10.1. Unusual Data Flow Scenarios

It never fails. Just when you think you've seen the worst possible extract, someone tops it. We've been consulting for years and we have competitions to see what the worst extract is. The examples you're about to see talk about how you can clean these horrible extracts. Oftentimes, you may have purchased a third-party product where you're not allowed to have direct access to the data. Instead, you must generate reports or access the data through web services and make this your data source. Painful as it may seem, there is a solution for solving almost any strange data feed in SSIS. In most cases, you won't even have to break out the Script Component. This chapter has many examples that explain how to handle this bad data, but the examples won't walk you through step-by-step on how to do the basics such as how to create a proper connection manager.

10.1.1. Creating Rows from Columns

As you know, mainframe screens rarely conform to any normalized form. For example, a screen may show Bill to Name, Ship To Customer, and Dedicated To Name fields. Typically, the data source would store these three fields as three columns in a file (such as VSAM). So, when you receive an extract from the mainframe, you may have three columns, as shown in Figure 10-1.

Figure 10-1. Extract from the mainframe

Your goal is to load this file into a Customer table in SQL ...