How to create SSIS pkg from a table without unique IDs?

[Francorp1]

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.

 

[RiverGuy]

I'm sure you could do something in SSIS to accomplish the task, but it would be much easier and faster to just use T-SQL for this. If you need to automate or schedule it, you can always use SSIS to simply write the file--without the consolidation logic. Consider the following example:

DECLARE @T1 TABLE
(
	AcctID VARCHAR(24),
	CustomerNotes VARCHAR(MAX)
)

INSERT INTO @T1 SELECT '001', '001Note1'
INSERT INTO @T1 SELECT '002', '002Note1'
INSERT INTO @T1 SELECT '001', '001Note2'
INSERT INTO @T1 SELECT '003', '003Note1'
INSERT INTO @T1 SELECT '001', '001Note3'
INSERT INTO @T1 SELECT '002', '002Note2'
INSERT INTO @T1 SELECT '003', '003Note2'


DECLARE @T2 TABLE
(
	AcctID VARCHAR(24),
	AllNotes VARCHAR(MAX),
	Processed BIT
)

INSERT INTO @T2 SELECT DISTINCT AcctID, '', 0 FROM @T1

WHILE (SELECT COUNT(*) FROM @T2 WHERE Processed = 0) > 0
BEGIN
	DECLARE @AcctID VARCHAR(24)
	SELECT @AcctID = AcctID FROM (SELECT TOP 1 AcctID FROM @T2 WHERE Processed = 0 ORDER BY AcctID) x
	DECLARE @Notes VARCHAR(MAX)
	SELECT @Notes = ''
	SELECT @Notes = @Notes + CustomerNotes + ' ' FROM @T1 WHERE AcctID = @AcctID
	UPDATE @T2 SET AllNotes = @Notes, Processed = 1 WHERE AcctID = @AcctID
END




SELECT AcctID, RTRIM(AllNotes) FROM @T2

 

[MDXer]

The good news is the answer is Yes! The not so good news depending on your skill set is you will have to use a script task.

You can bring a sorted dataflow into the account ID checking the account id against the previous record and if it is the same account ID you append the note. Of course you do this all in VB.NET

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck