When To Create Columns And Metadata For Custom Asynchronous Component Output

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


ADVERTISEMENT

Create Output Columns Based On Input In Custom Component

Aug 28, 2007



I'm trying to create a fairly simple custom transform component (because I've read that's the easiest one to create) which will take one column from a flat file source and based on the first row create the output columns.
I'm actually trying to write a component that will solve the now well known problem with parsing CSV files in SSIS. I have a lot of source files and all have many columns so a component that can read in the first line from the CSV file and create the output columns automatically will save me lots of time when migrating the old DTS packages.

I have the basic component set up but I'm stuck when trying to override the OnInputPathAttached method because I don't know how to use the inputID to get the first line from the input (the buffer).
Are there any good examples for creating output columns dynamically based on the input buffer?
Should I just give up on on the transform and create a custom source component instead?

View 5 Replies View Related

How Make Script Component Output 2 Asynchronous?

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

A Custom Component For Use As A VIEW In SSIS- Is It Possible To Create One MERGE Like Component With More Than 2 Inputs

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

Error OutPut In Custom Source Component

May 11, 2006

For the Custome source Component ErrorOutput, should I go for asynchronous / synchronous Output.

If i go for synchronous output

// Create the error output.
IDTSOutput90 errorOutput = ComponentMetaData.OutputCollection.New();
errorOutput.IsErrorOut = true;
errorOutput.Name = "ErrorOutput";
errorOutput.SynchronousInputID = What Id is required here;
errorOutput.ExclusionGroup = 1;


Is it the IDTSOutput90 InPut.ID / OutPut.ID which should be assigned.

Thanks Regards

Anil

View 5 Replies View Related

The Component Metadata For Component DataReader Source (1113) Could Not Be Upgraded To The Newer Version Of The Component.

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

The Component Metadata For Component DataReader Source Could Not Be Upgraded To The Newer Version Of The Component.

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

Adding Error Output To Custom Source Component

Dec 6, 2007

Hi all,
I saw a couple of other posts here on this topic, but none quite got to my issue.
I'm trying to add error output to a custom source component (not a script, a custom component). The samples all seem to deal with transform components, and my issues seem to be unique to source components.

I have the following code related to error handling ...

Public Overloads Overrides Sub ProvideComponentProperties()

...

Dim output As IDTSOutput90 = ComponentMetaData.OutputCollection.New

output.Name = OUTPUTCOLUMNNAME


output.ExternalMetadataColumnCollection.IsUsed = True

ComponentMetaData.UsesDispositions = True

output.ErrorRowDisposition = DTSRowDisposition.RD_NotUsed

output.ErrorOrTruncationOperation = "Something got truncated or blew up"

Dim errorOutput As IDTSOutput90 = ComponentMetaData.OutputCollection.New

errorOutput.Name = ERRORCOLUMNNAME

errorOutput.IsErrorOut = True

...
End Sub


Public Overloads Overrides Sub ReinitializeMetaData()


Dim output As IDTSOutput90 = ComponentMetaData.OutputCollection(OUTPUTCOLUMNNAME)

Dim outColumn As IDTSOutputColumn90 = output.OutputColumnCollection.New


outColumn.Name = strName

outColumn.SetDataTypeProperties(DataType.DT_I4, 0, 0, 0, 0)



Dim metadataColumn As IDTSExternalMetadataColumn90 = output.ExternalMetadataColumnCollection.New


metadataColumn.Name = outColumn.Name

metadataColumn.DataType = outColumn.DataType

metadataColumn.Precision = outColumn.Precision

metadataColumn.Length = outColumn.Length

metadataColumn.Scale = outColumn.Scale

metadataColumn.CodePage = outColumn.CodePage

outColumn.ExternalMetadataColumnID = metadataColumn.ID


outColumn.ErrorRowDisposition = DTSRowDisposition.RD_NotUsed

outColumn.ErrorOrTruncationOperation = "Something Truncated!"

outColumn.TruncationRowDisposition = DTSRowDisposition.RD_NotUsed

Dim errorOutput As IDTSOutput90 = ComponentMetaData.OutputCollection(ERRORCOLUMNNAME)

Dim errorColumn As IDTSOutputColumn90 = errorOutput.OutputColumnCollection.New

errorColumn.Name = outColumn.Name

errorColumn.SetDataTypeProperties(DataType.DT_I4, 0, 0, 0, 0)
...
End Sub

The confusions I have are:
a) the stock advanced properties editor (I haven't provided a custom one) doesn't seem to "realize" that I have an error output, so provides no method to configure. I am believing it would need to know which Output columns can have their error/truncation redirected. I'd have thought setting ErrorRowDisposition on my output column would have been enough to trigger this ??
b) since I don't have any means of configuring, not surprisingly, when I try to connect my error output, designer complains that, "Ths error output cannot receive any error rows. This occurs for several reasons: Input columns or output columns are not yet defined. Error handling is not supported by the component. Error handling is not configured for the component."
c) UsesDispoistions would seem to be appropriate only for a transform component

Thanks for reading, and appreciate any help or pointers.
Bill

View 5 Replies View Related

SSIS Custom Component, Output Buffer Problem

Mar 27, 2007

Hi Guys,

I created a SSIS custom component, transformation (Asynchronous) with one Input collection and 2 output collections.

The SSIS Package which includes the Component I created works well in the Business Intelligence Studio, but when the same Package is run in the 'Execute Package utility' It fails to run. ( when you Double click on the dtsx file)

The cause of the failiure is

public override void PrimeOutput(int outputs, int[] outputIDs, PipelineBuffer[] buffers)

method receives only one output buffer when executed using the 'Execute Package Utility' { outputs = 1 , buffer.Length = 1 } ( when executed in the BI studio, the method receives parameters of both the output buffers that I expect { outputs = 2 , buffer.Length = 2 } )

The property ComponentMetaData.OutputCollection.Count = 2 as well. Yet the PrimeOutput method provides only 1 buffer.

The Validation Succeeds on both instances, which I assume means that Meta Data is Provided Properly.


What would be the reason for the same pakage to run in 2 different ways like this,

What might I have missed out to do, to make the package run in different ways on 'Business Intelligence Studio' and 'Execute Package Utility'

Thanks a lot



Below are some of the lines from the ProvideComonentProperties Method which deals with the output Collection, Isn't this sufficient for the PrimeOutput to provide 2 output buffers?





ProvideComponentProperties()









public override void ProvideComponentProperties()
{

RemoveAllInputsOutputsAndCustomProperties();
base.RemoveAllInputsOutputsAndCustomProperties();
base.ProvideComponentProperties();

//other function calls

IDTSOutput90 output1 = ComponentMetaData.OutputCollection[0];
output1.Name = "Output1";
output1.Description = ".......................";
extracted.SynchronousInputID =0;


IDTSOutput90 output2 = ComponentMetaData.OutputCollection.New();
output2.Name = "Output2";
output2.Description = "..........................";
output2.SynchronousInputID = 0;

//other function calls
}

View 3 Replies View Related

Custom Transform Component, Change Type Or Add Output Column

Jun 26, 2006

Would anyone happen to have any pointers or know of any good code examples to either programmatically change the type of an input column when it is passed through the component, or add a new column to the output? I am extracting data from an Oracle database which is in Julian date format (represented within SSIS as a DT_NUMERIC column) and I need to to either transform the input column holding it into a date column, or to dynamically add a new output column holding the transformed data.

Many thanks

View 1 Replies View Related

Integration Services :: Custom Source Component - No Datatype Set When User Add Output Column

Aug 17, 2015

I'm writing a custom source component that reads data from a SharePoint list with dynamic mapping to output columns. It's my first custom component and it's based on several samples and tutorials from Internet

Output columns are not created by the component itself, they must be added by user at design time. The component makes dynamically an association between SharePoint fields and available output columns at run-time (based on an mapping table).

I made a very basic skeleton and I encounter a problem when I add a column to output: it has no datatype and when I try to set one I have an the error Property value is not valid, The component xxxxxx does not allow setting output column datatype properties.

Imports System
Imports Microsoft.SqlServer.Dts.Pipeline
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
<DtsPipelineComponent(ComponentType:=ComponentType.SourceAdapter,
DisplayName:="SharePoint Dynamic Assoc List Source",

[Code] ....

View 4 Replies View Related

How Do I Order Columns In My Custom Destination Component?

Nov 17, 2006

Here is the situation. I have created a package that takes 50 columns from a comma delimited flat file. I then validate and clean the data. Next I add two columns that were not in the original source file. These two columns need to be in the 5th and 9th column position when the file is then re-written to a text file. How do i get those two columns to write out in the desired order? Any ideas?



K. Lyles

View 1 Replies View Related

Tips On Creating Output Columns In A Custom Transformation

Aug 14, 2007

I would like my transformation to automatically create an output column for each input column. Any tips? I can't seem to determine which event to listen to or method to override.

View 3 Replies View Related

Asynchronous Script Component

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

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 View Related

Asynchronous Script Component Error...um ..help?

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

Asynchronous Outputs On Script Component Best Practice

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

Reference To Preceeding Component From Custom Dataflow Transformation Component

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

Expression Editor On Custom Properties On Custom Data Flow Component

Aug 14, 2007

Hi,

I've created a Custom Data Flow Component and added some Custom Properties.

I want the user to set the contents using an expression. I did some research and come up with the folowing:





Code Snippet
IDTSCustomProperty90 SourceTableProperty = ComponentMetaData.CustomPropertyCollection.New();
SourceTableProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
SourceTableProperty.Name = "SourceTable";






But it doesn't work, if I enter @[System:ackageName] in the field. It comes out "@[System:ackageName]" instead of the actual package name.

I'm also unable to find how I can tell the designer to show the Expression editor. I would like to see the elipses (...) next to my field.

Any help would be greatly appreciated!

Thank you

View 6 Replies View Related

Expression Issue With Custom Data Flow Component And Custom Property

Apr 2, 2007

Hi,



I'm trying to enable Expression for a custom property in my custom data flow component.

Here is the code I wrote to declare the custom property:



public override void ProvideComponentProperties()

{


ComponentMetaData.RuntimeConnectionCollection.RemoveAll();

RemoveAllInputsOutputsAndCustomProperties();



IDTSCustomProperty90 prop = ComponentMetaData.CustomPropertyCollection.New();

prop.Name = "MyProperty";

prop.Description = "My property description";

prop.Value = string.Empty;

prop.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;



...

}



In design mode, I can assign an expression to my custom property, but it get evaluated in design mode and not in runtime

Here is my expression (a file name based on a date contained in a user variable):



"DB" + (DT_WSTR, 4)YEAR( @[User::varCurrentDate] ) + RIGHT( "0" + (DT_WSTR, 2)MONTH( @[User::varCurrentDate] ), 2 ) + "\" + (DT_WSTR, 4)YEAR( @[User::varCurrentDate] ) + RIGHT( "0" + (DT_WSTR, 2)MONTH( @[User::varCurrentDate] ), 2 ) + ".VER"



@[User::varCurrentDate] is a DateTime variable and is assign to 0 at design time

So the expression is evaluated as: "DB189912189912.VER".



My package contains 2 data flow.

At runtime,

The first one is responsible to set a valid date in @[User::varCurrentDate] variable. (the date is 2007-01-15)

The second one contains my custom data flow component with my custom property that was set to an expression at design time



When my component get executed, my custom property value is still "DB189912189912.VER" and I expected "DB200701200701.VER"



Any idea ?



View 5 Replies View Related

Enable Error Handling When Writing Custom Source Component /custom Error Handling Component.

Apr 21, 2006

1) We are writing a custome Source component for Oracle with OCI calls, Could some one please let me know how to Enable Error Handling for the Same,

2) Is it possible to write Custome Error Handeling Component for SSIS? if yes could you please help me on how to write it.

Thanks in advance.

View 1 Replies View Related

Error: The Component Has Inconsistent Metadata

Jul 11, 2006



I have a Source Query with this sql set as a property expression:

"SELECT Category, Server_Name, Entitle_UserID,User_SubID,Start_Time,End_Time,Entitle_User_Name,Stat_Name,Stat_Count,Stat_Type,pk,create_date,run_num,Average,Median,Maximim FROM tbl_ws_stats WHERE pk > " + (DT_STR, 100, 1252)@[pk_var]

There is a message : 'The component has inconsistent metadata.'

Then when I click on the Source Query: 'The component is not in a valid state. Do you want the component to fix itself automatically?'

I also notice that there are no columns on the Column Mappings tab and no way to add columns.

How can I correct?

Thanks

View 9 Replies View Related

Adding Custom Property To Custom Component

Aug 17, 2005

What I want to accomplish is that at design time the designer can enter a value for some custom property on my custom task and that this value is accessed at executing time.

View 10 Replies View Related

Retain Input Columns Through An Asynchronous Transformation?

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

Custom Properties In External Metadata Not Writeable Using The Advanced Editor

Jun 30, 2006

Hello,

I'm working on a custom dataflow destination component. It makes use of the External Metadata Collection. I also use Custom Properties with the external metadata collection.

When I open the destination component using the Advanced Editor, and select an External Metadata Collection and change the Custom Property it always changes back to the original value.

Additionally the method SetExternalMetadataColumnProperty never gets called.

Here is a little Test Component that surfaces the problem:



[DtsPipelineComponent(ComponentType=ComponentType.DestinationAdapter, DisplayName="Test Destination")]
public class Class1 : PipelineComponent
{
public override void ProvideComponentProperties()
{
base.ProvideComponentProperties();

ComponentMetaData.InputCollection.RemoveAll();
IDTSInput90 input = ComponentMetaData.InputCollection.New();
input.Name = "Name";
input.ExternalMetadataColumnCollection.IsUsed = true;

IDTSExternalMetadataColumn90 ext = input.ExternalMetadataColumnCollection.New();
ext.Name = "ExtName";
IDTSCustomProperty90 customProp = ext.CustomPropertyCollection.New();
customProp.Name = "Custom Property";
customProp.Value = "Hallo";
}

public override IDTSCustomProperty90 SetExternalMetadataColumnProperty(int iID, int iExternalMetadataColumnID, string strPropertyName, object oValue)
{
return base.SetExternalMetadataColumnProperty(iID, iExternalMetadataColumnID, strPropertyName, oValue);
}
}

Any ideas how to make that work?

Georg

View 2 Replies View Related

How To Update The Source Columns'metadata Automatically?

Dec 14, 2006

Take a Dataflow Task, with an OLEDB Source Component and an OLEDB Destination Component in it. The Source component's Source is stored in a SQLQuery variable, and the Destination component's Destination is stored in a TableName variable. The Dataflow Task is put into an For Loop container.

I just want to do one thing:

In the For Loop, everytime it send a new value to the SQLQuery variable and the TableName variable, then I could use it to transfer many tables in a same logic.

My question is: Really, I could send new values to the variables and make it go. But everytime it says "The external metadata column collection is out of synchronization with the data source columns." Because the external metadata was recorded and not be change automatically everytime of Loop.

How to update the source columns'metadata automatically?

View 3 Replies View Related

Add A Variable From Custom Component

May 19, 2006

Hi

I am writing a custom transformation component that utilises a user variable.

Before using the variable at run time I am checking that the variable exists, but it would be nice to be able to add it if it does not. I cannot find any documentation on the subject, though I can see that the Variables class is derived from a ReadOnlyCollectionBase.

Is there a way to add a user variable with package scope from a custom component, either at run time or design time?

Thanks . . . Ed

View 1 Replies View Related

Custom Component Seems To Un-register Itself?

Apr 10, 2006

I've run into this a second time now. I'm hoping for some resolution AND guidance for proper build, save, etc. protocols in BIDS.

