Conditionally Adding A Column To My Custom Component (part 2)
Mar 13, 2006
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.
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.
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.
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.
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
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
I am new to SSIS. I am working in adding SSIS components programmatically. I have added Data flow task, Lookup transformation, OLEDB Source and OLEDB Destination.
Now, i am facing problems in adding Custom Script tranformation component programatically. Please help me out.
Hey. I need to see if "/" is present in the column11 and if it's then just pass it as is or do the substring part. How do I get this to work? It's giving me an error. This is for a TimeDate column. I can get a 20060813 or 2006/08/13.I'm using the below and it's giving me an error saying that It should be DT_BOOL and I'm trying to return DT_I4.
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?
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); } } } } }
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?
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.
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.
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",
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)
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.
Hi all, How to package sql server database as part of our custom application (VB application) using install shield or any software. That setup should detect the presence of sql server. If sql server is not present in the client machine, then our set up should install the sql server and deploy our database. If sql server is present in the client machine, then our set up should install our database alone. How to do that. Please help me. Regards, S.Sevugan.
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?
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.
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?
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.
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?
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?
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.
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() {
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();
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.
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)
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?
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
