The Script Component as a Transformation

by Allan Mitchell 2 Aug 2004 14:00

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

WorkComp

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.

scriptQ

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.

Edp1

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.

syncID

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

syncID

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.

BlankEd

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.

Comments (8) -

12/11/2008 3:17:10 PM #

Daniela

Hi Allan,

I am new to SSIS. I am having trouble trying to use an excel connection manager that has parameter in the ExcelFilePath. The Excel connection manager works fine in Sql tasks. I think that the connection with parameter is not working with the Excel
Destination because we have to select the name of the Excel Sheet. When we
are in designing, the variable doesn't have a value so the task can't find the
file to get the name of the sheet. Is there another way to pass the name of
the sheet without having to select it? Do you know if I could solve this problem with a script component? Where can I get more information about it?

I posted a question in the Microsoft Discussion Group: Using Parameters in Connection Managers in SQL Server Integration Services.

Any ideas?

Thanks
Daniela

Daniela United States

12/12/2008 6:17:03 AM #

Allan Mitchell

So the thing is Daniella SSIS will need to know about the connection at design time to be able to do its work.  I typically use a local version of the exact same structure and type so i can do my designing and then I expression the CM.  if the variable does not hold a value in this case yes SSIS gets upset.
Have a look at a property called "Delay Validation"

http://www.sqlis.com/post/What-is-Validation.aspx

This tells SSIS that the Data Flow Task/Cm will be ready by the time you get there but not yet.

Allan Mitchell Germany

6/10/2009 2:59:07 PM #

fs

Thanks for the excellent article.  I had been googling for the last 3 days for a suitable SSIS Scripting example and luckily I came across this.
Keep up the good work.
FS

fs Australia

6/24/2009 2:40:00 PM #

Jeswanth

Hi Allan,
You are doing a great job. Thanks a million.
JJ

Jeswanth India

9/11/2010 5:34:49 PM #

Ramchandra

Hi Allan,
  I need to do the same string separation thing in ssis 2008 and done similar to what you have mentioned except that I am using c#.net. The problem I am facing is all these components (source-script tran-destination) gets successfully completed but the data flow task fails with error "[SSIS.Pipeline] Error: component "Script component" (48) failed the post-execute phase and returned error code 0x80004002". Could you please help me out in this?
Thanks,
Ramchandra

Ramchandra India

3/23/2011 8:55:33 AM #

Sekhar C

Hi Allan,

here I am transfering data from excel file to Database file in SSIS2008r2 using package in BIDS
my input:
I have an Excel File as shown below
Emp_name     Salary
ram            60k
sam            45K

my output:
i want table as shown below

Emp_name     Salary
ram             60000
sam             45000

please guide me how to do scripting for the above challege, please let know any other ways to completes this job

Thanks

Sekhar C

Sekhar C India

5/1/2011 12:12:45 PM #

Piyush

@Sekhar C

Add a derived column transformation, use a substring function to check for the K value in the salary and then Replace it with 000. Simple.

Piyush India

5/31/2012 10:29:15 AM #

Ane

can any one tell me wt is the error in the following Code.It is giving me an error as binary code error when i am executing the command in SSIS 2008
Dim title_id As Integer
        Dim effective_date As Date
        Dim price As Integer
        title_id = Convert.ToInt32(InputBox("Please enter the title ID", "Dee Herrotz"))
        effective_date = Date.Parse(InputBox("Please enter the date", "Dee Herrotz"))
        price = Convert.ToInt32(InputBox("Please enter the price", "Dee Herrotz"))
        Dts.Variables("title_id").Value = title_id
        Dts.Variables("effective_date").Value = effective_date
        Dts.Variables("price").Value = price
        Dts.TaskResult = ScriptResults.Success

Ane India

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

Widget Amazon Book not found.

Object reference not set to an instance of an object.X

RecentComments

Comment RSS