Flat File to Relational Schema

Hello-

I am a complete newbie with SSIS. I started working with Flat Files today and have made some progress.

I have a task that requires me to pull out data from a | delimeted list with 160+ columns in a row. I am working with Movie and Entertainment data. So each movie has a number of actors associated with it.
For example:
MOVIEID|Zoolander|...|...|...|...|..................|Owen Wilson|Ben Stiller|Will Ferrel|...|...|...............|

I would like to take all the actors out of the middle of this long line of columns and place them in an Actors table with the movieID. However, when I look at Flat File Source, all I see is my X number of columns in the one row for all the actors (actor1-20).

Is there a way to make a new flat file that references the other flat file and can split up a certain amount of columns by rows inside of the middle of an existing row?

I hope that makes sence. Basically I would like to make a relational database schema out of ONE Flat File.

Thanks for your consideration,
ScottA good way to handle a problem like this would be to create a Flat File Reader. Input the records into a Multicast. Select the columns that you want and insert them into a Flat File destination. Alternatively, you can insert them directly into a table depending on your desired methodology.

Let me know if you need more help. I'll be happy to set up a sample job for you explaining this functionality.

Wes D|||

Check out the Unpivot Transformation. http://msdn2.microsoft.com/en-us/library/ms141723.aspx

Donald

|||Donald,

Thanks for the tip! That's what I am looking for. Although I need it to go to a different table, rather than normalizing it inside the same DataSet.

Is there anyway to branch off into a different DataSet?

And if so, is there anyway to give the primary key of the original DataRow to it as a join condition?|||

Have a look at the Multicast component.

Donald