CODEWITHSUNDEEP
.netsql-serverssis
Robert
Robert

Reputation: 1638

SSIS and Normalizing Data in TXT file

I'm learning SSIS and I have a question about how to normalize existing data in a text file.

In my directory I have a text file for each state in the United States so AK.txt and AL.txt has the following structure:

AK,F,1910,Mary,14 AL,F,1910,Mary,875

This stands for state,sex,year,name,frequency

If I have the following tables:

What transformation in SSIS would I use to put each data element in it's respective table?

Upvotes: 0

Views: 374

Answers (2)

Nick.Mc
Nick.Mc

Reputation: 19184

A. To firstly capture the multiple files, here are two options:

  1. Use a For Each File iterator to loop through each file and load into something (a rowset object , a raw file or a staging table), then use that as a source for part B

  2. Alternatively create an flat file source for each file and join them up with a union operator (not recommended)

B. To get that single data flow into multiple tables

Use a multicast to cast the data out to 5 streams. Then order and distinct them to get distinct records then load them into the tables.

My personal preference is to load into a staging table and do everything via SQL after that.

Upvotes: 1

TsSkTo
TsSkTo

Reputation: 1420

If your file is comma delimited "state,sex,year.." easiest way is to use the flat file source. You can set it up to use a comma as a column delimiter and a newline as a row delimiter. Make sure to set the right datatypes under advanced as well. The output is then a column for each delimited field. Then use one of the destinations(OLE DB..) and put the stuff in yout tables.

enter image description here

Let me know if you have any questions

Upvotes: 0

Related Questions