March 23, 2011 at 8:00 am
this is not a new problem,
I have an ETL process that ultimately produces two a record set, which are then stored in a package variable of an object type. These two record sets need to be processed in sequence in subsequent data flows tasks.
What I need to do is to read the record sets as the data source on each of the subsequent Data Flow Task. Now what I could do is iterate over the recordset rows using a ForEachLoop Task, HOWEVER, I the recordset could potentiall contain 000's of rows, so I don't want to have to effectively use the data 1 record at time a 1000 times - it just seems like overkill.
Is there a more effecient way (and examples would be great) on how the read the through the record set in the Script Task Source component) in the secord data flow? Effectively I want the recordset presented as a source table
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
March 23, 2011 at 8:35 am
I may be missing something but I would create a temporary staging table that I could throw the record set into and then using it as a source table is straight forward.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
March 23, 2011 at 8:39 am
You can use the Recordset as a source using a script component.
You can find an example here:
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 23, 2011 at 9:01 am
Dan.Humphries (3/23/2011)
I may be missing something but I would create a temporary staging table that I could throw the record set into and then using it as a source table is straight forward.
Hi dan, I can do that, but I just trying to keep all the processing in memory to try and avoid having to read and write to tables.
It is an option, but I want to explore the option of trying to do it without a table. - found this note so long ago and it's sort of what I'm trying to do.
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
March 23, 2011 at 9:25 am
I can see what you are saying. I find it is one of those philosophical points of veiw that are so often found in the IT world. I try to avoid placing data in memory that I do not have to. particularly in an ETL type process that thends to be IO heavy to begin with. There are many many arguments that can be made from both sides of the fence but in the end we do what we are comfortable with. My belief I admit is based on a slight bit of parranoia. OK so maybe it isn't that slight and is in fact rather large.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
March 23, 2011 at 9:31 am
Koen Verbeeck (3/23/2011)
You can use the Recordset as a source using a script component.You can find an example here:
Keon,
Thanks again for another answer, it's exactly what I was looking for.
for any one else who is looking for the same solution, check the link for full details, but essentially you do the following:
1 - Create your script task as a source
2 - add the package object variable that is your record set to the ReadOnlyVariables list
3 - defin your output columns
4 - define your script as per example below.
' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub CreateNewOutputRows()
Dim olead As New Data.OleDb.OleDbDataAdapter 'Define an ADO.Net data adapter
Dim dt As New Data.DataTable 'Define an ADO.Net DataTable
Dim row As System.Data.DataRow 'Define an ADO.Net DataRow
olead.Fill(dt, Me.Variables.RSfactsrcrows) 'Populate a DataTable form the adapter using our Package Variable - RSFactSRCRows (actaully shown in the package as RS_fact_src_rows)
For Each row In dt.Rows
With SrcRecordOutputBuffer
' Add rows by calling AddRow method on member variable called "<Output Name>Buffer"
' E.g., MyOutputBuffer.AddRow() if your output was named "My Output"
.AddRow()
.loadpicktypecode = row("load_pick_type_code").ToString ' Assign our values from the Recordset to the Output fields as required
.loadpicktypedescr = row("load_pick_type_descr").ToString '
End With
Next
End Sub
End Class
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
March 23, 2011 at 9:31 am
duplicate post removed
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply