My first inclination would be to use a script component (e.g. C#) as source in a Data Flow Task. The script would just read the column contents, parse the individual records and fields, and send records down the pipeline. The individual fields (perhaps strongly-typed) can be defined in the output record definition of the designer.
https://msdn.microsoft.com/en-us/library/ms136060.aspx contains an example of parsing a CSV file and processing a SQL query. You can combine the two examples for your needs. Below is an example of this. I don't have time to test right now but it should get you started.
public class ScriptMain:
UserComponent
{
IDTSConnectionManager100 connMgr;
SqlConnection sqlConn;
SqlDataReader sqlReader;
public override void AcquireConnections(object Transaction)
{
connMgr = this.Connections.MyADONETConnection;
sqlConn = (SqlConnection)connMgr.AcquireConnection(null);
}
public override void PreExecute()
{
SqlCommand cmd = new SqlCommand("SELECT CsvContents FROM dbo.CsvImportTable;", sqlConn);
sqlReader = cmd.ExecuteReader();
}
public override void CreateNewOutputRows()
{
while (sqlReader.Read())
{
{
string csvContents = sqlReader.GetString(0);
CreateNewOutputRowsFromCsv(csvContents);
}
}
}
public void CreateNewOutputRowsFromCsv(string csvContents)
{
string nextLine;
string[] columns;
char[] delimiters;
delimiters = ",".ToCharArray();
nextLine = textReader.ReadLine();
while (nextLine != null)
{
columns = nextLine.Split(delimiters);
{
MyAddressOutputBuffer.AddRow();
MyAddressOutputBuffer.AddressID = columns[0];
MyAddressOutputBuffer.City = columns[3];
}
nextLine = textReader.ReadLine();
}
}
public override void PostExecute()
{
sqlReader.Close();
}
public override void ReleaseConnections()
{
connMgr.ReleaseConnection(sqlConn);
}
}
