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 Complete Forum Thread with Replies
Related Forum Messages:
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 !
SSIS Custom Component, Output Buffer Problem
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 Replies !
Custom Dataflow Component: How Do You Make Properties Editable?
I have a custom component that takes in unicode stream and converts it to ascii text. However I would like to make my default string length and code page editable in the standard GUI editor. Right now I can set the default to 1000 characters, but when I try to change it, it says "Property value is not valid" Any ideas? Thanks!
View Replies !
Stand Alone Custom Dataflow Component Trying To Access MainPipe...
Hi, I've created a stand alone custom dataflow component in VB and I need to set up the connection to the Input and Output components and instantiate it. The only way I've seen this done is to create an entire package and Task then use the TaskHost wrapper object to instantiate the Mainpipe (IDTSPipline90 interface) so that you can create the IDTSPath90 interface and setup the connection to the input and output components... After all that, all that I would like to know is whether it is possible to instantiate the mainpipe interface without creating a package programmatically? I've seen something Darren Green put in an answer to a thread, about accessing the Mainpipe interface in the UI, to the effect that you can access it through IServiceProvider using the interface IDTSPipelineEnvironmentService - I think that's it... But I'd like to know if there is a more straightforward route to instantiating and accessing the Task or data flow directly? Thanks in advance, Will.
View Replies !
Enabling Expression Builder For Custom SSIS DataFlow Source Component
Hi, I have implemented a custom source component that can be used as the data source in the Data Flow task. I have also created a custom UI for this component by using the IDtsComponentUI . But my component does not have the capability of setting the custom properties via the DTS Variables using the Expression Builder. I have looked around for samples on how to do this, but I can only find samples of how to do this for custom Control Tasks, i.e. IDtsTaskUI. My question is, How can implement the Expression Builder in my custom Source component + custom Source UI. Or do you know of any samples which I can look at. Thank you, Jameel.
View Replies !
Script Component As Source: The Value Is Too Large To Fit In The Column Data Area Of The Buffer.
In my quest to get the Script Component as Source to work, I've come upon an error that says "The value is too large to fit in the column data area of the buffer.". Of course, I went through the futile attempt to get debugging to work. After struggling and more searching, I found that I need to run Dts.Events.FireProgress to debug in a Script Component. However, despite the fact that the script says: Code Block Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper Imports Microsoft.SqlServer.Dts.Runtime ... Dts.Events.FireProgress.. I get a new error saying: Error 30451: Name 'Dts' is not declared. Its like I am using the wrong namespace, but all documentation indicates that Microsoft.SqlServer.Dts.Pipeline.Wrapper is the correct namespace. I understand that I can use System.Windows.Form.MessageBox.Show, but iterating through 100 items makes this too cumbersome. Any idea what I may be missing now? Thanks, John T
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 !
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 !
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 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 !
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 !
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 !
SSIS Buffer Problem - Lookup Component
Hi, I am facing a problem with Lookup component in SSIS. I need to lookup from a transaction table for getting some info, But when im trying to implement the same, the Pre-Execute step itself got failed saying like, €œ[DTS.Pipeline] Information: The buffer manager failed a memory allocation call for 524264 bytes, but was unable to swap out any buffers to relieve memory pressure. 9467 buffers were considered and 5956 were locked. Either not enough memory is available to the pipeline because not enough are installed, other processes were using it, or too many buffers are locked. [Tracer [19717]] Error: A buffer could not be locked. The system is out of memory or the buffer manager has reached its quota. [DTS.Pipeline] Error: component "Tracer" (19717) failed the pre-execute phase and returned error code 0xC020204B.€? Component Tracer is the Look up. Tracer is having around 6.5 mil records. Is there any way to allocate more buffers thru buffer manager? Or is there any alternative to solve this problem? FYI, the hard disk free space is more than 250 GB. Thanks in advance.
View Replies !
Global Variables And The Script Component In DataFlow
I can't find anything on how to get to a global variable in a script component in the dataflow. I can get to it in a script task with no problem by using dts.variables but i doesn't appear you can do the dts variables in the script component. I did add it to the readwrite variable list but I haven't been able to access it.
View Replies !
Custom DataFlow Transformation Not Showing Up In Toolbox
I've created a custom data flow tranformation and it isn't showing up in the Tool Box Items to be added under the Data Flow Items tab (right click on tool box, 'Choose Items...', then clicked Data Flow Items). I have done the following: signed the assembly, added to GAC, copied the dll to C:Program FilesMicrosoft SQL Server90DTSPipelineComponents. It worked previously when I was just starting out, however now I cannot see it. What would cause it to not show up? Everything compiles fine. How would I determine how to fix it so that it shows up?
View Replies !
Dates In Slow Changing Dimension Dataflow Transformations Component
I€™m trying to populate a table with fields of date type [DT_DATE] using the Slow Changing Dimension Transformation component. When I add the date fields to the component it would not build the stream. The wizard fails and tells me the date fields are not of the same type. The fields in the destination table are of type €œdate€? and the input columns are of type [DT_DATE]. Am I missing something?
View Replies !
How Can I Initialize Runtimeconnection For My SSIS Dataflow Component From My IDtsComponentUI Correctly?
I am developing SSIS dataflow component. Extended user interface is based on class IDtsComponentUI. Connection properties are created in both standart and my extended editor (Extended user interface). To set up designtime connection I use standart and my extended editor. A main PipelineComponent component have two runtime connection: IDTSRuntimeConnection90 adoConnection1 = ComponentMetaData.RuntimeConnectionCollection.New(); adoConnection1.Name = "Connection 1"; IDTSRuntimeConnection90 adoConnection2 = ComponentMetaData.RuntimeConnectionCollection.New(); adoConnection2.Name = "Connection 2"; ---------------------------------------------------------------------- IDTSComponentMetaData90 dtsComponentMetaData; // current designtime component .... In IDtsComponentUI I use follow fragment of code (for example): _dtsComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager = Microsoft.SqlServer.Dts.Runtime.DtsConvert.ToConnectionManager90(_connections[0]); to set up IDTSRuntimeConnection90[0] connection using connections from a current package. After this operation the RuntimeConnectionCollection[0] is not null within the method PipelineComponent.AcquireConnections((object transaction)). ! But during next launching of Extended user interface the RuntimeConnectionCollection[0] is null within the method PipelineComponent.AcquireConnections((object transaction)). Why do I lose the connection? And why the connections which set up in my Advanced editor do not save in standart editor? Thanks in advance.
View Replies !
Custom Dataflow Transform In Programmatically Built Package
I'm building packages programmatically and all is well. I have a new custom transform that I developed. It also works fine. Now I'm trying to add my new component to my packages when I programmatically build them, and I'm unable to do that. Has anyone added their own custom components to a programmatically built package successfully? I get a COM error on the line that calls ProvideComponentProperties. I've attempted various modifications including not overriding ProvideComponentProperties or just having it do nothing. I always get the same result. What I don't understand is that the custom transform works and handles ProvideComponentProperties fine when it is added to a package in BIDS. Thanks. 'Add new dataflow task combitronics = dataFlow.ComponentMetaDataCollection.New() 'Set assembly to my component combitronics.ComponentClassID = "Ewrap.SSIS.Combitronics" 'Get instance of component Dim instance As CManagedComponentWrapper = combitronics.Instantiate() 'Initialize component ***Error Occurs Here*** instance.ProvideComponentProperties() ...
View Replies !
SSIS DataFlow Task To Generate Custom Columns
Hi All, I am using a Data Flow task which copies data from an Excel Source to a SQL Database Table Destination. From 15 columns I require only 10 columns to be imported to the DB Table. So I have mapped those colums. In SQL DB there is a colum called say X, whose value should be the "Remedy" for all the columns which are imported. Is there any task that can achieve it. Please Help.
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 !
[Flat File Source [8885]] Error: The Column Data For Column &&"CountryId&&" Overflowed The Disk I/O Buffer.
Hi everyone, I am using SSIS, and I got the folowing error, I am loading several CSV files in a OLE DB, Becasuse the file is finishing and the tak dont realize of the anormal termination, making an overflow. So basically what i want is to control the anormal ending of the csv file. please can anyone help me ??? I am getting the following error after replacing the '""' with '|'. The replacng is done becasue some text sting contains "" wherein the DFT was throwing an error as " The column delimiter could not foun". [Flat File Source [8885]] Error: The column data for column "CountryId" overflowed the disk I/O buffer. [Flat File Source [8885]] Error: An error occurred while skipping data rows. [DTS.Pipeline] Error: The PrimeOutput method on component "Flat File Source" (8885) returned error code 0xC0202091. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. [DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038. [DTS.Pipeline] Error: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. [DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0047039. [DTS.Pipeline] Information: Post Execute phase is beginning. apprecite for immediate response. Thanks in advance, Anand
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 !
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 !
|
TRACKER
