Given that I'm not good at SSIS, I did this in Access, because it's what I know, and it works. (So it is good for some things!)

In my source table "srcTable", the structure was like this:

CREATE TABLE srcTable (

ID INT IDENTITY(1,1) PRIMARY KEY,

FieldA VARCHAR(10),

FieldB VARCHAR(10),

FieldC VARCHAR(10),

FieldD VARCHAR(10)

);

Then my destination table was like this:

CREATE TABLE destTable (

ParentID INT NOT NULL,

TheValue VARCHAR(10) NOT NULL);

Then I created a module to do the dynamic SQL dirty work...

Option Compare Database

Option Explicit

Public Sub NormalizeData()

Dim tdf As DAO.TableDef

Dim i As Integer

Dim strSQL As String

DBEngine(0)(0).TableDefs("srcTable").Fields.Refresh

Set tdf = DBEngine(0)(0).TableDefs("srcTable")

For i = 1 To tdf.Fields.Count - 1

strSQL = "INSERT INTO destTable ( ParentID, TheValue ) SELECT srcTable.ID, srcTable.[" & tdf.Fields(i).Name & "] FROM srcTable WHERE srcTable.[" & tdf.Fields(i).Name & "] IS NOT NULL;"

Debug.Print strSQL

DBEngine(0)(0).Execute strSQL

Next i

Set tdf = Nothing

End Sub

Basically, it grabs the first column name (tdf.fields(0).Name) and the nth column (tdf.fields(i).Name) and inserts the non-null values into the destination table. then it processes the next column until there are no more columns to process.

For the SQL Smarties, I would love to know how to do this in T-SQL... I know about the sys.columns stuff, but not sure how to use it... so I resorted to something I know and that works. =)