Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Concatenating a text column in SSIS?

Status
Not open for further replies.

JAPixley

Technical User
May 6, 2004
15
US
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.
Code:
   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:
Code:
   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?
 
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!
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top