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"

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"

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"

Column Delimiter as a SPACE?

I'm in a flat file connection manager editor and I have a flat file where the columns are separated by a space. Does anyone know how to specify a space in the column delimiter option? I've tried {' '} and {\s} but these don't work. Not sure what the syntax is for indicating a space. Thanks in advanceJust hit the space bar in the column delimiter box. Make sure that the first row is delimited by a space (even if it's a header).

Collations and simple import from a csv-file

Hello. Here a two different problems that occur one and a while when I try to import a textfile to SQL Server 2005.

I have a flat file connection to a csv-file that originally is a export from an AS4000 DB2 database This csv-file is defined as a variable length file Why do SSIS automatically interpret the length of each column as varchar(50)? It does not matter if a define the same file as a fixed lenght file. The problem is that I will get a warning that information in columns will be truncated. I would like to do a direct export to the SQL Server 2005 table with shorter varchar fields. I can solve this by using the task for transforming data types but this only works on the text fields. This task cannot transform a string to a decimal or an integer column in the SQL Server 2005 table. Is there no other way than having a staging table between the text file and the SSIS-data pipe? I also get a lot of collation or code page errors even if we set the receiving columns to nvarchar and nchar. Is there any good article on this subject? Code page errors

Last question. Is there parameter support in the data reader source connection?

I appreciate all help.

Regards

Thomas Ivarsson

You can control the data types and lengths that SSIS uses for flat files by going to the Advanced area in the flat file connection manager. It will show you the columns defined, their data types, and their lengths, and you can alter the values to match what you want.

Parameter support in the data reader source: I don't believe that it does, but you can create the SQL that you want by building it in a variable, then assigning the variable to the Data Reader Source.SQL Command property using an expression.

|||

Thank's for the answere jwelch. I have actually tried the advanced properties and have set the flat file connection data types according to the target table data types.

Perhaps I have another error but this configuration will work?

I will have a look at your advice regarding the data reader source.

Regards

Thomas Ivarsson