Hi All I have been searching high and low for some sample code that creates a custom destination component, similar to the ado.net destination. I can find code that writes out to file or that performs a merge using a custom ui. However, this doesn't help me with the initial setup. So far I have tried the following: Added the following to ProvideComponentProperties() - ComponentMetaData.RuntimeConnectionCollection.New(); --For the ado.net connection.. ComponentMetaData.InputCollection.New() to map the input columns I have also tried adding logic to to ReinitializeMetaData() but this only show external columns when one refreshes (obviously) However I am at a loose end, as to how the receive the external columns (columns mapping). Or even pass the table name to allow the metadata to be populated correctly A point in the right direction would much be appreciated.Cheers David

here you can see how to use Script Component as a Destination (and there is also a sample there which explained how to write to flat file): http://msdn.microsoft.com/en-us/library/ms135939.aspxhttp://www.rad.pasfu.com

Hi Thanks, however I am looking for sample code that creates a Custom Component (particularly focusing around the initialization and properties). I have already created a script component that inserts into an ado.net destination. My focus is trying to incorporate this into a Custom Component. However I am really struggling to find either a tutorial or sample code. As mentioned, the only sample code that I can find is for either a file flat destination or a custom ui interface. Unfortunately this doesn't help help in trying to get to grips with how to setup input/output columns. I even I tried the examples in "SQL SEVER 2012 Integration Services" "CHAPTER 18 PROGRAMMING AND EXTENDING SSIS" (only flat file destination) and " Microsoft SQL Server 2008 Integration Services unleashed " "Writing a Destination Adapter".Cheers David

I fear that this may be one that cannot be answered without the advise of going to a third party toolCheers David

Good to hear you're trying this out. I don't have a destination sample in mind for you that uses a connection manager - although I'd suggest you search through the SSIS Community Tasks and Components site to see if there's something there that could assist. A lot of those components are hosted on CodePlex, and are open source. You should also not limit yourself to a destination component - sources would use the same initialization and property code. I've written a transformation that uses a connection before - but I'm a little rusty, so take what I say with a grain of salt as I read my code. I used a connection pool so I could actually open parallel instances of the connection to get more done faster, so I'll have to decode that to get to the essentials, and hopefully not leave anything out. In ProvideComponentProperties, I told SSIS I wanted a connection by doing what you did - here's a simple recap: ComponentMetaData.RuntimeConnectionCollection.RemoveAll(); RemoveAllInputsOutputsAndCustomProperties(); // Add a single input. IDTSInput100 input = ComponentMetaData.InputCollection.New(); input.Name = "input"; input.HasSideEffects = true; // Add a single output. IDTSOutput100 output = ComponentMetaData.OutputCollection.New(); output.Name = "output"; output.SynchronousInputID = input.ID; // Add the connection manager. IDTSRuntimeConnection100 adoNetConnection = ComponentMetaData.RuntimeConnectionCollection.New(); adoNetConnection.Name = "ADONet Connection"; It appears as though I provided two public properties that looked like this (this._componentMetaData is ComponentMetaData - I just saved a reference to it in my class): public string ConnectionManagerID { get { return this._componentMetaData.RuntimeConnectionCollection[0].ConnectionManagerID; } } public ConnectionManager ConnectionManager { set { this._componentMetaData.RuntimeConnectionCollection[0].ConnectionManagerID = value.ID; this._componentMetaData.RuntimeConnectionCollection[0].ConnectionManager = (IDTSConnectionManager100)DtsConvert.GetExtendedInterface(value); this.ReleaseConnections(); this._adoNetConnectionType = AdoNetConnectionType.Unknown; } } Since they're public, VStudio can see them, which puts them in the property pages to edit. Elsewhere in the code - Validate and the runtime stuff, you can refer to ComponentMetaData.RuntimeConnectionCollection[0] like above. Whenever you want to use your connection to get something done - at design time or runtime, use the connection manager's AcquireConnection method. For example, in my ReinitializeMetadata method, I query my connection for some information about a table. The name of the table is in a string property I maintain (nothing to do with the connection, just a standard property you have to persist on your own). So I just craft a SQL statement, make a SqlCommand object, run the query to get what I want, and go. You may want to do something similar to what I do - although I'm sure I'm not doing it the best way. I create a SELECT TOP 1 * SqlCommand, then ExecuteReader on it, then GetSchemaTable on the reader object, converting .Net types to SSIS types. You'll then have to set up your output columns manually by adding columns to the OutputColumnCollection of the right type and other characteristics. There are lots of samples of that kind of thing, I think. Hope that helps. Talk to me now on

Good to hear you're trying this out. I don't have a destination sample in mind for you that uses a connection manager - although I'd suggest you search through the SSIS Community Tasks and Components site to see if there's something there that could assist. A lot of those components are hosted on CodePlex, and are open source. You should also not limit yourself to a destination component - sources would use the same initialization and property code. I've written a transformation that uses a connection before - but I'm a little rusty, so take what I say with a grain of salt as I read my code. I used a connection pool so I could actually open parallel instances of the connection to get more done faster, so I'll have to decode that to get to the essentials, and hopefully not leave anything out. In ProvideComponentProperties, I told SSIS I wanted a connection by doing what you did - here's a simple recap: ComponentMetaData.RuntimeConnectionCollection.RemoveAll(); RemoveAllInputsOutputsAndCustomProperties(); // Add a single input. IDTSInput100 input = ComponentMetaData.InputCollection.New(); input.Name = "input"; input.HasSideEffects = true; // Add a single output. IDTSOutput100 output = ComponentMetaData.OutputCollection.New(); output.Name = "output"; output.SynchronousInputID = input.ID; // Add the connection manager. IDTSRuntimeConnection100 adoNetConnection = ComponentMetaData.RuntimeConnectionCollection.New(); adoNetConnection.Name = "ADONet Connection"; It appears as though I provided two public properties that looked like this (this._componentMetaData is ComponentMetaData - I just saved a reference to it in my class): public string ConnectionManagerID { get { return this._componentMetaData.RuntimeConnectionCollection[0].ConnectionManagerID; } } public ConnectionManager ConnectionManager { set { this._componentMetaData.RuntimeConnectionCollection[0].ConnectionManagerID = value.ID; this._componentMetaData.RuntimeConnectionCollection[0].ConnectionManager = (IDTSConnectionManager100)DtsConvert.GetExtendedInterface(value); this.ReleaseConnections(); this._adoNetConnectionType = AdoNetConnectionType.Unknown; } } Since they're public, VStudio can see them, which puts them in the property pages to edit. Elsewhere in the code - Validate and the runtime stuff, you can refer to ComponentMetaData.RuntimeConnectionCollection[0] like above. Whenever you want to use your connection to get something done - at design time or runtime, use the connection manager's AcquireConnection method. For example, in my ReinitializeMetadata method, I query my connection for some information about a table. The name of the table is in a string property I maintain (nothing to do with the connection, just a standard property you have to persist on your own). So I just craft a SQL statement, make a SqlCommand object, run the query to get what I want, and go. You may want to do something similar to what I do - although I'm sure I'm not doing it the best way. I create a SELECT TOP 1 * SqlCommand, then ExecuteReader on it, then GetSchemaTable on the reader object, converting .Net types to SSIS types. You'll then have to set up your output columns manually by adding columns to the OutputColumnCollection of the right type and other characteristics. There are lots of samples of that kind of thing, I think. Hope that helps. Talk to me now on

Thank you. This definitely helpful and I will take your tips on-boardCheers David