Looping in SSIS in a table without unique IDs

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Looping in SSIS in a table without unique IDs

Author

Topic

Franco_1
Starting Member

2 Posts

Posted - 2008-10-16 : 14:26:17

Table Structure:

CustomerNotes: AcctID:
P.D. CHEQUES RECEIVED 01-010153909
PD CHEQUES EXPIRED 01-010153909
POTENTIAL NIS 01-010153909
REMOVE FROM PREDICTIVE 01-010153909
CHQ IS GOOD TO POST 01-010694075
ABLE NOW LOCATED PLEASE C 01-010694075
ALL CONSUMER TO MAKE SURE 01-010694075
ATED 04/12/07 WAS UNLOCAT 01-010694075
CALL CONNECTED 01-010694075
CALL DISCONNECTED 01-011060614
AND CALL NOT GOING THROU 01-011060614
AND NO CONS THERE IS WRN 01-011060614
NAME & (ZACH'S) BUT NL 01-011060614
S NO TELE SO CN'T REP CON 01-011060614
See Purged Notes--------- 01-011060614
SHOWS CFI AT ONE TME CON 01-011060614
DBI NOT HOME 01-011360372
CALL CONNECTED 01-011360372
MANAGER REVIEW 01-011360372
NO ANSWER 01-011360372
POTENTIAL NIS 01-011360372
REMOVE FROM PREDICTIVE 01-011360372

Would it be possible to use SSIS Package to create a file that will have two columns: AcctID and Notes from the table structure above. So, instead of repeating the acctIDs (as above)there will be one acctID, and all the notes for that account concatenated with spaces between each note in the second column in the newly created destination file.

Note: Table structure looks funny from preview window. All acctIDs begin with 01.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-17 : 09:57:28

You need a data flow task in your ssis for that. inside data flow task use OLEDB Source and Flat file destination. Connect source to your table and give destination as your file. Select SQL Command option for OLEDB source and give query below in it

SELECT t.AcctID,LEFT(nl.noteslist,LEN(nl.noteslist)-1)
FROM
(SELECT DISTINCT AcctID FROM YourTable)t
CROSS APPLY(SELECT CustomerNotes +',' AS [text()]
            FROM yourtable
            WHERE AcctID=t.AcctID
            FOR XML PATH(''))nl(noteslist)

click preview to see results and then map the columns to destination file columns. Run package and see the file getting populated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources