Adding Column Attributes For Custom Pipeline Component
I'm building a custom transform component. I want to mark some input columns as keys for deduplicating. In a similar way to the provided Sort component, I want to check those columns and allow pass-throughs (or not) for the others - so next to each input column name I need two checkboxes (1:use for dedupe; 2:include in output if 1 not checked). If a column is checked for use in the dedupe, I want some other attributes to be shown indicating how it will be used. How do I display the checkboxes to let users select which columns to include for deduplication, and then how do I add further attributes underneath (copying the Sort component's look) for selection?
Thanks in advance for guidance and pointers on this.
View Complete Forum Thread with Replies
Related Forum Messages:
Conditionally Adding A Column To My Custom Component
Hi, I am building a custom component have a IDTSCustomProperty90 property that can take the value 'True' or 'False'. Depending on its setting, I want to include (or not include) a column in the output. Any advice on how to go about doing this (with some sample code) would be much appreciated! Here's how I'm declaring the property in ProvideComponentProperties() IDTSCustomProperty90 IncludeErrorDesc = ComponentMetaData.CustomPropertyCollection.New(); IncludeErrorDesc.ExpressionType = DTSCustomPropertyExpressionType.CPET_NONE; IncludeErrorDesc.Name = "Some Name"; IncludeErrorDesc.TypeConverter = typeof(Boolean).AssemblyQualifiedName; IncludeErrorDesc.Value = Convert.ToBoolean(false); Thanks in advance -Jamie
View Replies !
Conditionally Adding A Column To My Custom Component (part 2)
A month or so ago I instigated this thread- http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=243117&SiteID=1 which talked about how to conditionally add a column to my component depending on the value of a custom property. If the custom property is TRUE then the column should appear in the output (and vice versa). Bob Bojanic said I should use the SetComponentProperty() method to do this and that is working pretty well. However, it bothers me that SetComponentProperty() could be called, the column will then be added, and then the package developer could press 'Cancel'. In this instance the value of my custom property would be inconsistent with the presence of the extra column. How do you get around that? Thanks Jamie
View Replies !
Adding Custom Component To The Tool Box
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 Replies !
Adding Error Output To Custom Source Component
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 Replies !
Column Mapping In A Custom Component
Does anyone know how to get destination coulmns to show up in the advanced editor for a custom component? I have a custom flat file destination component that builds the output based on a specific layout. It works as long as the upstream column names match my output names. What I want is to allow non-matching columns to be mapped by the user as they can in a stock flat file destination. The closest that I have been able to come is to get the "column mappings" tab to show up and populate the "Available Input Columns" by setting ExternalmetadataColumnCollection.IsUsed to true on the input. The problem is that the "Available destination columns" box is always empty. I have tried the IsUsed property on the output and pretty much every other property that I could find. On the Input and Output properties all of my columns show up under the output as both External and Output columns. Is there a separate collection for "destination" columns that I can't find? It's getting a little frustrating, is this something that can be done or do I have to write a custom UI to make it happen? Thanks! Harry
View Replies !
Let User Choose Column In Custom Component
Hi, I want to recalculate some columns in a custom component, due to a formula using a single column as factor. What will be the best way to let the user choose the single column (factor) beneath the columns to work with. Is there a way to use customproperties other than strings. DropDowns for example. If so, do you know a tutorial oder code snippet? Thanks
View Replies !
Custom Dataflow Component---add New Column To Buffer
This is trivial I'm sure but I'll be dogged if I can find someone who mentions how to do it. I am attempting to develop a Data Flow Transformation that appends a new column (a string value) into the current stream. I have found plenty of references on how to replace an existing column but I'd really like to just add my new column in there. It doesn't need to be configurable, it can be a static column name. I'll take a solution that allows the column name to be set at design time, don't get me wrong but the magic I'm looking for is how to implement a new column in a stream. Yes, I am well aware of the derived column task but I will be replacing a few hundred instances and I'd much rather just drag an item onto the designer than to drag a derived column, double click it, type in the column name, set the expression and then set the datatype, etc. Anyone spare a moment to enlighten me? Pardon the lack of formatting, this BB doesn't play with Opera (I know, I'm a heretic) using System; using System.Collections; using System.Runtime.InteropServices; using Microsoft.SqlServer.Dts.Pipeline; using Microsoft.SqlServer.Dts.Pipeline.Wrapper; using Microsoft.SqlServer.Dts.Runtime.Wrapper; using Microsoft.SqlServer.Dts.Runtime; namespace Microsoft.Samples.SqlServer.Dts { [ DtsPipelineComponent ( DisplayName = "Nii", Description = "This is the component that says Nii.", ComponentType = ComponentType.Transform ) ] public class Nii : PipelineComponent { public override void ProcessInput(int inputID, PipelineBuffer buffer) { if (!buffer.EndOfRowset) { while (buffer.NextRow()) { try { // do something here to } catch (Exception e) { ComponentMetaData.FireInformation(0, ComponentMetaData.Name, "There was an error on row " + buffer.CurrentRow.ToString() + ". The error is: " + e.Message + " : " + e.Source + " : " + e.StackTrace, "", 0, ref fireEventAgain); } } } } }
View Replies !
Custom Data Flow Component Column Mapping Question
Hi, I'm having my first go at developing a destination adapter which will send data to an update Web Service. I've got some rather big gaps in my understanding. I've been following the various samples I've found on the net and have validated my mapping and picked up all the available column names and datatypes which are appearing in the Input and Output Properties tab of the Advanced Editor but I only have a tab for "Input Columns" and not "Column Mappings". Which method defines the availble columns for the user to map? Let me know if I haven't given enough information. cheers
View Replies !
Custom Transform Component, Change Type Or Add Output Column
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 Replies !
DB Design / Custom Attributes
I apologize ahead of time for the long post...Background:Working on a CRM type custom application. The application is for anevent management company. The company will provide the application forother organizations to manage their own events. The events includeconferences, corp meetings, sales meetings, etc...An event planner will define what information is needed for an attendeeto register for an event. We will be providing a standard list ofattributes for the event planner to select from. This list includespersonal information (name, address, phone numbers), air travelinformation (preferred carriers, departure airports, etc...), hotelinformation, etc...we've included all of the information available tous from the business's previous experience. As far as the databasegoes, all of the standard information given to use will be normalized.The problem is each event may have unique information that needs to becollected that is not part of the standard list of attributes. Forexample, if McBurgers is planning an event, the event planner may wantto collect an attendee's McBurger employee code.Depending on the uniqueness of the event, there may be up to 200 uniqueattributes defined for it. This number comes from researching eventsplanned in the last 5 years. The number of attendees for an event rangefrom 100 to 10,000. The company expects about 3000 events per year.Database DesignI've done a fair amount of research and found a couple of options tomeet our requirements, more specifically the need for event planners todefine custom attributes for an event.1-)DynamicColumns:Add an Event specific custom attributes table. The table would looksomething like this:Event_McBurger05AttendeeID | McBurgerEmployeeCode | HiredDate | SomeOtherAttribute-Join Bytes! | AxEt356 | 01/01/2004 | Other val 22-)EAV:Add an EAV (entity, attribute, value) table. The table would looksomething like this:Event_AttributesEventCode | AttendeeID | Attribute | Value-McBurger05 | Join Bytes! | McBurgerEmployeeCode | AxEt356McBurger05 | Join Bytes! | HiredDate | 01/01/2004McBurger05 | Join Bytes! | SomeOtherAttribute | Other val 2The Value attribute would be a character (probably varchar) datatype.3-)Stronger Typed EAVHave an EAV table for each data type. The tables would look somethinglike this:Event_CharAttributesEventCode | AttendeeID | Attribute | CharValue-McBurger05 | Join Bytes! | McBurgerEmployeeCode | AxEt356McBurger05 | Join Bytes! | SomeOtherAttribute | Other val 2Event_DateAttributesEventCode | AttendeeID | Attribute | CharValue-McBurger05 | Join Bytes! | HiredDate | 01/01/2004There would be one Event_[DataType]Attribute table for each of thedatatypes allowed.Pros/Cons1-)DynamicColumnsPros:-Data integrity can be enforced-Simpler queries for reporting-Clearer data model for understanding data storedCons:-Row size limitation of 8k must be managed (probably need to addanother table if run out of room.-Stored procedures for CRUD operations would need to dynamicallycreated ORNeed to use dynamic SQL on the database or application.-Adding/Removing columns on the fly can be very error prone2-)EAVPros-Static CRUD stored procsCons-No data integrity-Complex queries for reporting-Worse performance than option 1.-Table can get BIG...fast.3-)Stronger Typed EAVPros-Static CRUD stored procs-Better data type integrity than EAVCons-Complex queries for reporting-Worse performance than option 1-Table can get BIG...fast.If you are still reading this...thank you!The Questions:-Are there other options other than the 3 described above? Or are thesepretty much it with slight variants.-Does anyone see any missing Pros/Cons for any of the options thatshould be considered?-Is there a "preferred" method for what I am trying to do?I suspect this will come down to the lesser of three devils. Justtrying to figure out which of the three it is.We have prototyped the three options and are leaning towards option 1and 3.Any comments/suggestions are appreciated.Thx
View Replies !
Reference To Preceeding Component From Custom Dataflow Transformation Component
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 Replies !
Expression Issue With Custom Data Flow Component And Custom Property
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 Replies !
How To Dynamically Generate Drop Down Values For Custom Property In Custom Component
Need help in custom component with custom property. I have seen examples on how to have an enumeration for custom property value selection; however, the enumeration is pre-set. In my case, I will need to generate the values for a drop down list at custom component design time. Specifically, I will need the input column names in the drop down list for the user to select as the custom component is configured. I have no idea how to pass the collection of input column names and the user selected value back and forth to my UITypeEditor subclass. How can I achieve that? Thanks in advance for any input, Yan Yi
View Replies !
Expression Editor On Custom Properties On Custom Data Flow Component
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 Replies !
How To Update A Dimension Column With The Pipeline Tasks
I have been working with DTS and ETL in data warehousing projects for several years and my question is this. You can only update a dimension column with SSIS by using TSQL-update statements. There is no way to do this except issuing TSQL from the control flow or the data flow? This subject is not mentioned in Wrox SSIS book nore in Kirk Haseldens book. When you run the SCD task in the data flow you will get an OLEDB command that actually do this, issue a TSQL-statement. Is this correct? Regards Thomas Ivarsson
View Replies !
Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer Column Ordinal From Name?
Hi, I need to access columns from a data flow by ordinal position in a script transformation (I'm parsing an excel file which has several rowsets across the page). The first problem I encountered is the generated BufferWrapper does not expose the columns collection (i.e. Input0Buffer(0) does not work) but I got around that by implementing my own ProcessInputs(InputId, Buffer) method instead of using the wrapper. My problem now is that the column ordinals are in some random order (i.e. Column "F1" is ordinal 1 but Column "F2" is 243). Where in the object model can I map between the name and the ordinal - it's not jumping out at me? Dave PS Why is the script editor modal, it's frustrating having to switch between the Visual Studio environment and the VSA one.
View Replies !
XML With Dynamic Attributes Based On Column Values
I have been banging my head against the wall for TWO days. I have gone back and forth with a very patient guy on thescripts.com. You can see the ridiculous thread here http://www.thescripts.com/forum/threadnav628777-1-10.html If you have time, at least peruse that so we don't go in circles. Anyway, if you guys can help me solve this, I will be forever grateful!! Here is the "basic" problem: Here is an example for TWO different entities in the database. EntityID XmlFieldName Value 1 City Austin 1 State TX 1 Country US 2 CityName Los Angeles 2 StateCode CA 2 CountryCode US 2 Zip 111111 Here is how the two different results should be where EntityID = 1 <Address City="Austin" State="TX" Country="US"/> where EntityID = 2 <Address CityName="Los Angeles" StateCode="TX" CountryCode="US" Zip="111111"/> Notice how the attribute names (City or CityName, State or StateCode, etc) are based off the XmlFieldName and I don't know in advance what the possible values will be? I also don't know how many attributes there will be, but they can be different per entity, depending on how they have set up an address in our application. Another thing to note, is that I kind of have this working in an sproc using PIVOT and generating a table with the values that have the correct dynamic column names (you can see this on my other thread I posted above) but I REALLY need this to not use dynamic SQL (so can use it in a function) if possible and be able to be used in a select statement, whether it be a temp table as I would like to get a result set back that I can do a FOR XML RAW on. If this is confusing, it is because I am delerious. OR is there a way to return a table from an SPROC that has dynamic columns built? Please help!! Thanks so much!!! Brian
View Replies !
Name Localization Of Custom Component
Hi, I am facing a issue. I have a custom component, whose name should be localized. For this I have to read the resource file and set the Display Name and Description of the DtsPipelineComponent attribute. As per the MSDN Documentation http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.pipeline.localization.dtslocalizableattribute.aspx We need to set the LocalizationType property of the DtsPipelineComponent attribute to the type of the resource class that contains the resources for the component. When ever I set this attribute to the Resource Type I get this Error "Error 1 'ETI.HPC.Oracle.Dest.OracleDestinationResources' is a 'type', which is not valid in the given context. " Do I have to inherit this resource class for some class. What I am doing wrong. After that how do I use DtsLocalizableAttribute to set the Name and Description. If any one is having any Example please Send me the Link. Thanks Dharmbir
View Replies !
Add A Variable From Custom Component
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 Replies !
Custom Component Seems To Un-register Itself?
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 Replies !
How To Deploy A Custom Component?
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 Replies !
Custom Component For Web Logs
I'm looking for an SSIS custom component that parses web logs. Most of our logs are from Apache web server. I did a web search but didn't find anything available. Does anyone know if this is available? I'd rather buy or re-use, if someone has already written this. thanks!
View Replies !
Custom Component Development
I am looking for a sample component (with source) for sql reporting service that will accept a recordset. I have been able to create a component that accepts a single value.
View Replies !
Stuck From The Start On Custom Component
Imports System Imports Microsoft.SqlServer.Dts.Pipeline Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime Imports Microsoft.SqlServer.Dts.Runtime.Wrapper Namespace SSISComp <DtsPipelineComponent()> _ Public Class UpperCase Inherits PipelineComponent End Class End Namespace The above is fine, i.e. at least i get no IDE complaints. But, as soon as i try to make the attribute even remotely useful, i get Mr Squiggly and he no wanna go away. (and most of this is pulled from samples so I must be missing someyhing embarrasingly obvious: This one will get you DisplayName is not declared Namespace SSISComp <DtsPipelineComponent(DisplayName = "Hello")> _ Public Class UpperCase Inherits PipelineComponent End Class End Namespace This one will get you Too many argumnets to Public sub New() Namespace SSISComp <DtsPipelineComponent(, , , "Hello", , , , , , )> _ Public Class UpperCase Inherits PipelineComponent End Class End Namespace So, what is the syntax for this Note, we have a reference to Microsoft.Sqlserver.Pipelinehost (as well as dtspipelinewrap,dtsruntimerap,and managedts) Clues please Dave
View Replies !
Custom Component Development Questions
I have looked at everything I could find on custom components, and I'm still confused about what has to be implemented in ReinitializeMetadata. The first custom component I'm trying to build is very similar to the Audit component, a derived column synchronous transformation that should be able to create any non-empty subset of four types of output columns. I haven't found anything that does much with the inputs, the samples all seem to rely on RemoveInvalidInputColumns. I haven't found any substantial explanation of what this method actually does, or whether there are cases it doesn't handle. If it really handles all problems on the input side I don't need to know the details, but I'd like to hear some confirmation that this is the case. For outputs, most of the sample projects seem to just call the base method. Some projects throw out all the output columns and create new ones to match the input columns one-to-one. Project Real throws away all the output columns and external metadata columns and creates a standard set of output columns with external metadata. The RegEx sample project uses information in a component custom property to tell it what output columns to expect, then adds missing columns and deletes unwanted columns. All of these approaches are using some reference information to inform it which outputs belong and which don't. What do you do when there is no other reference, just the output column and external metadata column collections? The description for this method is "fix the errors found in Validate", but these projects just delete everything that wasn't perfect and recreate anything missing. In cases where the number and types of output columns require some user configuration, maybe a few custom properties, it seems a little high-handed to throw it all away and make them start from scratch. Or do you assume that this method isn't called unless the component state is really screwed up? It seems to me that simply adding or deleting input and output columns in the Advanced Editor would trigger ReinitializeMetadata because the external metadata would be out of sync. If it reacted by immediately re-adding an optional default column you deleted, or deleting an unconfigured column you just added, it would be pretty annoying. Assuming that I wanted ReinitializeMetadata to actually fix things, I have some specific questions: 1 If an output column is not mapped to an external metadata column (or mapped to a nonexistent emc), should the output column be deleted or should a matching external column be created? 2 Same question reversed, if an external column is not mapped to any output column, should it be deleted or a matching output column created? 3 Would it make sense to look for compatible unmapped output and external columns and remap them? 4 If the user adds or deletes an output column in the advanced editor, was that an output column or an external metadata column? And another thing, is there a definative list of which methods should be marked as not cls-compliant?
View Replies !
Query On Custom Source Component
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 Replies !
Custom Source Component Problem?
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 Replies !
Standards For Custom Component Development
Hi, Maybe I didn't search hard enough on BOL, but does Microsoft have a documented set of standards regarding custom component development for SSIS. Things like: - extend this base class, implement this interface - override these methods - follow these naming conventions - best practices Thanks, - Joel
View Replies !
Custom Component (Transformation) Questions
I've been trying to figure this out on my own for pretty much all of today, and part of last week. I've downloaded samples, searched this forum, blogs, etc. So I figured I would post, since it's the end of the day, and I'm not much further along. I'm working on a custom transformation component, whose main function is to use SQL encryption/decryption to encrypt/decrypt data from the input columns, into the output columns. The component needs two strings, a key name and a certificate name, as well as the connection manager it should use to connect to SQL which will do the encryption/decryption. Here's where I'm stuck: 1) How can I provide the key/certificate names via properties? What I'm expecting/looking for is a way to add these two properties at the component-level, which would show up under the "Custom Properties" section of the properties pane (currently, this only has one property, "UserComponentTypeName"). These key/certificate values will be used for all input columns. 2) How do I access the connection managers from within the component? What is the best way to go about using a connection manager from within my component to connect to SQL and perform the encryption/decryption? In a custom task, this was fairly simple, but it seems that same concept won't work on a transformation component. 3) Is there a better way to go about accomplishing this (column encryption via SQL from within SSIS)? Am I going about this all wrong? As I said, I've searched for direction, but there seems to be next to nothing in the regards of a good reference for creating custom transformation components. I've looked at two MS samples, but can't seem to make any sense out of them. Thanks in advance. Jerad
View Replies !
Custom Component: How Can I Have Two Input Datasources?
Hi all. Can you help me? I'm trying to build a custom component that recieves two datasources (like for instance the union all) . I first started by adding a new IDTSInput90 in the ProvideComponentProperties, but when I tried to use the component I got an error that has very helpful : =================================== 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. (Microsoft Visual Studio) =================================== Error at Data Flow Task [Replica Transformation [1289]]: System.Runtime.InteropServices.COMException (0xC0048004): Exception from HRESULT: 0xC0048004 at Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSOutputCollection90.get_Item(Object Index) at MyCustomSSISComponent.SampleComponentComponent.ProvideComponentProperties() at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProvideComponentProperties(IDTSManagedComponentWrapper90 wrapper) =================================== Exception from HRESULT: 0xC0048004 (Microsoft.SqlServer.DTSPipelineWrap) ------------------------------ Program Location: at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HandleUserException(Exception e) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProvideComponentProperties(IDTSManagedComponentWrapper90 wrapper) at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ProvideComponentProperties() at Microsoft.DataTransformationServices.Design.PipelineTaskDesigner.AddNewComponent(String clsid, Boolean throwOnError) This is my ProvideComponentProperties: Code Snippet public override void ProvideComponentProperties() { RemoveAllInputsOutputsAndCustomProperties(); ComponentMetaData.UsesDispositions = true; IDTSInput90 input = ComponentMetaData.InputCollection.New(); input.Name = "Staging Data"; input.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent; IDTSInput90 input2 = ComponentMetaData.InputCollection.New(); input2.Name = "Replica Data"; input2.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent; // Add the output IDTSOutput90 output = ComponentMetaData.OutputCollection.New(); output.Name = "Replica Output"; output.SynchronousInputID = input.ID; output.ExclusionGroup = 1; // Add the error output AddErrorOutput("StagingErrorOutput", input.ID, output.ExclusionGroup); // Adds columns AddXmlColumn(); IDTSOutputColumn90 column0 = ComponentMetaData.OutputCollection[1].OutputColumnCollection.New(); column0.Name = m_SyncStatusColumnName; column0.SetDataTypeProperties(DataType.DT_STR, 1, 0, 0, 1252); IDTSOutputColumn90 column1 = ComponentMetaData.OutputCollection[2].OutputColumnCollection.New(); column1.Name = m_AS400ImportedDateColumnName; column1.SetDataTypeProperties(DataType.DT_DATE, 0, 0, 0, 0); } Since I'm new to SSIS I'm following Josh's SSIS Xmlify Data Flow Task sample. Thanks.
View Replies !
Custom Component Install Wizard (.MSI)
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 Replies !
Evaluate Expression In Custom Component
Is there a way to evaluate an expression (like the derived column component) in a custom component? If so where should I look first? Is there an example? An extremely simple sample is to put in an expression and evaluate one column and then add that to another column to create a new column. i.e. newcolumn = column1 + column2. I realize that the derived column allows me to do this but I'm trying to figure out if it is possible to do this in a custom component without having to build my own expression evaluator. Thanks! -Thames
View Replies !
Custom Component And Unit Testing
Hello. I want to test my custom component with unit tests and i thought i must only initilize the component to play around with it. But when i calling the ProviderComponentProperties method and there the RemoveAllInputsOutputsAndCustomProperties method a NullReference exception is thrown. After debugging the test i had seen that the ComponentMetaData of the component is null. Is there a way to initilize the ComponentMetaData? The Code of the Component looks like this: Code Block [DtsPipelineComponent( DisplayName = "TestSourceAdapter", ComponentType = ComponentType.SourceAdapter, IconResource = "TestSourceAdapter.TestSourceAdapter.ico" )] public class TestSourceAdapter: PipelineComponent { public override void ProvideComponentProperties() { RemoveAllInputsOutputsAndCustomProperties(); IDTSOutput90 output = ComponentMetaData.OutputCollection.New(); output.Name = "TestSourceAdapter"; ComponentMetaData.OutputCollection ["TestSourceAdapter"].ExternalMetadataColumnCollection.IsUsed = true; ComponentMetaData.ValidateExternalMetadata = false; ComponentMetaData.UsesDispositions = true; ComponentMetaData.Name = "TestSourceAdapter"; ComponentMetaData.Description = "TestSourceAdapter"; ReinitializeMetaData(); } ... } And in test i call only this Code Block TestSourceAdapter testAdapter = new TestSourceAdapter(); _testAdapter.ProvideComponentProperties(); i hope anyone can help regards kai
View Replies !
Custom Source Component Timeout
Just thought it would be useful to post this in case anyone comes across the same. When building a SSIS Customer Source Component to access data from an OLEDB data source, the component will timeout after around 30 seconds. Unless in the PrimeOutput overridden method one sets. oledbcommand.Timeout = 0 Stuart
View Replies !
Custom Transformation Component Tutorial
Hi all, Is there any tutorial to learn how custom transformation component works? maybe a blog, pdf or something... Specifically, i need to learn how to generate an output column composed from 3 input columns. The problem is i dont know how to set the column value... anyone have some sample code? Thanks!
View Replies !
Creating A Custom Transformation Component Walkthrough
Microsoft published a "Creating a custom transformation component Walkthrough" published on http://www.microsoft.com/downloads/details.aspx?FamilyID=1c2a7dd2-3ec3-4641-9407-a5a337bea7d3&DisplayLang=en Does anyone know where to get the Hands-On Lab Files mentioned? Thanks Alex
View Replies !
Saving Packages And Custom Component Data
I am looking for some advice regarding saving custom component data when saving packages. For custom "properties", this is not an issue, as saving the package will save these properties. Howver, I also have information for each column (besides the properties that columns provides, like Name and DataType) that I need to save if a package were to be saved, and right now it does not save because I am using my own objects to store the data. I am wondering as to how I can save this information. I have looked up on serialization, but I would like to know if there is another way besides serialization to save this inforamtion as I'd rather not save this to a seperate file. Thanks
View Replies !
How To Restrict A Custom Component To Be Used Only In The Event Handler Tab?
Hi, One of my plan is to develop a custom task compoent to log the errors into the destination provided as paramter to the component. Now how can I restrict the use of this component in the event handler tab only. can something like this be done? Also extending this a little further, is the below thing possible? The custom component should expose custom properties which should allow the user to add the destinations for logging the errors. It will be a predefined list (window event log, text file, sql table etc) and the user will be allowed to check what all he/she wants. Then if the user has selected sql table then ask for the oledb connection and the table name, if text file is selected the ask for the path of the file. Apology if I am asking too many questions around the same thing (error handling). There may be a better way to acheive what I am trying to acheive but then I have no idea about it. Your guidance will be of great help. Again, Thanks a lot for helping this extra curious guy who wants to try and develope generalized compoenents if possible. Regards, $wapnil
View Replies !
Building Custom Component - Marking All Inputs
I have been building a custom component with the default GUI. I want to have all the input fields selected to be passed through to outputs without having to explicitly check each one in the GUI. Is there some method or property to set on the input to do this?
View Replies !
How Do I Order Columns In My Custom Destination Component?
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 Replies !
How Do I Programatically Fail My Custom Destination Component?
I am writing a custom dataflow destination that writes data to a named pipe server running in an external process. At runtime, in PreExecute, I check to see if the pipe exists. If it does not exist I want to be able to fail the component and gracefully complete execution of my package. Question is, how do I do this? If I make a call to ComponentMetaData.FireError() that will only create an OnError event and not actually stop the execution. So, how to I halt the execution of my component and return the error message? Thanks, ~ Gary
View Replies !
Warnings That I Don't Understand When Building A Custom Component
Hi, I have a custom component that compiles just fine on one environment. I copied and pasted the code to another environment and now I'm getting compilation warnings: Warning 1 CA1014 : Microsoft.Design : '*******.Seer.Ssis.Common' should be marked with CLSCompliantAttribute and its value should be true. *******.Seer.Ssis.Common Warning 2 CA1705 : Microsoft.Naming : Correct the capitalization of type name 'PPDMDataLineageComponent'. C:PROJECTSSeerCode*******.Seer.Ssis.CommonPPDMDataLineageComponent.cs 13 Chevron.Seer.Ssis.Common Warning 3 CA1062 : Microsoft.Design : Validate parameter 'buffer' passed to externally visible method PPDMDataLineageComponent.ProcessInput(Int32, PipelineBuffer):Void. C:PROJECTSSeerCode*******.Seer.Ssis.CommonPPDMDataLineageComponent.cs 99 Chevron.Seer.Ssis.Common Any ideas? Thanks in advance -Jamie
View Replies !
Icon Not Displayed Correctly For Custom Component
So i finally figured out how to create custom transform and add an icon to it. However - when i added the component to toolbox it appears as a file icon (when i didnt have icon it appeared as a blue box) - in the data flow designer it appears as the correct image. From BOL wrote:"The Data Flow toolbox uses the 16x16, 16-color image type, while the design surface of the data flow tab uses the 32x32, 16-color image type. Both are default image types for icons created using Microsoft Visual Studio 2005." I assume this has something to do with it - my ico is default 32x32 - however, what would be the way to fix it?
View Replies !
Error OutPut In Custom Source Component
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 Replies !
Selecting Connection In Custom Destination Component UI
Hi there, I am writing a Custom Destination component with a custom UI. The UI contains a combo box which contains the connection names of type €œFLATFILE€?. I also have provided a button which would create a new connection of type €œFLATFILE€? by making a call to CreateConnection method of IDtsConnectionService. The combo box gets properly updated showing the connections of type €œFLATFILE€? but on clicking on the new Connection button the application hangs up. Am I missing something or is there some other way to do it? The function are the events handlers which are called by the UI. void form_GetConnections(object sender, AvailableColumnsArgs args) { Debug.Assert(this.ServiceProvider != null, "Service Provider not valid."); this.ClearErrors(); try { IDtsConnectionService connectionService = (IDtsConnectionService)this.ServiceProvider.GetService(typeof(IDtsConnectionService)); if (connectionService != null) { ArrayList temp_Connections = connectionService.GetConnectionsOfType("FLATFILE"); args.AvailableColumns = new AvailableColumnElement[temp_Connections.Count]; for (int i = 0; i < temp_Connections.Count; i++) { ConnectionManager runtimeConnection = (ConnectionManager)temp_Connections; args.AvailableColumns.AvailableColumn = new DataFlowElement(runtimeConnection.Name, runtimeConnection); } } } catch (Exception ex) { this.ReportErrors(ex); } } void form_GetNewConnection(object sender, AvailableColumnsArgs args) { Debug.Assert(this.ServiceProvider != null, "Service Provider not valid."); this.ClearErrors(); try { IDtsConnectionService connectionService = (IDtsConnectionService)this.ServiceProvider.GetService(typeof(IDtsConnectionService)); if (connectionService != null) { connectionService.CreateConnection("FLATFILE"); ArrayList temp_Connections = connectionService.GetConnectionsOfType("FLATFILE"); args.AvailableColumns = new AvailableColumnElement[temp_Connections.Count]; for (int i = 0; i < temp_Connections.Count; i++) { ConnectionManager runtimeConnection = (ConnectionManager)temp_Connections; args.AvailableColumns.AvailableColumn = new DataFlowElement(runtimeConnection.Name, runtimeConnection); } } } catch (Exception ex) { this.ReportErrors(ex); } } Has anyone else run into this? I am running SQL 2005 9.0.1399 and VS 2005 8.0.50727.42 (RTM.50727.4200) on Windows Server 2003 Enterprise Edition SP1. Any suggestions would be welcome. The code sample is an extension to the RemoveDuplicates sample (Dec 2005) which comes along with the SQL Server. TIA, Robinson
View Replies !
SSIS Custom Component/task Examples
Hi, Near the end of 2005 Microsoft made available some sample C# apps that implemented custom SSIS tasks and components. I think Doug Ladenshlager may have had a heavy hand in building them. Does anyone know where they are? I can't darned well find them! -Jamie
View Replies !
|
TRACKER
