January 20, 2010 at 2:29 pm
Hi All
I wanted to ask, If using SSIS Script Task Following Task can be achieved.
Example
Name Value
Ab1 23
Ab1 25
Ab1 26
Desired Output as below
Name Value
Ab1^1 23
Ab1^2 25
Ab1^3 26
So simply check the incoming column name and if it appears for 1st time add ^1, if the same appears for 2nd time and 3rd Add ^2 and ^3 respectively behind the Name column.
And the input coming from top is order by Name
thanks
R
January 20, 2010 at 2:49 pm
The short answer is 'Yes' a script task can do this. You would first ensure that the data is ordered properly, declare a package variable as the counter, a package variable for the Name value, and write the script to review the increment the value of the counter variable when the Name value = the variable Name value.
What will you be doing with the data once it is in the format Name^#?
January 20, 2010 at 3:18 pm
Thanks John
For such a quick and to the point reply
I made following code
Declared following variable as DIM in Public Class
1.variable as the counter = Counter
2.package variable for the Name value = Name
If (Counter = Name) Then
Row.Name = row.Name + Counter
End If
Row.Name.ToString()
End Sub
But Not achieving the Result.
January 21, 2010 at 2:52 pm
OK, I mocked up an example based off of this table:
IF OBJECT_ID('dbo.SSISTest') IS NOT NULL
DROP TABLE dbo.SSISTest
GO
CREATE TABLE dbo.SSISTest(
Name varchar(10) NULL,
Value int NULL
)
GO
INSERT INTO dbo.SSISTest
SELECT 'Ab1',23 UNION ALL
SELECT 'Ab1',25 UNION ALL
SELECT 'Ab1',26 UNION ALL
SELECT 'Ab2',23 UNION ALL
SELECT 'Ab2',25 UNION ALL
SELECT 'Ab3',26
Create 2 variables:
--USER::Name as string
--USER::Counter as int32
Then, I created a data flow. The first task inside the data flow is an OLE DB Source pointed to the dbo.SSISTest table. The next task is a Script Transformation with the following code:
' 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
Option Strict Off
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 Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'
Dim Counter As Integer
Dim Name As String
Dim vars As IDTSVariables90
Me.VariableDispenser.LockOneForRead("User::Name", vars)
Name = vars(0).Value
vars.Unlock()
Me.VariableDispenser.LockOneForRead("User::Counter", vars)
Counter = vars(0).Value
vars.Unlock()
If Name = Row.Name Then
Counter = Counter + 1
Else
Counter = 1
End If
' Write values back to variables
Me.VariableDispenser.LockOneForWrite("User::Name", vars)
vars(0).Value = Row.Name
vars.Unlock()
Me.VariableDispenser.LockOneForWrite("User::Counter", vars)
vars(0).Value = Counter
vars.Unlock()
' Update row in pipeline
Row.Name = Row.Name + "^" + Counter.ToString
End Sub
End Class
I routed the script component to an OLE DB Destination linked to this table:
IF OBJECT_ID('dbo.SSIS_Dest') IS NOT NULL
DROP TABLE dbo.SSIS_Dest
GO
CREATE TABLE dbo.SSIS_Dest(
Name varchar(10) NULL,
Value int NULL
)
GO
This is the results it produced for me:
Name Value
Ab1^123
Ab1^225
Ab1^326
Ab2^123
Ab2^225
Ab3^126
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply