August 5, 2009 at 7:47 am
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
...................
August 5, 2009 at 12:59 pm
Anyone? ANy thoughts ..............
August 5, 2009 at 2:09 pm
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?
August 5, 2009 at 2:21 pm
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
August 5, 2009 at 2:25 pm
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.?
August 5, 2009 at 2:52 pm
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.
August 5, 2009 at 3:21 pm
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
August 5, 2009 at 3:32 pm
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.
August 5, 2009 at 3:48 pm
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
August 5, 2009 at 4:13 pm
September 26, 2012 at 4:06 am
I too have the same issue of generating the sid
and im getting the following errors in the script u have given
1. 'ScriptMain' is not CLS-compliant because it derives from 'UserComponent', which is not CLS-compliant.
2.Type of parameter 'Row' is not CLS-compliant.
3.Type 'IDTSVariables90' is not defined
and I have zero knowledge at C# and VB.NET
please help me out in rectifying these errors
Thanks in advance
September 26, 2012 at 4:07 am
Hi John
I too have the same issue of generating the sid
and im getting the following errors in the script u have given
1. 'ScriptMain' is not CLS-compliant because it derives from 'UserComponent', which is not CLS-compliant.
2.Type of parameter 'Row' is not CLS-compliant.
3.Type 'IDTSVariables90' is not defined
and I have zero knowledge at C# and VB.NET
please help me out in rectifying these errors
Thanks in advance
September 26, 2012 at 4:08 am
Hi John
I too have the same issue of generating the sid
and im getting the following errors in the script u have given
1. 'ScriptMain' is not CLS-compliant because it derives from 'UserComponent', which is not CLS-compliant.
2.Type of parameter 'Row' is not CLS-compliant.
3.Type 'IDTSVariables90' is not defined
and I have zero knowledge at C# and VB.NET
please help me out in rectifying these errors
Thanks in advance
September 26, 2012 at 11:59 am
Rakhi,
I don't know exactly ur issue and even fix it. But In my case it worked, but then after doing actual test, this was really slow compared to other what i have currently.
What i do is load text files to loading table and then copy that data to another 3 months holding table. Having two dataflow was faster for me rather than having one dataflow and generating id in the flow and inserting into 2 differnt tables.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply