In this article we are going to assume a few things about the reader as we want to concentrate as much as possible on the Script Component itself and not the peripheral stuff
The things we will assume are:
- You are happy with adding a Data Flow task to the designer in Workflow
- You are happy to configure an OLE-DB Source adapter
- You are comfortable with configuring mappings in the Destination adapter
As usual we will show you a graphic of the Data Flow once it is completed so we know that if yours resembles ours at the end then chances are we are at the same place.
Here is our Data Flow completed
We are also going to want some tables and data with which to play so here is the sample we used.
CREATE TABLE [TempSamplesBad] ([siteID] [int] IDENTITY (1, 1) NOT NULL ,[Readings] [varchar] (50))
INSERT TempSamplesBad(Readings) VALUES('1,9,-6,3,5,7')
INSERT TempSamplesBad(Readings) VALUES('34,39,10,13,51,27')
INSERT TempSamplesBad(Readings) VALUES('1,21,2,9,7,78')
CREATE TABLE [TempSamplesGood] (
[siteID] [int] NOT NULL ,
[ReadingID] [int] NOT NULL ,
[ReadingDate] [smalldatetime],
[Reading] [int] NULL ,
PRIMARY KEY CLUSTERED
(
[siteID],
[ReadingID],
[ReadingDate]
) ON [PRIMARY]
) ON [PRIMARY
After dropping the Script component onto designer, the GUI is going to want to know how you want to use the component.
For our article we want to use it as a transform. That means that it can accept 1 or more inputs and can have 1 or more outputs. This article will keep things simple and only have one of each as shown on the opening page. After hooking up our source to the Component we can then go ahead and start to configure it.
The first page shows us our available inputs at the top and because we only have one then it shows only Input 0. Underneath we can see the columns available through that input and for our example we will need to check both of them.
If we now move onto the column properties section and expand the folders we will see our columns we just checked appear in the Input Columns Folder and we will see no columns in the Output Columns folder of our only output.
We want this component to process rows asynchronously so we need to set the SynchronousInputID property on the Output to 0.
Because we want to expose columns to the downstream SQL Server destination adapter then we are going to need to add some columns to our output. We need a column for each of our destination table columns. Remember to set the correct data types when adding the columns. When we're finished it should look something like this
Now let's move on to the script section which is where all the work is done so hit Script over on the left hand side and then Design script over on the right.
This is what should be awaiting us.
The only component in which we can work is ScriptMain as both BufferWrapper and ComponentWrapper are Read-Only.
All we need to do now is add our code.
What we need to do is loop over a row of input data extracting the multi-valued attribute values one at a time. For every value found in the column we need to add another row to the Output Buffer. In our example sample of data we had 3 input rows but will be returning 18 output rows of data.
Here is the code to do that.
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'
'The Row parameter to this method is a collection of our input attributes
'We can work with this using .Net and add to the Output our data.
'See the following
'Read our comma delimited rows into an array
Dim arr() As String = Row.Readings.Split(","c)
Dim iReadingCount As Integer = 1
'we loop around on the same row so long as there are
'delimited values to be extracted
For i As Integer = 0 To arr.Length - 1
With Output0Buffer
'add a row to the output buffer
.AddRow()
'Set the values of each of our output buffer columns
.SiteID = Row.siteID
.ReadingID = iReadingCount
.DateOfReading = System.DateTime.Now
.Reading = CType(arr(i), Integer)
'Increase the reading count by 1
iReadingCount += 1
End With
Next
End Sub
This component is really powerful and will have a huge amount of uses.