Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Script Task In SSIS to Check column with Same value and change it accordingly Expand / Collapse
Author
Message
Posted Wednesday, January 20, 2010 2:29 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, January 11, 2013 1:09 PM
Points: 13, Visits: 123
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
Post #850867
Posted Wednesday, January 20, 2010 2:49 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, September 26, 2014 2:04 PM
Points: 3,840, Visits: 3,843
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^#?




John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Post #850881
Posted Wednesday, January 20, 2010 3:18 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, January 11, 2013 1:09 PM
Points: 13, Visits: 123
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.
Post #850890
Posted Thursday, January 21, 2010 2:52 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, September 26, 2014 2:04 PM
Points: 3,840, Visits: 3,843
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^1 23
Ab1^2 25
Ab1^3 26
Ab2^1 23
Ab2^2 25
Ab3^1 26




John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Post #851590
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse