Column metadata from Connection Manager programmatically

Hi all!

My problem I've been struggling with is the following. I have a set of text files (around 70), each with different column numbers and types. I define Flat File Connection Managers for each of them where I can nicely rename, set data types and omit certain columns. I do this once and this will be the basis for the rest of the data process (would be nice programmatically too actually).
I would like to pump each of these text files into SQL Server tables using CREATE TABLE and BULK INSERT (because do it one-by-one is really a pain). The question is:

is there a way to obtain column information (Script Task) from a Connection Manager so I can run CREATE TABLE-s? I just need the names, data type for each nothing fancy...

(I bumped into interfaces like IDTSConnectionManagerFlatFileColumns90, which I cannot handle from the Script Task.)

Any help appreciated!

What your asking is a design-time action, not run-time, and could be done if you load the package and walk round the object model. If using BULK INSERT, then why bother with SSIS Flat File Connections at all?|||

Thanks for the answer. That is exactly I cannot achieve:

Dim mgr As ConnectionManager = Dts.Connections(1)
Dim o As Object = mgr.Properties("Columns").GetValue(mgr)

This returns something (COM IDTSConnectionManagerFlatFileColumns90?) that I cannot handle more. Or am I on the wrong track? Do I need more assemblies and references?

The other question: I've found it very comfortable to define flat file structure using Flat File Connections (UI, data types). On the other hand I need a CREATE TABLE based on a flat file structure. Other ideas maybe?

|||

What I said was that this was probably not the right way to do this. The Script task is using run-time.

If you try and use IDTSConnectionManagerFlatFileColumns90 then you will need another reference. Just look it up in Books Online and it wiull tell you that it is in the Microsoft.SqlServer.DTSRuntimeWrap assembly, so add this reference.

Dim conn As ConnectionManager = Dts.Connections(0)

Dim o As Object = conn.Properties("Columns").GetValue(conn)

Dim xx As Wrapper.IDTSConnectionManagerFlatFileColumns90 = CType(o, Wrapper.IDTSConnectionManagerFlatFileColumns90)

Dim dt As Wrapper.DataType = xx.Item(0).DataType

Dim w As Integer = xx.Item(0).MaximumWidth

The above code seems to work.

|||

Hi darren, how do you get the column name? The Wrapper.IDTSConnectionManagerFlatFileColumns90 doesn't have any 'name' member.

Also, i'm trying to do the reverse of this process, which is to add columns to the connection programmatically? How do i go about this?

I've come as close as getting adding the column into the wrapper.idtsconnectionmanagerflatfilecolumns90 collection, but i have no way of adding a 'name' to it? How do i do that? Here's my code:

dim conn2 as idtsconnectionmanager90 = pkg.connections("FlatFileConn").value

Dim conn3 As Wrapper.IDTSConnectionManagerFlatFile90 = CType(conn2.InnerObject, Wrapper.IDTSConnectionManagerFlatFile90)

Dim mynewcol1 As Wrapper.IDTSConnectionManagerFlatFileColumn90

mynewcol1 = conn3.Columns.Add

mynewcol1.DataType = Wrapper.DataType.DT_BOOL

mynewcol1.ColumnDelimiter = "~"

'<< This is where i can't add the 'name' >>

Some code would be really helpful.

Thanks.

|||

Try the following code after you add the mynewcol1

Dim name As Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSName90

name = TryCast(mynewcol1, Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSName90)

name.Name = "ColumnName"