GENERATE IDENTITY VALUE IN THE DATAFLOWTASK PIPLINES

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » GENERATE IDENTITY VALUE IN THE DATAFLOWTASK...

14 posts, Page 1 of 2

1 2 »»

GENERATE IDENTITY VALUE IN THE DATAFLOWTASK PIPLINES

Rate Topic

Display Mode

Topic Options

Author

Message

SSIS\SSRS\SSAS

Posted Wednesday, August 05, 2009 7:47 AM

SSChasing Mays

Group: General Forum Members
Last Login: Saturday, October 06, 2012 4:41 PM
Points: 626, Visits: 507

Is there a way to generate identity value for rows that are passing in the dataflow task pipelines?.
There are two tables load_CUS and hist_CUS. load_CUS is used as a loading table, which has a identity column as ID. hist_CUS is used to store data from everyday load which has a identity column of its own called HIST_ID and ID column which holds value from load_CUS table when i move data from load to hist table. SO here we are doing 2 reads and 2 writies. First I read flat file and load into load_Cus table and then from Load_Cus i again insernt into hist_CUS table.

WHat i was thinking, was doing a multicast in the dataflow and load into both tables in parallel.
But I need to generate identity value in the flow so that i can insernt that value for ID column in hist_CUS.

is there someway to do this?

here is table structure
load_CUS
ID identity
name
address
....
....
.....
.

hist_CUS
hist_ID identity
ID (this comes from load_CUS tbl)
name
address
...................

Post #765453

SSIS\SSRS\SSAS

Posted Wednesday, August 05, 2009 12:59 PM

SSChasing Mays

Group: General Forum Members
Last Login: Saturday, October 06, 2012 4:41 PM
Points: 626, Visits: 507

Anyone? ANy thoughts ..............

Post #765775

John Rowan

Posted Wednesday, August 05, 2009 2:09 PM

Hall of Fame

Group: General Forum Members
Last Login: Tuesday, January 29, 2013 10:54 AM
Points: 3,837, Visits: 3,821

Add a Derived Column Transformation to your dataflow to hold the Identity value, default it to zero. Add a Script Transformation and increment the ID value in the script.

Just curious, do you reallly need the data in both tables anyways? Why does same data live in 2 tables?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden

Post #765822

SSIS\SSRS\SSAS

Posted Wednesday, August 05, 2009 2:21 PM

SSChasing Mays

Group: General Forum Members
Last Login: Saturday, October 06, 2012 4:41 PM
Points: 626, Visits: 507

Yes john we need data in both tables. load_Cus is used as a loading tbl for daily load which gets truncated for every load. and hist_cus is used to keep those daily load datas for future.
Like if friday loads fails we can come monday and then run the load from hist table for friday becuase the load_cus will hold data only for one day. I know there is option that i have a datafile archieved somewhere and i can run that particular dayafile.

But we do have a system where we check the validity of each row too.

SO we tie this inofrmation from error log back to hist_cus tbl.

I will work on the information that u provided

thanks

Post #765829

SSIS\SSRS\SSAS

Posted Wednesday, August 05, 2009 2:25 PM

SSChasing Mays

Group: General Forum Members
Last Login: Saturday, October 06, 2012 4:41 PM
Points: 626, Visits: 507

John, I think I know what u trying to say using derived column. but is there somewhere where i can find code to do the increment.

Probabaly what i am thinking is dcelare a variable say varInteger of type int, intial value to 0
then bring Derived transformation to hold value in this variable. but how do i incremnet its value using script transformation.?

Post #765832

John Rowan

Posted Wednesday, August 05, 2009 2:52 PM

Hall of Fame

Group: General Forum Members
Last Login: Tuesday, January 29, 2013 10:54 AM
Points: 3,837, Visits: 3,821

