March 30, 2010 at 10:16 am
I need some advice from the experts. Here is the situation we have some account information I need to load from one database to another. The informaton consists of an account number and meter number. When I write the records out to the other database I need to add a meter sequence number. Each account could have up to 5 meters and so the record might look like
account Meter Seq
1234 678 1
1234 678 2
1234 678 3
So my thought was to add a script transform to the data flow and check to see if account number has changed. If it has go ahead and increment a variable by 1 and add that to the data flow.
this seems simple enough but I am encountering an error I can not figure out. Since SSIS does not allow breaks I can not figure out through debug where the script is breaking. Any thoughts would be greatly appreciated.
Here is the error!
The Value is too large to fit in the column data area of the buffer.
at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.SetString(Int32 columnIndex, String value)
at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.set_Item(Int32 columnIndex, Object value)
at Microsoft.SqlServer.Dts.Pipeline.ScriptBuffer.set_Item(Int32 ColumnIndex, Object value)
at ScriptComponent_c435171261424684b2e6588babdbc61d.Input0Buffer.set_MeterSeq(String Value)
at ScriptComponent_c435171261424684b2e6588babdbc61d.ScriptMain.Input0_ProcessInputRow(Input0Buffer Row)
at ScriptComponent_c435171261424684b2e6588babdbc61d.UserComponent.Input0_ProcessInput(Input0Buffer Buffer)
at ScriptComponent_c435171261424684b2e6588babdbc61d.UserComponent.ProcessInput(Int32 InputID, PipelineBuffer Buffer)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)
Here is the code!
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim AccountNo As String
Dim MeterNo As String
Dim MeterSeq As Integer
If Row.CACCOUNT <> AccountNo Then
MeterSeq = 1
AccountNo = Row.CACCOUNT
MeterNo = Row.CMETER
Else
If Row.CMETER <> MeterNo Then
MeterSeq = MeterSeq + 1
MeterNo = Row.CMETER
End If
End If
Row.MeterSeq = "0" + CStr(MeterSeq)
End Sub
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
March 30, 2010 at 10:59 am
Did you try using Row_Number() Function in SQL?? its simple and would give you the same results ... Partition them on Seq,meter
March 30, 2010 at 11:34 am
divyanth (3/30/2010)
Did you try using Row_Number() Function in SQL?? its simple and would give you the same results ... Partition them on Seq,meter
Sounds like it would work ... if running an UPDATE query after the import is a satisfactory solution.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 30, 2010 at 11:58 am
I had not tried that but I will play around with that idea for a while and see what I can come up with.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
March 30, 2010 at 12:51 pm
Can I ask a different question..
Does the operational system that provides this data not have some unique identifier for the meter itself? And if not then WHY and if so then why aren't you using it?
CEWII
March 30, 2010 at 12:58 pm
Well That is a very good question and I asked it myself. Basically it comes down to like most good questions to circumstances I can not control. The meter number itself is a unique number and that is not really the issue but as I understand it the sequence number is used in the application that accesses this data. It atleast for now does not seem like that can be changed so I need to make the data match what the application is doing even though it may not be the best design.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
March 30, 2010 at 1:03 pm
How do you guarantee always to generate the sam sequence number for a particular meter. Is this a oneshot deal? If not each subsequent run could easily generate a different id value..
CEWII
March 30, 2010 at 1:13 pm
The seq number does not have to be the same everytime for each meter as long as there is a seq number present for each multi meter account.
This is a legacey system that currently is running based on a SQL 2000 DTS. We had hoped to eliminate the need for the system but since that does not look like that will happen any time soon I have to recreate the DTS on 2005 so we can get rid of our older servers.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
March 30, 2010 at 1:20 pm
Adding the following line to my select query worked great.
row_number() OVER (PARTITION by C_account order by c_account)
I would have never thought to do it this way.
Thanks for the advice!
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply