Programatically create and configure a FlatFileConnectionManager

I am writing some C# code that builds a package programatically. the package contains a flat file connection manager. I have referenced Microsoft.SqlServer.Dts.Runtime.Wrapper in order that I can use the ConnectionManagerFlatFileClass class.

Here is some of my code:

Package p = New package();

ConnectionManager sourceCM = p.Connections.Add("FLATFILE");

sourceCM.Name = "some-name";

ConnectionManagerFlatFileClass cmffc = sourceCM.InnerObject()

I get a compilation error on the last line saying I cannot implicitly cast as ConnectionManagerFlatFileClass. Fair enough, so I change the last line of code to:

ConnectionManagerFlatFileClass cmffc = (ConnectionManagerFlatFileClass)sourceCM.InnerObject()

Now it will compile. But when I run it I get the error:

"Unable to cast COM object of type 'System.__ComObject' to class type "Microsoft.SqlServer.Dts.Runtime.Wrapper.ConnectionManagerFlatFileClass'. COM components that enter the CLR and do not support IProvideClassInfo or that do not have interop assembly referenced will be wrapped in the __ComObject type. instances of this type cannot be cast to any other class; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface"

Can anyone tell me how I can cast the ConnectionManager.InnerObject as a ConnectionManagerFlatFileClass please?

-Jamie

P.S. My ultimate aim here is to set the metdata of each of the columns defined by the flat file connection manager. I can't see how I can do that without referring to the connection manager as a ConnectionManagerFlatFileClass!

OK, it looks like I can reference the Columns collection of my flat file connection manager using

sourceCM.Properties["Columns"]

but that returns me an object of type DTSProperty which I've tried casting to IDTSOutputColumnCollection90 but it says it cannot convert a Microsoft.SqlServer.Dts.Runtime.DtsProperty to Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSOutputColumnCollection90 which, again, makes complete sense!

So, if anyone can tell me how I can programatically access the columns in my flat file connection manager then I'd be very grateful.

-Jamie

|||

DtsProperty.GetValue Method ?

DtsProperty is the definition of the property, and you want the actual value, which is where the GetValue method comes in.

I think the whole idea of this is to abstract you from the real class, such that they can be changed and updated at will without breaking existing code. Not too sure on the deal on binary compatability as we used to think of it, it seems to be less of an issue, but there is probably some good reason!

|||

Thanks mate, but that won't do it. However, I've managed to find the answer elsewhere (and for once in my life I didn't rely on Darren to give me the answer ). Namely, in this sample: http://msdn2.microsoft.com/en-us/library/ms161541.aspx

Here's the code that does it, turns out I DO need the Microsoft.SqlServer.Dts.Runtime.Wrapper namespace:

using wrap = Microsoft.SqlServer.Dts.Runtime.Wrapper;

...

private wrap.IDTSConnectionManagerFlatFile90 ffc = (wrap.IDTSConnectionManagerFlatFile90)sourceCM.InnerObject;

wrap.IDTSConnectionManagerFlatFileColumn90 col = ffc.Columns.Add();

OK, cool. I've got it working. It kinda begs the question tho, why is there no class in the Microsoft.SqlServer.Dts.Runtime namespace that enables us to do it? Its not like configuring a FlatFile connection manager isn't core functionality. I've asked the same question elsewhere (sort of). Hopefully it'll get answered.

-Jamie