how do I determine how many columns there are in a file?

Hi,

I have a few different files in a directory.

I want to take each file and determine which columns and data types it has.

How can I know how many columns there are in a file?

Thank you.

Columns. That depends entirely on what you define a column to be. How is it delimited?

Column data-type. In a file the type of every column is text/char/call it whatever you want. A file is just a text string. You give semantic meaning to those strings (e.g. data-types) which you define yourself.

There are a million and one answers to your question given the information that you have provided. Only you can answer it.

-Jamie

|||

Jamie,

let's assume that I get a csv file from my customer.

I don't know how many columns there are in that file (and I don't want to count it by myself).

I wanted to know which component can take this file and tell me how many columns it has.

This is stage 1.

|||

The Flat File allows you to define a delimiter and therefore tell you how many columns there are.

-Jamie

|||

suppose I use a "flat file" component, and I see that there are 4 columns.

I want to use the "for each" component and apply a "data flow task" on each one of the columns.

The "for each" component will take one column at a time, and run 4 times.

I want to use the same "data flow task" for all of the columns.

How can I do it automatically?

|||

one way is to start a new project in vs as ssis

right click over solution explorer->SSIS Packages and select import and export wizard

select your flat file as source and destination as server, by this way create a new package for every text file, later you can copy and paste of each package contents into one and with little change this will be work for you and hope you get column list by looking at tables.

|||

reut wrote:

suppose I use a "flat file" component, and I see that there are 4 columns.

I want to use the "for each" component and apply a "data flow task" on each one of the columns.

The "for each" component will take one column at a time, and run 4 times.

I want to use the same "data flow task" for all of the columns.

How can I do it automatically?

Data flows operate on datasets, not on a column at a time. Even if you could do what you want to (which you can't - see below) I would recommend not doing it.

The reason that you can't apply the same data-flow to different data set (which in your case would exist of a single column) is because the metadata of the the data-flow (i.e. the columns) is set at design-time and cannot be changed when the package is executed.

-Jamie

|||

This is a good way, thanks for that, but what do I do if I have 30 different files with 100 columns in each one of them, and I don"t want to create 30 different packages?...

I want to create one "smart" packege, that will take a file, take a column, and insert the relevant data into a table on the server.

If I decide to do it with the "for each" component, it will run 30*100 times.

The resault has to be something like this:

fileName columnName dataType

xxx.csv column1 int

xxx.csv column2 date

...

xxx.csv column100 varchar

yyy.csv column1 date

...

How do I do it?

Thank you for your patience.

|||

you can create a raw table with columns from 1 to 500, then fetch every text file into it and call that data flow in your for each loop and then you better know which column tells you about specific table then put the data into that table from raw table by calling just one stored proc from your ssis, and that sp will decide data insert into that specific table based on data

|||

Your only option is to create a "Smart" custom source that is either a script source or a script component. Your custom component would take the column name, or position and output the relevant column into the data flow.

You could also unpivot the data so that your columns were now rows. You then filter the flow based on the column you want to process.