Calculating Asynchronous Replication Bandwidth
Feb 24, 2008
Hi
I am evaluating the possibility of replicating a database over a network to our HQ from the control site (one way). The original database is on SQL server.
The database is likely to grow to many terabytes so we would be using transactional replication.
The table to be replicated recieves about 500 records per second.
The table will probably consist of a record key (8 byte int), site ID (4 byte int), reading (8-byte float), and timestamp (8 byte timestamp). All up, 28 bytes + whatever overhead exists.
MINOR DETAIL:
The HQ's copy should be preferably no more than 1 hour behind the control site's. This would be a long-term setup that would last for many years. Our link is currently about 2 MB/s, and is fairly reliable.
QUESTIONS:
I'm guessing that (bytes/record)*(records/second) won't be the whole story. Does anyone have an estimate of the average data efficiency factor for transactional replication?
How would I go about calculating how much bandwidth would be needed? Is there a formulae hiding somewhere?
Thanks in advance.
Simon.
View 5 Replies
ADVERTISEMENT
Mar 11, 2008
Hi All,
I am setting up P2P replication for a high latency environment (although at 100MBit per sec, the bandwidth is not a serious issue ).
I have noticed that at high load, the bandwidth between Distribution and Subscriber servers maxes out at 1MBit per second (our network bandwidth is 100MBit per second).
This causes transactions to 'back up' and when the load reduces they 'catch up' and synchronise.
Does anyone know where this limitation of 1Mbs is being enforced and what is the way round it?
I have seen the -SubscriptionStreams NN parameter, but this is not applicable in P2P replication.
Any advice would be appreciated.
Thanks
Aasim
View 2 Replies
View Related
Aug 18, 2003
I have scenerio that find out the Bandwitdh size between clint and server.
I wanted find out howmuch size of data recieveing from server at a time.
Any advice regarding this.
Thanks,
Ravi
View 3 Replies
View Related
Jan 30, 2007
I am building a SQL server on a Windows 2000 Server box. It has a public IP and we have 4 T1 lines. After I install SQL 2000 it will run for about 30 minutes and then it will take up all the bandwidth on the network to such a degree that my users are unable to access the internet. I have been troubleshooting this over the last couple of days. This morning I reinstalled SQL with just the default DB's and after 45 mins it brought the network down again. Any help on this would be greatly appreciated.
View 3 Replies
View Related
Aug 3, 2007
Hi there SQL Team
I hope I’ve posted this in the right section.
We have a customer, call them customerA
CustomerA has just employed a new Chief Information Officer.
The New CIO would like to make a few changes to the current way we have things right now.
Bare with me as I explain our environment.
We have The following Servers
Application Server[FSS (Dual DC Xeon 2.33GHz CPU, 4GB RAM, 3x72GB hdd RAID]
Database Server [FSS (Dual DC Xeon 2.33GHz CPU, 4GB RAM, 3x72GB hdd RAID)]
Both the above servers sit in a fully fault tolerant environment with daily backups power generators etc etc etc.1000MB network everything work 100%
Now my question:
The new CIO would like to move the DB server out of this environment to a remote building downtown, and have the application server connect to this remote db server.
Is this advisable?
What are the required connection speeds, so that the end user does not have to wait for a response?
What is the recommended response time IIS should give?
Please also note that we are in South Africa and broadband is VERY expensive / unreliable.
The CIO would like to install a 2Mbit radio link from this building to the ISP I’m not sure on the cost, but do know that this type of link is very unstable,
Their failover will be a 512kb fixed line or diginet line. Cost about ZAR 8000.00 per month
The application is extremely data / graphs intensive as it contain property information.
I think they average about 16000 visitors daily, not a lot.
If anyone can give me any input i would appreciated.
Thanks
View 1 Replies
View Related
Jul 23, 2005
HiI have two SQL2000 servers in different sites, once a day approximately1M of data in the form of a large update is required to be transferedbetween the 2. We have use of a 2M pipe between the servers but thereis no quality of service, the other users on the pipe are traders sothere must be no interruption in the quality of their bandwidth at anytime.Is there any way of throttling back the data transfer between the twoservers to restrict its bandwidth use. Obviously we want to retain themax bandwidth on our local network.The pipe is administered by a seperate company so we do not have adminaccess to their gateways, routers etc.. so a solution which we canimplement on our database servers would be the easiest.I am not sure if this is the right newsgroup for this but anyinformation would be greatThanksMark
View 1 Replies
View Related
Feb 28, 2005
Does anyone know a wayto compress data between two database connections over "low bandwidth" lines in order to speedup datatransfer?
Used connections are Oracle<->SQL2000 and SQL2000<->SQL2000.
View 3 Replies
View Related
Jan 21, 2008
Hi to all experts,
I have a server with about 50 databases and the server bandwidth usage is 15-30 Mbps.
I need to know which databse is consuming the bandwidth.
Can you tell me what is the best way (if any) to monitor bandwidth usage per database?
View 4 Replies
View Related
Nov 1, 2006
All:
One of the new tests that we are running have to do with load testing an application with a constrained network pipe. I like this. One of my beefs has been related to stored procedures that return bloated result sets. This new set of tests potentially gives me some more amunition to use when I review stored procedures. A piece that I would like to produce as a result of this has to do with establishing an output bandwidth standard for our database servers. I have a few biased ideas but I would like to know if any of you have any similar pre-existing standards along this line. Any help?
Dave
View 1 Replies
View Related
Mar 27, 2007
Hello :
I have a Web application with reports which lasts for a long time, for it I reflected to make call Asynchronous for reports
(that is I execute the report and warned the user when the report is ready).
I found an example which supplied with SQL Server, but as I am novice C# I understands not everything in the example
( AsynchronousRenderCS).
Please indicate me a simpler example.
Thank you.
View 1 Replies
View Related
Apr 8, 1999
Hi all,
Can anybody tell me if the have had any luck on creating and processing asychronous cursors. According to Microsoft SQL 7.0 books online after you create your Async cursor and then display the variable @@cursor_rows it should display either a negative number like -1245 meaning that it is still loading or a whole number meaning that it finish loading, but every time I display this variable I get -1 and according to MSSQL documentation this means I'm creating a Synchronous cursor, I have modified the cursor threshold settings, declared my cursor INSENSITIVE, and still can't get a cursor to be Async.
Thanks
View 1 Replies
View Related
Mar 21, 2007
What I am looking to do is have a stored procedure begin a dialog with my request service.
With that dialog established my stored procedure sends 50 request messages, one for each of the 50 of the United States. I want these to be processed asynchronously by a procedure that is called on activation for the request queue. In that activation procedure the request is processed against the respective state and a response message is sent to the response service (to the response queue). I want to be able to tie these request messages and response messages together with some type of shared identifier. These requests don't need to be processed in any specific order and don't need any fancy locking mechanism via conversation group since these requests require to be processed asynchronously. What is the best approach? Do I need to create 50 seperate queues and open dialogs with each? If this is the route to take, would this be a performance hit?
My goal is to have all 50 states process all at once, each finishing with a response message sent to the response queue. The initiating procedure, after sending these 50 requests, would then spin and wait for all 50 to complete, be it a response, error, or timeout. When all 50 have returned, the procedure would then merge the results and return. So as you can see in my scenario, I dont care when a state is complete as they do not affect the outcome, nor do they access any of the same resources when being processed.
View 3 Replies
View Related
Jul 13, 1999
We're looking for a solution to an audit trail issue. Our business people are looking to track each column value that changes(before and after images) for every table on our database as well as the userid that changed the data and when it was changed. Are there any methods that have been employed by other sites to track this level of detailed changes without resorting to triggers for each table and has anyone worked out a way for this audit trail writing to be handled asynchronously within SQL Server?
View 1 Replies
View Related
Oct 21, 2014
i tried to use xp_cmdshell in order to execute a vbscript from a trigger and it works, but i notice that trigger wait until the vbscript was terminated. i do some things into vbscript, so i can't wait until the end. There is a way to don't wait until the end, in practice, run vbscript in asynchronous mode??
View 6 Replies
View Related
Apr 19, 2007
Hi--done some searching, but I am not finding exactly what I need. I am using an asynchronous script component as a lookup since my table I am looking up on requires an ODBC connection. Here is what my data looks like:
From an Excel connection:
Order Number
123
234
345
The table I want to do a lookup on has multiple rows for each order number, as well as a lot of rows that aren't in my first table:
Order Number Description
123 Upgrade to System
123 Freight
123 Spare Parts
234 Upgrade to System
234 Freight
234 Spare Parts
778 Another thing
889 Yet more stuff
etc. My desired result would be to pull all the items from table two that match on Order Number from table one. My actual results from the script I have is a single (random) row from table two for each item in table one.....So my current results look like:
Order Number Description
123 Freight
234 Freight
345 Null
And I want:
Order Number Description
123 Upgrade to System
123 Freight
123 Spare Parts
234 Upgrade to System
234 Freight
234 Spare Parts
345 Null
etc.... Here is my code, courtesy of half a dozen samples found here and elsewhere...
Code Snippet
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Data.Odbc
Public Class ScriptMain
Inherits UserComponent
Dim connMgr As IDTSConnectionManager90
Dim odbcConn As OdbcConnection
Dim odbcCmd As OdbcCommand
Dim odbcParam As OdbcParameter
Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
connMgr = Me.Connections.JDEConnection
odbcConn = CType(connMgr.AcquireConnection(Nothing), OdbcConnection)
End Sub
Public Overrides Sub PreExecute()
odbcCmd = New OdbcCommand("SELECT F4211.SDDSC1, F4211.SDDOCO FROM DB.F4211 F4211 Where F4201.SHDOCO = ?", odbcConn)
odbcParam = New OdbcParameter("1", OdbcType.Int)
odbcCmd.Parameters.Add(odbcParam)
End Sub
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim reader As Odbc.OdbcDataReader
odbcCmd.Parameters("1").Value = Row.SO
odbcCmd.ExecuteNonQuery()
reader = odbcCmd.ExecuteReader()
If reader.Read() Then
With Output0Buffer
.AddRow()
.SDDSC1 = reader("SDDSC1").ToString
.SONumb = Row.SO
.SOJDE = CDec(reader("SDDOCO"))
End With
End If
reader.Close()
End Sub
Public Overrides Sub ReleaseConnections()
connMgr.ReleaseConnection(odbcConn)
End Sub
End Class
I just don't know what I need to do to get every row from F4211 where SDDOCO matches Row.SO instead of a single row...... Any ideas or help? Oh, the reason I am starting with my Excel connection is that sheet lists the Orders I need detailed data for, and is only a few hundred rows....F4211 is really really really big.
I have also worked out an alternate way to do this using merge join tasks...but then my datareader source goes off and fetches 300,000 rows from F4211 before my final result set of about 1200 rows. That just feels like a bad approach to me...or am I being over-cautious? I'm a newb (if you couldn't already tell)...so guidence is appreciated.
Thank you....
View 12 Replies
View Related
Jan 3, 2007
I noticed that the current SLQCe driver does not offer support for the APM(Asynchronous Programming Model). Are there any plans to do this in the future? In light of the lack of APM functionality doe anyone have any ideas or thoughts on how async operations could be done, or if they are even needed in the context of applications that use SQL Ce
View 4 Replies
View Related
Jan 3, 2008
Can someone please clarify:
If you have a data file, and you only want CERTAIN rows to pass to the destination, ie) a table
and you are using a script task to accomplish this,
is this a synchronous or asynchronous transformation?
Q. And how do you assign the values to the output? Do you have to create output columns, or not?
I am very very confused right now. I can't seem to find a decent answer to what is a very basic question either in my SSIS book or in the documenation. Perhaps it is so basic, that the question doesn't seem valid? I don't know. But I just don't understand this at all.
Thank you
View 9 Replies
View Related
Mar 20, 2007
Hi All,
Any help regarding this very appreciated.
Problem:
I have a tough situation of trying to execute multiple instance of same package, to reduce the process load times.
Introduction:
We have src system which get 7000 tiny files of 72 rows each, and the SSIS package uses For Each Loop task and iterates through each file and loads data. We have a Process table that keeps track of the status of the SRC Process & ETL Load Process.
When the src process starts, For each row in the process table, it assigns a row status 'Assigned' brings in the flat file of 72 rows & updates the status as 'Complete'. When the ETL starts, for each file in the shared directory, it assigns status 'Running' and loads the data and updates status 'Complete'. Then the file is moved to different processes folder. Its like the bridge table between the 2 processes.
Bride Table Format: Table_PK(identity col), (DATE, City) is natural key, it is a cross join of date & City, so the process is getting 1 file every day for 1 city. Initial status are both 'Queued'
-----------------------------------------------------------------------------------------------------------------
Table_PK DATE CITY SrcProcStatus ETLStatus
-----------------------------------------------------------------------------------------------------------------
1 03/17/2007 Abingdon Queued Queued
2 03/17/2007 Albion Queued Queued
3 03/17/2007 Aledo Queued Queued
4 03/17/2007 Altamont Queued Queued
5 03/17/2007 Alton Queued Queued
6 03/17/2007 Amboy Queued Queued
7 03/17/2007 Anna Queued Queued
8 03/17/2007 Antioch Queued Queued
9 03/17/2007 Arcola Queued Queued
10 03/17/2007 Arlington Heights Queued Queued
11 03/17/2007 Ashley Queued Queued
.... ....
11 03/17/2007 Zeigler Queued Queued
11 03/17/2007 Zion Queued Queued
----------------------------------------------------------------------------------------------------------------
Since the bridge table is prepopulated, the src process(which is on Unix) starts multiple threads and gets files with in 30 minutes. But the SSIS is serial process & takes 2 -3 hrs to load the files, most of the time is taken by file operations and SSIS can only start only 1 thread.
Future Plan:
So to bring down the processing times, we wanted to start the SSIS packages in the Bridge table instead of starting in the share folder. i.e. for each row in the bridge where SRCProcess is Complete & ETLProcess Queued, start the SSIS process for this src file. Since our SRC files are names as "CityName_Date.csv" it will not be difficult. So we wanted to start multiple threads, that way the load process will be fast.
Implementation:
In the T-SQL loop we wanted to use 'xp_cmdshell' and call DTEXEC utility with the src file name as variable. But the DTEXEC is a synchronous process, but I am looking for a way to implement this asyncronously. Just like using "nohup executionscript &" in unix.
So any ideas on how to implement this, I looked on the web, and there is some thing about service broker, but all it says is about sending messages & queuing. Any light on how to implement this on windows server is going to be a life saver.
Thanks a lot,
Venkat
View 2 Replies
View Related
Sep 19, 2006
Greetings, I have a requirement for a SQL Server 2005 database to
notify 3rd Party applications of a table change. As is stands now, 3rd
Party applications access the database via a Data Access Layer (DAL)
dll (C#). I'd like to somehow implement an asychronous event
notification scheme via the same DAL to these clients.
Can someone offer a clever way to implement such events?
Broker Services? I am under the impression the SSBS is typically deployed when databases are to communicate with one another.
Triggers to call some CLR code?
Other?
Thanks in advance,
Loopsludge
View 3 Replies
View Related
May 13, 2008
Hi,
The application that I'm working is has a job that runs 24/7 which checks a table for rows with specific status, if it finds it, then I has to execute a job for each row that it finds.
I don't know wheather if SQL Server jobs are asynchronous or not, since I need the execution of jobs to be in parallel instead of in a pipeline mode.
I'd appreciate your help.
Thanks,
mamrez
View 2 Replies
View Related
Mar 19, 2008
Porting an existing SQL 2k DTS job over to a SQL 2k5 SQL Server running SSIS.
Background:
The job loads data into an empty work table and performs some work before clearing out the work table.
This job runs every minute.
Question:
If the job happens to take longer than a minute, does SSIS create a second instance of the job?
Or perhaps it does what DTS did and reschedules the job for the next iteration?
Concern:
I need to know because there would be key contraint violations if another instance of the job started before the working table was cleared out.
Thanks in advance
View 1 Replies
View Related
Sep 21, 2007
Hi
Can anyone please tell me what happens if I have Asynchronous mirroring setup and my Primary server physically dies and not available then what happens?. Does
1. Automatic failover occur to Secondary server?
2. What does the Database state show as. Primary, disconnected?.
3. what happens to my transactions. Are they lost?
4. Does any data loss occur?
If I rebuild a new server how do I sync back my current primary to the new one? In that case is it going to be just a fail back?
Any information is appreciated,
Thank you
AK
View 6 Replies
View Related
May 4, 2006
Hi,
Im new to this list and after many days of trying to figure this out-here we go
Can you please tell me where I€™m going wrong in my asynchronous script component?
I€™m almost there but by using to variable iReadingCount to add the row is not quite correct. There has to be a better way !!!
Thanks in advance
Dave
I have to process a data from a staging table this which has imported a data in a structure like this, each line has a tag which is a fieldname <MyName > followed by the value
<Advice Note Number> is the Tag that tells me it is the start of the next record with the only gotca is there may be up to six <Contractor Billing> Tags in one record.
Tag Val1 Val2
<Advice Note Number> 1374239
<Customer Names> My Name
<Customer Address> My Address
<Completion Date Time> 2005/11/25 16:30:00
<Service Order Number> 123456
<Phone Number> 999535431
<Telephone Exchange> MNG
<Contractor ID> Fabrikan
<Service Order Type> F3
<Contract ID> 41
<Comments> 1 2
<Contractor Billing> 165 1
<Contractor Billing> 167 1
<Customer Signature> NO
<Advice Note Number> 1374240
<Customer Names> My Name
<Customer Address> My Address
<Completion Date Time> 2005/11/25 16:30:00
<Service Order Number> 123456
<Phone Number> 999535431
<Telephone Exchange> MNG
<Contractor ID> Fabrikan
<Service Order Type> F3
<Contract ID> 41
<Comments> 1 2
<Contractor Billing> 165 1
<Customer Signature> NO
So I need a asynchronous script component
(
Setting SynchronousInputID=0 turns your component into an asynchronous component - thus giving you access to the output buffer.)
Because I need to map this data structure like this
Input Table
CREATE TABLE [S_CAT] (
[Tag] [varchar] (8000) COLLATE Latin1_General_CI_AS NULL ,
[Val1] [varchar] (8000) COLLATE Latin1_General_CI_AS NULL ,
[Val2] [varchar] (8000) COLLATE Latin1_General_CI_AS NULL ,
[Val3] [varchar] (8000) COLLATE Latin1_General_CI_AS NULL )
GO
Desired Output Table
CREATE TABLE [S_CATM] (
[CATID] [int] IDENTITY (1, 1) NOT NULL ,
[AdviceNoteNumber] [int] NOT NULL ,
[CustomerNames] [varchar] (75) COLLATE Latin1_General_CI_AS NULL ,
[CustomerAddress] [varchar] (120) COLLATE Latin1_General_CI_AS NULL ,
[ArrivalDateTime] [smalldatetime] NULL ,
[CompletionDateTime] [smalldatetime] NULL ,
[ServiceOrderNumber] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[PhoneNumber] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[TelephoneExchange] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[ContractorID] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[ServiceOrderType] [varchar] (6) COLLATE Latin1_General_CI_AS NULL ,
[ContractID] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[Comments] [varchar] (160) COLLATE Latin1_General_CI_AS NULL ,
[ContractorBilling] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[ContractorBillingQuantity] [tinyint] NULL ,
[ContractorBilling2] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[ContractorBillingQuantity2] [tinyint] NULL ,
[ContractorBilling3] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[ContractorBillingQuantity3] [tinyint] NULL ,
[ContractorBilling4] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[ContractorBillingQuantity4] [tinyint] NULL ,
[ContractorBilling5] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[ContractorBillingQuantity5] [tinyint] NULL ,
[ContractorBilling6] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[ContractorBillingQuantity6] [tinyint] NULL ,
[ApprovalCode] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[TelecomRejectReason] [varchar] (132) COLLATE Latin1_General_CI_AS NULL ,
[ContractorRejectResponse] [varchar] (132) COLLATE Latin1_General_CI_AS NULL ,
[CustomerSignature] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
[ReceivedOnTime] [varchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[DateAdded] [smalldatetime] NOT NULL CONSTRAINT [DF_CATRecords_DateAdded] DEFAULT (getdate()),
CONSTRAINT [PK_CATRecords] PRIMARY KEY CLUSTERED
(
[CATID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
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
Dim iReadingCount As Integer = 0
Dim Comments1 As String
Dim Comments2 As String
Dim Comments3 As String
Dim AdviceNoteNumber As Integer
Dim CustomerNames As String
Dim CustomerAddress As String
Dim ArrivalDateTime As Date
Dim CompletionDateTime As Date
Dim ServiceOrderNumber As String
Dim PhoneNumber As String
Dim TelephoneExchange As String
Dim ContractorID As String
Dim ServiceOrderType As String
Dim ContractID As String
Dim Comments As String
Dim ContractorBilling As String
Dim ContractorBillingQuantity As Integer
Dim ContractorBilling2 As String
Dim ContractorBillingQuantity2 As Integer
Dim ContractorBilling3 As String
Dim ContractorBillingQuantity3 As Integer
Dim ContractorBilling4 As String
Dim ContractorBillingQuantity4 As Integer
Dim ContractorBilling5 As String
Dim ContractorBillingQuantity5 As Integer
Dim ContractorBilling6 As String
Dim ContractorBillingQuantity6 As Integer
Dim ApprovalCode As String
Dim TelecomRejectReason As String
Dim ContractorRejectResponse As String
Dim CustomerSignature As String
Dim ReceivedOnTime As String
'Public Overrides Sub CreateNewOutputRows()
'End Sub
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Try
If StrConv(Row.Tag, VbStrConv.ProperCase) = "<Advice Note Number>" Then
AdviceNoteNumber = CInt(Trim(Row.Val1))
'Increase the reading count by 1
iReadingCount += 1
ElseIf StrConv(Row.Tag, VbStrConv.ProperCase) = "<Customer Names>" Then
CustomerNames = Left(Trim(Row.Val1 & Row.Val2), 75)
ElseIf StrConv(Row.Tag, VbStrConv.ProperCase) = "<Customer Address>" Then
CustomerAddress = Left(Trim(Row.Val1 & Row.Val2), 120)
'ElseIf Row.Tag = "<ARRIVAL Date Time>" Then
' 'ArrivalDateTime = CDate(Trim(Row.Val1))
' ArrivalDateTime = CDate(Trim(Row.Val1) & " " & Trim(Row.Val2))
'ElseIf Row.Tag = "<Completion Date Time>" Then
' 'CompletionDateTime = CDate(Trim(Row.Val1))
' CompletionDateTime = CDate(Trim(Row.Val1) & " " & Trim(Row.Val2))
ElseIf StrConv(Row.Tag, VbStrConv.ProperCase) = "<Service Order Number>" Then
ServiceOrderNumber = Left(Trim(Row.Val1), 20)
ElseIf StrConv(Row.Tag, VbStrConv.ProperCase) = "<Phone Number>" Then
PhoneNumber = Left(Trim(Row.Val1), 20)
ElseIf StrConv(Row.Tag, VbStrConv.ProperCase) = "<Telephone Exchange>" Then
TelephoneExchange = Left(Trim(Row.Val1), 20)
ElseIf StrConv(Row.Tag, VbStrConv.ProperCase) = "<Contractor Id>" Then '"<Contractor ID>"
ContractorID = Left(Trim(Row.Val1), 10)
ElseIf StrConv(Row.Tag, VbStrConv.ProperCase) = "<Service Order Type>" Then
ServiceOrderType = Left(Trim(Row.Val1), 6)
ElseIf StrConv(Row.Tag, VbStrConv.ProperCase) = "<Contract Id>" Then '"<Contract Id>"
ContractID = Left(Trim(Row.Val1), 20)
ElseIf StrConv(Row.Tag, VbStrConv.ProperCase) = "<Comments>" Then
Comments1 = Left(Trim(Row.Val1), 160)
Comments2 = Left(Trim(Row.Val2), 160)
Comments3 = Left(Trim(Row.Val3), 160)
'One Line
If Len(Comments1) > 1 And Len(Comments2) = 1 And Len(Comments3) = 1 Then
Comments = Comments1
End If
'Two Lines
If Len(Comments1) > 1 And Len(Comments2) > 1 And Len(Comments3) = 1 Then
Comments = Comments1 & " " & Comments2
End If
'Three Lines
If Len(Comments1) > 1 And Len(Comments2) > 1 And Len(Comments3) > 1 Then
Comments = Comments1 & " " & Comments2 & " " & Comments3
End If
ElseIf StrConv(Row.Tag, VbStrConv.ProperCase) = "<Contractor Billing>" Then
ContractorBilling = Left(Trim(Row.Val1), 10)
ContractorBillingQuantity = 0 'CInt(Val(Trim(Row.Val2))) 'CInt(Val(Trim(Row.Val2)))
ElseIf StrConv(Row.Tag, VbStrConv.ProperCase) = "<Contractor Billing>" Then
ContractorBilling2 = Left(Trim(Row.Val1), 10)
ContractorBillingQuantity2 = 0 'CInt(Val(Trim(Row.Val2))) 'CInt(Trim(Row.Val2))
ElseIf StrConv(Row.Tag, VbStrConv.ProperCase) = "<Contractor Billing>" Then
ContractorBilling3 = Left(Trim(Row.Val1), 10)
ContractorBillingQuantity3 = 0 'CInt(Val(Trim(Row.Val2))) 'CInt(Trim(Row.Val2))
ElseIf StrConv(Row.Tag, VbStrConv.ProperCase) = "<Contractor Billing>" Then
ContractorBilling4 = Left(Trim(Row.Val1), 10)
ContractorBillingQuantity4 = 0 'CInt(Val(Trim(Row.Val2))) 'CInt(Trim(Row.Val2))
ElseIf StrConv(Row.Tag, VbStrConv.ProperCase) = "<Contractor Billing>" Then
ContractorBilling5 = Left(Trim(Row.Val1), 10)
ContractorBillingQuantity5 = 0 'CInt(Val(Trim(Row.Val2))) 'CInt(Trim(Row.Val2))
ElseIf StrConv(Row.Tag, VbStrConv.ProperCase) = "<Contractor Billing>" Then
ContractorBilling6 = Left(Trim(Row.Val1), 10)
ContractorBillingQuantity6 = 0 'CInt(Val(Trim(Row.Val2))) 'CInt(Trim(Row.Val2))
ElseIf StrConv(Row.Tag, VbStrConv.ProperCase) = "<Approval Code>" Then
ApprovalCode = Left(Trim(Row.Val1), 20)
ElseIf StrConv(Row.Tag, VbStrConv.ProperCase) = "<Telecom Reject Reason>" Then
TelecomRejectReason = Left(Trim(Row.Val1), 132)
ElseIf StrConv(Row.Tag, VbStrConv.ProperCase) = "<Contractor Reject Response>" Then
ContractorRejectResponse = Left(Trim(Row.Val1), 132)
ElseIf StrConv(Row.Tag, VbStrConv.ProperCase) = "<Customer Signature>" Then
CustomerSignature = Left(Trim(Row.Val1), 1)
ElseIf StrConv(Row.Tag, VbStrConv.ProperCase) = "<Received On Time>" Then
ReceivedOnTime = Left(Trim(Row.Val1), 3)
End If
If iReadingCount = 1 Then
'Finally add the row
With Output0Buffer
'add a row to the output buffer
.AddRow()
'Set the values of each of our output buffer columns
.AdviceNoteNumber = AdviceNoteNumber
.CustomerNames = CustomerNames
.CustomerAddress = CustomerAddress
'.ArrivalDateTime = ArrivalDateTime
'.CompletionDateTime = CompletionDateTime
.ServiceOrderNumber = ServiceOrderNumber
.PhoneNumber = PhoneNumber
.TelephoneExchange = TelephoneExchange
.ContractorID = ContractorID
.ServiceOrderType = ServiceOrderType
.ContractID = ContractID
.Comments = Comments
.ContractorBilling = ContractorBilling
.ContractorBillingQuantity = ContractorBillingQuantity
.ContractorBilling2 = ContractorBilling2
.ContractorBillingQuantity2 = ContractorBillingQuantity2
.ContractorBilling3 = ContractorBilling3
.ContractorBillingQuantity3 = ContractorBillingQuantity3
.ContractorBilling4 = ContractorBilling4
.ContractorBillingQuantity4 = ContractorBillingQuantity4
.ContractorBilling5 = ContractorBilling5
.ContractorBillingQuantity5 = ContractorBillingQuantity5
.ContractorBilling6 = ContractorBilling6
.ContractorBillingQuantity6 = ContractorBillingQuantity6
.ApprovalCode = ApprovalCode
.TelecomRejectReason = TelecomRejectReason
.ContractorRejectResponse = ContractorRejectResponse
.CustomerSignature = CustomerSignature
.ReceivedOnTime = ReceivedOnTime
End With
iReadingCount = 0 'Reset
End If
Catch e As Exception
Me.ComponentMetaData.FireError(1, "script source", e.Message, "", 0, True)
'Finally
End Try
End Sub
View 5 Replies
View Related
Jan 15, 2008
I'm creating a script component that reads from an OLEDB source and writes to an OLEDB destination.
For every input row, I need to output several rows.I tried using the Row.DirectRowToOutput0() method inside a loop in the
Input0_ProcessInputRow routine but that's not working. Should I be using Addrow() instead? If I use Addrow() does this mean it needs to be an asynchronous transformation?
I remember seeing a blog entry (Jamie's?) that did almost exactly what I wanted, but I can't find it now.
Any pointers appreciated
View 13 Replies
View Related
Jan 16, 2008
Well my 1 in/multiple out asynchronous script component was looking fabulous, until I tried to run it.
Turns out you can't step through a script component with the debugger, so I'm kind of stuck.
I'm getting the error 'There is no current row in the buffer. A row may need to be added using the AddRow method.'
Here's the script I'm running. For each input row, it's trying to unstring linefeed-seperated input column data into a set of arrays, then create an output row for each populated occurrence and use Addrow() to write the new row. (According to the MSDN doco I shouldn't need to use CreateNewOutputRows())
Can anyone spot where I'm going wrong?
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
Dim rcobc(0 To 9) As String
Dim rcobcdesc(0 To 9) As String
Dim rcobcbase(0 To 9) As String
Dim rcobcunits(0 To 9) As String
Dim ratechg(0 To 9) As String
Dim ratelevy(0 To 9) As String
Dim ratered(0 To 9) As String
Dim ratetotal(0 To 9) As String
Dim arrposn As Integer
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
rcobc = Split(Row.INASMRCOBC, vbLf)
...(more unstringing here)
ratetotal = Split(Row.INASMRATETOTALGI, vbLf)
For arrposn = LBound(rcobc) To UBound(rcobc)
If rcobc(arrposn) > " " Then
RatelineBuffer.obc = rcobc(arrposn)
...(more assignments here)
RatelineBuffer.valuation = Row.ASMVALUATION
RatelineBuffer.AddRow()
End If
Next arrposn
End Sub
Public Sub CreateNewOutputRows()
'
' Add rows by calling AddRow method on member variable called "<Output Name>Buffer"
' E.g., MyOutputBuffer.AddRow() if your output was named "My Output"
'
End Sub
End Class
View 1 Replies
View Related
Jan 15, 2015
MSDN states the following on: Readable Secondary Replicas (AlwaysOn Availability Groups) for SQL Server 2014:
Limitations and Restrictions:
Change tracking and change data capture are not supported on secondary databases that belong to a readable secondary replica:
Change tracking is explicitly disabled on secondary databases.
Change data capture can be enabled on a secondary database, but this is not supported.
This confuses me: You can not track the changes. However you can enable CDC?
The scenario I am trying to achieve is to use SSIS CDC components on an asynchronous secondary replica. Is this possible? If not what would be other viable approaches?
View 0 Replies
View Related
Nov 16, 2015
I have availability group with 3 node
2 in prod env and other one in DR
all replica configure with Asynchronous statusÂ
I would like to test fail-over first scenario prod env i would like to trying shutdown primary server and move to replica in prod.
I should move to replica prod from cluster service management or from SQL management studio
if i do it from cluster manager do you have power shell script for that
if i do it from SQL management studio i need the command
View 2 Replies
View Related
Jun 1, 2015
I have configured active passive cluster in production environment. And we also have a dr which we have configured with asynchronous mirroring with no witness. Currently active node(node
a) is in sync with dr. When failover happens and the second node(node
b) becomes active, the mirror is broken and goes to disconnected mode.
But when we failback again to node a mirror is connected again and is in sync again. In our setup we have active passive cluster and a standalone server as dr.
View 11 Replies
View Related
Jul 6, 2007
I am working with the Data Flow Task Script Component for the first time. I have created a second Output. In my script I add rows to this output.
I have found that Ssis does not release those rows to the second Output until it has processed all of the incomine pipeline records. This will not work for me as there are going to be a few million records coming down the pipe, so I need the Script Component to as soon as possible release these records downstream for insert into the destination component Ole Db component.
Any help would be greatly appreciated?
View 8 Replies
View Related
Mar 14, 2006
If you have an output that is not synchronous with the input what is the best way of processing the data.
I am currently using a generic queue, and a custom class. I am creating an instance of the class in the ProcessINputRow and then adding it to the Queue.
The CreateNewOutputRows Dequeues the class instances and creates buffer rows.
Is there a better solution?
View 2 Replies
View Related
Jan 23, 2008
Is there by chance a cunning way to make the input columns automatically populate the output of an asynchronous script transformation?
My transformation writes several rows for each input row read. I'm creating some new columns along the way but I'd like all of the input columns to get output each time also. However I can't see any obvious way to achieve this, short of manually defining each column to the output and populating it in the script.
View 3 Replies
View Related
May 9, 2008
Problem: Have to call SPs reside in 2 SQL server DBs which will return the table information, need to merge and perform sort the result and return the final result set. this is something like wrapper SP calling other SPs asynchronoulsy.
Can this be possible in SP / SQL functions to make asynchronous calls?
If yes can you please guide me to achive this.
If no, any alternative approach, or the reason why.
I appreciate your views on this.
-Hemanth
View 3 Replies
View Related
Oct 22, 2007
On URL: http://technet.microsoft.com/en-us/library/ms135931.aspx among other things there is an VB example for "Creating and Configuring Output Columns" of Custom Transformation Component with Asynchronous Outputs:
Public Overrides Sub OnInputPathAttached(ByVal inputID As Integer)
Dim input As IDTSInput90 = ComponentMetaData.InputCollection.GetObjectByID(inputID)
Dim output As IDTSOutput90 = ComponentMetaData.OutputCollection(0)
Dim vInput As IDTSVirtualInput90 = input.GetVirtualInput()
For Each vCol As IDTSVirtualInputColumn90 In vInput.VirtualInputColumnCollection
Dim outCol As IDTSOutputColumn90 = output.OutputColumnCollection.New()
outCol.Name = vCol.Name
outCol.SetDataTypeProperties(vCol.DataType, vCol.Length, vCol.Precision, vCol.Scale, vCol.CodePage)
Next
End Sub
When I copy-paste given code into ScriptMain of Ascynchronous Script Transformation Component I receive error message "sub 'OnInputPathAttached' cannot be declared 'Overrides' because it does not override a sub in a base class."
View 1 Replies
View Related
