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 12»»

GENERATE IDENTITY VALUE IN THE DATAFLOWTASK PIPLINES Expand / Collapse
Author
Message
Posted Wednesday, August 5, 2009 7:47 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 9:44 AM
Points: 627, Visits: 509
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
Posted Wednesday, August 5, 2009 12:59 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 9:44 AM
Points: 627, Visits: 509
Anyone? ANy thoughts ..............
Post #765775
Posted Wednesday, August 5, 2009 2:09 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: Tuesday, August 19, 2014 10:01 AM
Points: 3,840, Visits: 3,841
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
Posted Wednesday, August 5, 2009 2:21 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 9:44 AM
Points: 627, Visits: 509
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
Posted Wednesday, August 5, 2009 2:25 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 9:44 AM
Points: 627, Visits: 509
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
Posted Wednesday, August 5, 2009 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: Tuesday, August 19, 2014 10:01 AM
Points: 3,840, Visits: 3,841
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
Posted Wednesday, August 5, 2009 3:21 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 9:44 AM
Points: 627, Visits: 509
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
Posted Wednesday, August 5, 2009 3:32 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: Tuesday, August 19, 2014 10:01 AM
Points: 3,840, Visits: 3,841
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
Posted Wednesday, August 5, 2009 3:48 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 9:44 AM
Points: 627, Visits: 509
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
Posted Wednesday, August 5, 2009 4:13 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: Tuesday, August 19, 2014 10:01 AM
Points: 3,840, Visits: 3,841
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 »

Add to briefcase 12»»

Permissions Expand / Collapse