SSIS, splitting a single row into multiple rows

Question

My problem is as follows. I have a CSV file (~100k rows) containting history information with the column format of:

ID1,History1,ID2,History2...ID110,History110

Each row may have anywhere between 0 and 110 history entries. Each separate entry requires a stored procedure to be called.

If there were a small number of possible entries per row, I imagine the way to do this would be to transform the data using a script, and send it to a unique path. Creating 110 paths would probably work, but isn't very elegant (and quite time consuming).

What would the best way to approach this be?

Answer 1

Just load the data (raw csv unchanged, one row per file line) into a staging table. Then, call a stored procedure that will use a string splitter to break up and loop over the staging table rows and call your other procedure for each history entry.

see: Arrays and Lists in SQL Server 2005 and Beyond

also see this previous answer: http://stackoverflow.com/questions/2341374/sql-comma-delimted-column-to-rows-then-sum-totals/2341510#2341510

Answer 2

If you want to solve this in SSIS without the staging tables, you could create a destination script component. You could use switch statement or hashtable to lookup the right sproc to execute for the data row.

It is unclear whether this is a better solution then the staging table approach above; but it is an alternative.

Answer 3

I know you already accepted an answer, but couldn't you use an Unpivot task to achieve what you wanted to do here?