I've coded a custom component, included post-build procedures to add it to the GAC, selected it from the "Choose Items..." menu, and successfully added to a data flow. I've then been able to succesfully debug it.

This was all Friday afternoon. I shut down my BIDS session(s) and called it a week. This morning when I open BIDS, I get the following error when I attempt to add it to a data flow task:

TITLE: Microsoft Visual Studio------------------------------The component could not be added to the Data Flow task.Could not initialize the component. There is a potential problem in the ProvideComponentProperties method.------------------------------ADDITIONAL INFORMATION:Error at Data Flow Task [DTS.Pipeline]: Component "component "" (16)" could not be created and returned error code 0x80131600. Make sure that the component is registered correctly.------------------------------Exception from HRESULT: 0xC0048021 (Microsoft.SqlServer.DTSPipelineWrap)------------------------------BUTTONS:OK------------------------------

What would explain this? I'm new to VS/BIDS. Am I improperly shutting down my BIDS sessions? Is a save required for my code? Does "Build <component>" not automatically save?

Also, when I re-build a component after making a change to it, what do I need to do in my package's Data Flow task? Delete the current component, re-add it, and configure it again? I'm sure there are certain things that require one type of re-initialization vs. others (i.e., making a change to the Design Time code vs. making a change to code within the ProcessInput method.)

What can I do to properly register this now? I'm dead in the water and have no idea what to do. Any help's appreciated.

View 7 Replies View Related

How To Deploy A Custom Component?

Sep 26, 2007

Hi

I developed a €śdata flow source€? in my computer and it is ready!
It works in my computer, but how do I deploy this component in to the server? I have any programming environment on it.
Any Idea?

View 1 Replies View Related

Adding Custom Component To The Tool Box

Nov 20, 2006

Hi,

I was trying the SSIS programming samples,that comes with installation of SQL Server 2005, I want to load the ChangeCase DLL in the toolbox.

The readMe file gives the direction. It does not work. It says you will see component in once you see DataFlow Task button( clicking Choose Items in the tools box).

I cannot load the custom component to the toolbox, Can I get some help.

Thank you,









View 1 Replies View Related

Query On Custom Source Component

May 10, 2006



Hi

in the acquireconnection method Using the below statment I can get a connection Object

oledbConnection = cmado.AcquireConnection(transaction) as OleDbConnection;

from the connection object I can get the connectionstring from the object by calling

oledbConnection.connectionstring() property which will have all the details like DataBase, UserName & other Inofrmation but there is no password Info.

How to get the password Information, I need that information since I will use that info to make OCI calls to fetch the data from the Oracle database in m,y custome source component.

any help is much appriciated

thanks in advance.

View 10 Replies View Related

Custom Source Component Problem?

Mar 23, 2006

Hi,I have developed a custom source component to get data from a generic odbc connection with some special caracteristics.

The component works fine by getting and mapping the output fields etc...

The only two problems existing are that when i run the task it says that the data flow has no components inside... how is this possible since i have my source mapped to a flat file inside that data flow?

This is the error:


SSIS package "BVEIT000D.dtsx" starting.

Warning: 0x80047034 at BVEIT000D_<EMPRESA> TXT, DTS.Pipeline: The DataFlow task has no components. Add components or remove the task.

Information: 0x4004300C at BVEIT000D_<EMPRESA> TXT, DTS.Pipeline: Execute phase is beginning.

SSIS package "BVEIT000D.dtsx" finished: Success.

The other problem is that if i want to <ignore> a certain source column the component already shows me an error saying that the no column with ID 0 was found...



Any one with experience in creating custom components?



Regards,

View 6 Replies View Related

Custom Component Install Wizard (.MSI)

Nov 9, 2007

Hi huys,
can you tell me how you create a MSI file for your custom component.

basically i've created a SSIS custom component, and i want to distiribute among a few people, i guess instead of providing step by step instructions it would be easy if they just clicked on packaged msi file.

i've seen many people putting their custom components on the web as MSI, how do you do it?

cheers

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved