Can You Please Tell Me Where I'm Going Wrong In My Asynchronous Script Component?
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
ADVERTISEMENT
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
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 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
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
Apr 17, 2006
I'm having a tad bit of trouble getting output from an asynchronous component that I've written and am looking for some insight.
This component takes in a name string passed from upstream and parses the name components into standardized output fields. I'm using an asynchronous component because if the name string contains two names ("Fred & Wilma Flintstone") I'm outputting one row for Fred and one for Wilma. I've gotten it to run and with debugging have observed what appeared to me to be proper execution, but zero rows are flowing out of it.
In my ProvideComponentProperties method, I add the three fields and there associated metadata to the OutputColumnCollection. Is this method where this should occur? It's before the PrimeOutput method, so I didn't know if I should be creating the output columns in ProcessInput (i.e., after the output buffer is provided by PrimeOutput.)
In ProcessInput, I'm using AddRow for each input row and another if it contains a second name, setting the value for each index using the buffer's SetString method, to no avail. I can observe it to this point, but then don't know what's in that output buffer (if I'm using the wrong buffer index value, etc)
Thanks.
View 3 Replies
View Related
Oct 26, 2007
Hello,
I have a package that has a data lfow task. this task imports data from a db2 database (using the IBM Ole DB provider fro db2) and adds it to sql server database table. This package was created on the server. then though version control (using TFS source control) I check out the package on my local machine. and when I open the package I get the foll 3 errors.
Error 1 Validation error. Import Account Num from BMGP_BDR: DTS.Pipeline: The component metadata for "component "DataReader Source" (1113)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
Error 2 Error loading BMAG Download Xref Tables - bmag.dtsx: Microsoft.SqlServer.Dts.Pipeline.ComponentVersionMismatchException: The version of component "DataReader Source" (1113) is not compatible with this version of the DataFlow. [[The version or pipeline version or both for the specified component is higher than the current version. This package was probably created on a new version of DTS or the component than is installed on the current PC.]] at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostCheckAndPerformUpgrade(IDTSManagedComponentWrapper90 wrapper, Int32 lPipelineVersion)
Error 3 Error loading BMAG Download Xref Tables - bmag.dtsx: The component metadata for "component "DataReader Source" (1113)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
Please advice.
Thank you.
View 7 Replies
View Related
Jan 23, 2007
Hi,
I have a package which reads an Access file from a folder. My connection manager to this file is .NET providers for OledbMicrosoft Jet 4.0 OLE DB Provider.
Package works from my computer. But when I execute it on the server as a SQL Agent job, I get
The component metadata for "component "DataReader Source" (1) could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
I copied the mdb file to a folder on the server which my packages have no problem reading data from.
My packages run under the same domain account as defined in proxies.
Appreciate a help.
Gulden
View 4 Replies
View Related
Feb 19, 2006
Im trying to insert a record in my sql server 2005 express database.The following function tries that and without an error returns true.However, no data is inserted into the database...Im not sure whether my insert statement is correct: I saw other example with syntax: insert into table values(@value1,@value2)....so not sure about thatAlso, I havent defined the parameter type (eg varchar) but I reckoned that could not make the difference....Here's my code: Function CreateNewUser(ByVal UserName As String, ByVal Password As String, _ ByVal Email As String, ByVal Gender As Integer, _ ByVal FirstName As String, ByVal LastName As String, _ ByVal CellPhone As String, ByVal Street As String, _ ByVal StreetNumber As String, ByVal StreetAddon As String, _ ByVal Zipcode As String, ByVal City As String, _ ByVal Organization As String _ ) As Boolean 'returns true with success, false with failure Dim MyConnection As SqlConnection = GetConnection() Dim bResult As Boolean Dim MyCommand As New SqlCommand("INSERT INTO tblUsers(UserName,Password,Email,Gender,FirstName,LastName,CellPhone,Street,StreetNumber,StreetAddon,Zipcode,City,Organization) VALUES(@UserName,@Password,@Email,@Gender,@FirstName,@LastName,@CellPhone,@Street,@StreetNumber,@StreetAddon,@Zipcode,@City,@Organization)", MyConnection) MyCommand.Parameters.Add(New SqlParameter("@UserName", SqlDbType.NChar, UserName)) MyCommand.Parameters.Add(New SqlParameter("@Password", Password)) MyCommand.Parameters.Add(New SqlParameter("@Email", Email)) MyCommand.Parameters.Add(New SqlParameter("@Gender", Gender)) MyCommand.Parameters.Add(New SqlParameter("@FirstName", FirstName)) MyCommand.Parameters.Add(New SqlParameter("@LastName", LastName)) MyCommand.Parameters.Add(New SqlParameter("@CellPhone", CellPhone)) MyCommand.Parameters.Add(New SqlParameter("@Street", Street)) MyCommand.Parameters.Add(New SqlParameter("@StreetNumber", StreetNumber)) MyCommand.Parameters.Add(New SqlParameter("@StreetAddon", StreetAddon)) MyCommand.Parameters.Add(New SqlParameter("@Zipcode", Zipcode)) MyCommand.Parameters.Add(New SqlParameter("@City", City)) MyCommand.Parameters.Add(New SqlParameter("@Organization", Organization)) Try MyConnection.Open() MyCommand.ExecuteNonQuery() bResult = True Catch ex As Exception bResult = False Finally MyConnection.Close() End Try Return bResult End FunctionThanks!
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
Mar 16, 2007
In a Data Flow, I have the necessity to use a SSIS variable of type €œObject€? inside Script Component and assign to it the content of 'n' variables of string type.
On exiting from the script the variable of type object should contain something like in the following lines:
AAAAAAAAAAAAAAAAAAAAAAAAAAAAA
BBBBBBBBBBBBBBBBBBBBBBBBBBBBB
CCCCCCCCCCCCCCCCCCCCCCCCCCCCC
DDDDDDDDDDDDDDDDDDDDDDDDDDDDD
€¦€¦€¦€¦€¦€¦€¦.
€¦€¦€¦€¦€¦€¦€¦.
On exiting from the data flow I will use the variable of type Object in a Script Task, by reading each element in a cyclic fashion.
Is there anyone who have experienced something like this? Could anyone provide any example of that?
Thanks in advance!
View 3 Replies
View Related
Aug 13, 2007
Hi all
I'm into a project which uses a lot of views for joining 2 or more tables. Using the MERGE component in SSIS will be a huge effort coz it only has 2 inputs and I gotta SORT the input too.
Isnt it possible to have a VIEW like component that joins more than 2 tables and DOESNT need sorting??
(I've thought about creating views in database engine but it breaks my data floe in SSIS and is'nt a practical solution)
View 4 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
Mar 30, 2006
I am writing a custom dataflow transformation component and I need to get the name of the preceeding component.
I have been trying to find a way to get a reference to the Package object, MainPipe object or IDTSPath90 object (connecting to the IDTSInput90 of my component) from my component because I think from there I can get to the information I want.
Does anyone have any suggestions?
TIA . . . Ed
View 7 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
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
Nov 27, 2007
No idea where this bug crept in from. Have been using SSIS for 1.5 years now without hitting this problem.
I had a script component opening an XML document and parsing it using XPATH. I added some code that uses StreamReader / Streamwriter (closing one stream before starting the other). The code works without issue in my C# app.
And it ran without issue 2-3 times in SSIS. Then suddenly after running my package again, the script component says it completes successfully, yet nothing happens. I set a breakpoint on the first line of code - it never hits it. I add a msgbox as the first line of code - and it never displays.
I then close my package / exit out of ssis ... and then re-open it. When i open my script component, all of my code is GONE. All references that I added are gone.
I tried adding the streamreader/writer process to a dll I created from my c# app ... and added the DLL to the package -- same result.
I can reproduce this on 2 different computers.
Anyone experience this problem ? Any idea how to stop it ? Or debug it ?
Here is a slimmed down code sample of what causes the error :
Public Class ScriptMain
Public Sub Main()
Try
Dim xmlDoc As New XmlDocument
xmlDoc.Load("c:ulkasync_86281519_20070628045850225_4.xml")
MsgBox("xmlLoaded") --this doesn't display once the package starts "acting up"
Catch ex As Exception
MsgBox(ex.Message)
UpdateXML("c:ulkasync_86281519_20070628045850225_4.xml", ex.Message)
End Try
Dts.TaskResult = Dts.Results.Success
End Sub
Private Sub UpdateXML(ByVal fileName As String, ByVal message As String)
Try
Dim invalidChar As String = message.Trim().Substring(message.Trim().IndexOf("0x"), 4)
Dim rd As StreamReader = New StreamReader(fileName)
Dim xml As String = rd.ReadToEnd()
Xml = Xml.Replace(invalidChar, String.Empty)
xml = xml.Replace("", String.Empty)
xml = xml.Replace("<![CDATA[<![CDATA[", "<![CDATA[")
xml = xml.Replace("]]>]]>", "]]>")
MsgBox("replaced")
rd.Close()
Dim wr As StreamWriter = New StreamWriter(fileName)
wr.Write(xml)
wr.Close()
Dim xdoc As XmlDocument = New XmlDocument()
xdoc.Load(fileName)
Catch ex As Exception
UpdateXML(fileName, ex.Message)
End Try
End Sub
End Class
View 4 Replies
View Related
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
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
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 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
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
