Professional SQL Server™ 2005 Integration Services

5.2. Typical Mainframe ETL with Data Scrubbing

With the basic ETL out of the way, you will now jump into a more complex SSIS package that attempts to scrub data. You can start this scenario by downloading some public data from the Florida Department of Corporations at ftp://dossftp.dos.state.fl.us/public/doc/cor/. Manually download the file 010305c.dat from the FTP site and place the file into a directory called C:\SSISDemos.

In this scenario, you run a credit card company that's interested in marketing to newly formed domestic corporations in Florida. You want to prepare a data extract each day for the marketing department to perform a mail merge and perform a bulk mailing. Yes, your company is an old-fashioned, snail-mail spammer.

Luckily the Department of State for Florida has an interesting extract you can use to empower your marketing department. As with most extracts that come from a legacy system, this extract unfortunately is in a fixed-width format. This means that the file is not delimited by any symbol, and you'll have to manually configure each column's width. In these types of events, your mainframe group or the third party would send you a record layout for the file extract. In this case, you can see the record layout here: http://www.sunbiz.org/corpweb/inquiry/corfile.html. The record layout for this extract would look like this:

01 ANNUAL_MICRO_DATA_REC. 03 ANNUAL_COR_NUMBER PIC X(12). 03 ANNUAL_COR_NAME PIC X(48). 03 ANNUAL_COR_STATUS PIC X(01). 03 ANNUAL_COR_FILING_TYPE ...