Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dhumphries
Starting Member
18 Posts |
 Posted - 2009-02-06 : 12:53:30
|
| I am creating an SSIS that among many other things will update some records based on some criteria. Before I update these records however I must create a record of what the previous value was and what the new value will be in a historical table. The historical table relies on a sequence number as the key and that is were the problem comes in. Before I move the records over to be changed I must read the current Maxreserved value from a parm table that will tell me what the next available number is. after that I must write the records out to the destination table. I can get the records to write out but I can not figure out how to get the maxreserved to incriment by 1 on each record. I have tried adding this in a dervied column transform and a script transform but I can not figure out how to get it to up the variable by one on each record. |
|
|
dhumphries
Starting Member
18 Posts |
Posted - 2009-02-06 : 14:45:01
|
| Thought I might add some additional information. I am reading a set of records from an OLDB source. this is then passed to the script transform.In the script transform I have a project variable that in the control flow is set to the last max reserve.The code in the transform looks like this. Imports SystemImports System.DataImports System.MathImports Microsoft.SqlServer.Dts.Pipeline.WrapperImports Microsoft.SqlServer.Dts.Runtime.WrapperPublic Class ScriptMain Inherits UserComponent Dim varmaxRes As Integer = CType(Me.Variables.MaxReser, Integer) Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) Row.MaxRes = varmaxRes varmaxRes = varmaxRes + 1 End SubEnd ClassI know that you can not update a project variable from here so microsoft recommends setting up a local variable and incrementing that however I still get the error. The collection of variables locked for read and write access is not available outside of PostExecute. how can I get the value of this varieable read in so I can then increment the local variable on each row. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-07 : 09:18:39
|
quote:
Originally posted by dhumphries
I am creating an SSIS that among many other things will update some records based on some criteria. Before I update these records however I must create a record of what the previous value was and what the new value will be in a historical table. The historical table relies on a sequence number as the key and that is were the problem comes in. Before I move the records over to be changed I must read the current Maxreserved value from a parm table that will tell me what the next available number is. after that I must write the records out to the destination table. I can get the records to write out but I can not figure out how to get the maxreserved to incriment by 1 on each record. I have tried adding this in a dervied column transform and a script transform but I can not figure out how to get it to up the variable by one on each record.
for creating a record of what the previous value was and what the new value will be in a historical table, you just need to use SCD wizard task with type 2 property set in data flow task.To get maxreserved from parm table, use oledb command task and then store the output value onto to a variable created in package for later use. |
|
|
|
|
|
|
Add sequence number from reserved set