O'Reilly logo

Professional SQL Server™ 2005 Integration Services by Mike Murphy, Haidong Ji, Jason Gerard, Erik Veerman, Andy Leonard, Kathi Kellenberger, Douglas Hinson, Darren Green, Allan Mitchell, Brian Knight

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required