I would create a package level (or scope it to the data flow) variable of type int and set to zero. The code in the Script Transformation will use the Input0_ProcessInputRow procedure to increment your variable and your Identity Column. Something like this should do the trick:

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)
        MyBase.Input0_ProcessInputRow(Row)

        Dim vars As IDTSVariables90
        Dim Count As Integer

        Me.VariableDispenser.LockOneForRead("PipelineRows", vars)
        Count = vars(0).Value
        vars.Unlock()

        Me.VariableDispenser.LockOneForWrite("PipelineRows", vars)
        vars(0).Value = Count + 1
        Row.Ident = Count + 1
        vars.Unlock()
    End Sub
End Class

Keep in mind that you'll want to set your identity column (Ident in my exampe) to ReadWrite on the Input Columns page of the Script Transformation editor.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden

Post #765843

SSIS\SSRS\SSAS

Posted Wednesday, August 05, 2009 3:21 PM

SSChasing Mays

Group: General Forum Members
Last Login: Saturday, October 06, 2012 4:41 PM
Points: 626, Visits: 507

John thanks.....for all the help. STill i am getting validation error on script component.
here are the steps and detail info.
1. Declare variable "Ident" of type int32, scoped to package.
2. inside dataflow, derived column and add as new column for the variable Ident.
3. Script component to use as transormation.
-on script page of transformation validate metadata set to true
- readwrite variables set to User::Ident.

On edit script page this is the code
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)
MyBase.Input0_ProcessInputRow(Row)
Dim vars As IDTSVariables90
Dim Count As Integer
Me.VariableDispenser.LockOneForRead("PipelineRows", vars)
Count = vars(0).Value
vars.Unlock()
Me.VariableDispenser.LockOneForWrite("PipelineRows", vars)
vars(0).Value = Count + 1
Row.Indent = Count + 1
vars.Unlock()
End Sub
End Class

I get blue and green lines on this page, which i think is the prblm.
I am using VB as script langaugae.

4. in input columns of transformation i have ident column as input alias as ident and readwrtite type.
can u please check above code and make sure it is correct.
as i am very bad at VB and C#

thanks

Post #765859

John Rowan

Posted Wednesday, August 05, 2009 3:32 PM

Hall of Fame

Group: General Forum Members
Last Login: Tuesday, January 29, 2013 10:54 AM
Points: 3,837, Visits: 3,821

OK, you're close, but now quite there. I should have expanded my explanation a bit in my previous post. Here's what I did to get my example to work:

1. Create a variable (scope it to your choice) of type int called PipelineRows.
2. Create a Derived Column Transformation. Inside it, create a new column named Ident with a default value of zero.
3. Enter the Script Transformation. On the Input Columns page of the Script Tranformation editor, check the Ident column and make sure the Usage Type is set to ReadWrite. On the script page, no editing of properties is needed - not even the variables stuff. Instead, go straight to the script designer and use the code I provided inside the script transformation.

That should do it. If you still get blue/green lines in your script designer, hover your cursor over them and they'll tell you what the error is....post it here if you get one.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden

Post #765863

SSIS\SSRS\SSAS

Posted Wednesday, August 05, 2009 3:48 PM

SSChasing Mays

Group: General Forum Members
Last Login: Saturday, October 06, 2012 4:41 PM
Points: 626, Visits: 507

Thanks JOHn
Worked. Th problme was IDTSVariable90 in the script was changed to IDTSVariable100 and Ident variable was named Pipilinerows.

Its gonna save my half time for the amount of load. and when i execute 60 pacakages for this type, i t would drmataically increase loading time

Thanks

Post #765868

John Rowan

Posted Wednesday, August 05, 2009 4:13 PM

Hall of Fame

Group: General Forum Members
Last Login: Tuesday, January 29, 2013 10:54 AM
Points: 3,837, Visits: 3,821

No problem, glad to help.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden

Post #765880

« Prev Topic | Next Topic »

14 posts, Page 1 of 2

1 2 »»

Permissions