Concatenating a text column in SSIS?

[JAPixley]

I have to import a bunch of spreadsheets (example below) that all have the same format into a SQL 2005 table. Some records have only one SEQ_NUM while others may have as many as twelve; it might be possible to end up with more than that.

   Record  SeqNum  Text        RanData1  RanData2  RanData3
   10548   1       This is a   16548     LR37A     19534.54
   10548   2       screwy way  NULL      NULL      NULL    
   10548   3        to mainta  NULL      NULL      NULL    
   10548   4       in a recor  NULL      NULL      NULL    
   10548   5       d.          NULL      NULL      NULL

I need the final table to look like this:

   Record  NewText                                     RanData1  RanData2  RanData3
   10548   This is a screwy way to maintain a record.  16548     LR37A     19534.54

I've figured out how to do this manually with a temp table and a function, but now I want to automate the process in SSIS and the function won't work the way I'm trying to use it. Anyone out there have an idea how to accomplish this via SSIS without cursors or temp tables?

 

[Qik3Coder]

What you have here is a stealthy cross tab.

search this site's faq, and pior postings for cross tabs (dynamic).

You've got questions and source code. We want both!

 

[simian336]

I have never done in SSIS but here is a pretty good example of one that I use. For futher info look up FOR XML Path. There are lots of examples out there.

SELECT DISTINCT s1.[ActBrch],
STUFF(
(SELECT ', ' + s2.[SVCID]
FROM [Meta].dbo.srvmast AS s2
WHERE s2.[ActBrch] = s1.[ActBrch]
FOR XML PATH(''))
, 1, 1, '') AS CODES
FROM [400_Metafile].dbo.yad_srvmast AS s1
ORDER BY s1.[ActBrch]


You can remove the ', ' as you don't need a comma.

Simi