October 14, 2010 at 11:18 am
Hi Community
I am relatively new to SSIS and am currently using VS BIDS 2008 to develop import packages for SQL Server 2008.
The Situation:
I have a log table from a legacy database that has one entry spread across 14 columns and multiple rows (1-99). Each log begins with a header record that contains the number of rows for the entry. It looks something like this:
LOG_SIZELGN_NARRATIVE_1LGN_NARRATIVE_2
<1> <NULL> <NULL>
<NULL><first etnry> <second entry>
The Task:
We want to combine all the narrative records for each log into one long text field and then place it in the header row, producing one record. I have used several approaches so far.
The most successful attempt was with the Script Component (transformation) using the PipelineBuffer combined with a For Loop container. Unfortunately, I don't think For Loop was designed to process 6 million records : /. I can find the log_size and concatenate the narratives accordingly. My problem is placing the output in the correct row in the data flow and then deleting narrative records that have been combined.
Where it Stands:
The records that are concatenated are not deleted in the dataflow (although i would like to), instead they are marked with a unique string that we can use to delete after import. The combined narrative is placed in the last row for each log. My boss doesn't like this approach so much.
Any answers or suggestions will be greatly appreciated!
Thanks
October 14, 2010 at 11:22 am
In your query of the log data, can you use the "For XML Path" trick to concatenate it all into a string?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 18, 2010 at 11:16 am
I've used XML auto for exporting data from tables and then importing them with a cursor. You think the for xml would work for 6 million records? Maybe you could be so kind as to show me a little example? 🙂
October 25, 2010 at 1:10 pm
For anyone who may have encountered a similar task, I found a viable solution. Using the script transformation component. The following puts three rows (date, notes, and contact) on one line. A conditional split is used after this task in the data flow to eliminate unused records.
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Static strRowz As String
Static log_notation, contact_code As String
Dim audit_id, intake, worker As String
Static contact_dt As Date
If Row.LOGINTNO_IsNull = False Then
intake = Row.LOGINTNO
Else
intake = ""
End If
If Row.AUDITUSERID_IsNull = False Then
audit_id = Row.AUDITUSERID
Else
audit_id = ""
End If
If Row.LOGWORKERID_IsNull = False Then
worker = Row.LOGWORKERID
Else
worker = ""
End If
If Row.LOGSIZE_IsNull = True Then
Dim strN As String
strN = Row.notation
strRowz = strRowz + strN
End If
If Row.LOGSIZE_IsNull = False Then
Try
If strRowz <> "" Then
Row.Note = strRowz.ToString
Row.Audituser = audit_id
Row.Intakeno = intake
Row.Workerid = worker
Row.Contacttypecode = contact_code
Row.log = log_notation
Row.contactdt = contact_dt
log_notation = ""
contact_code = ""
contact_dt = ""
End If
Catch ex As Exception
End Try
If Row.LOGCONTACTDATETIME_IsNull Then
contact_dt = "2010-10-25"
Else
contact_dt = Row.LOGCONTACTDATETIME
End If
contact_code = Row.LOGCONTACTTYPECODE
log_notation = Row.LOGNOTATION
strRowz = ""
End If
End Sub
End Class
cheers!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply