Stand Alone Custom Dataflow Component Trying To Access MainPipe...
Feb 4, 2008
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?
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.
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 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"
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.
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)
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 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.
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?
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:
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?
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?
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.
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.
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?
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.
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?
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?
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)
