Flattened results: How to get column names?

I read on this forum that you can get flattened results using this code:

table = New DataTable()

Using dataAdapter As AdomdDataAdapter = New AdomdDataAdapter(command)

dataAdapter.Fill(table)

End Using

When you do this, the first few columns in the datatable are actually row header values. That's exactly what I want. Correspondingly I expect the first few rows to be the column header values. But they are not. Why not? Is there any easy way to get the column header values?

I think you will find that the ColumnName property of the column contains some sort of concatenation of the column headers.

|||The column name is something like [abc].[def].&[ghi].[jkl].&[mno]. I guess I could parse this myself. So parsing this is the only way to get the column headers? That seems unreliable. For row headers I can easily find them in the body of the table, and I don't have to do any parsing.

|||

This is just how the flattened rowsets work. If you have ever used a linked server in SQL Server back to an SSAS server you will have seen similar result sets. The only other option would be to get a cellset (which is multi-dimensional) and flatten it yourself.

sql

flat files without column names; how to map over 250 columns

hi,

i am sure this question must have been anwsered some where, but after a lot of searching i still have not find the anwser.

i have flat files without column headers (267 columns in total).
since i have the file's description i have created a table to house these extracts with the columns in the same order as in the flat files.
additionally, i have an excel containing a list of the column names their data types and length as well as their position on the flat files.
in the old, DTS would map the columns without headers to those columns in the destination table using their order, in which case it works like a breeze for me. but i can not find a way of doing that in SSIS.

i would very much appreciate someone's assistance on this one since i am sure that there must be a better way than manually (and tediously & error prone) to map all those columns.

thanks in advance

nicolasdiogo wrote:

hi,

i am sure this question must have been anwsered some where, but after a lot of searching i still have not find the anwser.

i have flat files without column headers (267 columns in total).
since i have the file's description i have created a table to house these extracts with the columns in the same order as in the flat files.
additionally, i have an excel containing a list of the column names their data types and length as well as their position on the flat files.
in the old, DTS would map the columns without headers to those columns in the destination table using their order, in which case it works like a breeze for me. but i can not find a way of doing that in SSIS.

i would very much appreciate someone's assistance on this one since i am sure that there must be a better way than manually (and tediously & error prone) to map all those columns.

thanks in advance

I'm afraid its manual. I agree this is very tedious but I don't agree that its any more error prone than DTS. One of the big problems with DTS was that it would often do things for you but do things wrongly. One of the aims with SSIS was to put all responsibility in the hands of the package developer rather than letting DTS "guess".

I'm sure your answer will be "but it always works for me in DTS" and that's a valid point of course. I guess you just can't please all of the people all of the time. Personally I think the approach of putting all decisions in the hands of the developer is a good thing - but that's just me.

How long would it take to map 250 columns? I would guess at about 20 minutes? Yes its onerous but its not TOO much time out of your day is it? It must have taken you about 5 minutes to write the email above.

Another alternative might be to see if the import/export wizard does any auto-mapping for you. I'm not sure what it does to be honest but it might be worth checking out.

Sorry, I think you may be stuck doing it manually.

-Jamie

flat files without column names; how to map over 250 columns

hi,

i am sure this question must have been anwsered some where, but after a lot of searching i still have not find the anwser.

i have flat files without column headers (267 columns in total).
since i have the file's description i have created a table to house these extracts with the columns in the same order as in the flat files.
additionally, i have an excel containing a list of the column names their data types and length as well as their position on the flat files.
in the old, DTS would map the columns without headers to those columns in the destination table using their order, in which case it works like a breeze for me. but i can not find a way of doing that in SSIS.

i would very much appreciate someone's assistance on this one since i am sure that there must be a better way than manually (and tediously & error prone) to map all those columns.

thanks in advance

nicolasdiogo wrote:

hi,

i am sure this question must have been anwsered some where, but after a lot of searching i still have not find the anwser.

i have flat files without column headers (267 columns in total).
since i have the file's description i have created a table to house these extracts with the columns in the same order as in the flat files.
additionally, i have an excel containing a list of the column names their data types and length as well as their position on the flat files.
in the old, DTS would map the columns without headers to those columns in the destination table using their order, in which case it works like a breeze for me. but i can not find a way of doing that in SSIS.

i would very much appreciate someone's assistance on this one since i am sure that there must be a better way than manually (and tediously & error prone) to map all those columns.

thanks in advance

I'm afraid its manual. I agree this is very tedious but I don't agree that its any more error prone than DTS. One of the big problems with DTS was that it would often do things for you but do things wrongly. One of the aims with SSIS was to put all responsibility in the hands of the package developer rather than letting DTS "guess".

I'm sure your answer will be "but it always works for me in DTS" and that's a valid point of course. I guess you just can't please all of the people all of the time. Personally I think the approach of putting all decisions in the hands of the developer is a good thing - but that's just me.

How long would it take to map 250 columns? I would guess at about 20 minutes? Yes its onerous but its not TOO much time out of your day is it? It must have taken you about 5 minutes to write the email above.

Another alternative might be to see if the import/export wizard does any auto-mapping for you. I'm not sure what it does to be honest but it might be worth checking out.

Sorry, I think you may be stuck doing it manually.

-Jamie

Flat Files having Only Column Names in One file and the Rows in the other

Sorry if this question had already been answered previously. I was unable search the forum on this topic. How will I merge these and then configure the first row as Column names (As this helps to map to the destination column names automatically)

As far as I know, you can't. Why would you? A quick mapping exercise, name the columns etc, it not that much of a job...

You might do it by merging your files using a DOS command. Google merging text files in dos.|||

Hi Crispin,

Thanks for your reply

Unfortunately, We have hundreds of columns for each of the Flat File Sources among 20 in each Package. Naming them will obviously take ages.

Thanks

Subhash Subramanyam

|||I've had the same situation.

If you specify the file with the column names as the source, it'll do it for you. After that, you give it the files with the actual data in. Once columns are named, you do not need to do anything else with it. Unless the column order changes - in which case, you have a problem...

Otherwise, look at merging the files using DOS.

Another option you could try is to use Data Defractor for text files. Might be overkill for text but works non the less...

Flat file connection is skipping columns

The data file contains column names in the first row. Excel imports the file correctly. I can see the tabs in UltraEdit32. But...the flat file connection just skips over a column.
In the preview window, it appears to skip the column entirely.

However, when the data is imported, data from the non recognized column goes into a column that is mapped to receive data from another column shifting the data in to the next column.

I am using the CopyColumn and the SQL Destination controls.

Any ideas on how to make this work?

Thanks,
IanOCan you copy 'n paste some of the data here for us to look at?

Fixed Header in Reporting Service 2000

I want to create a report with SQL Server Reporting Service 2000 where I need to have a fixed header and one fixed column containg names of each row items. I tried to find "Fixed Header" property in table but could not locate it anywhere.Can anyone please kindly tell me where should i look for it?Or how to achieve this functionality?

Thank you in advance.

Bharat Gadhia.

FixedHeader is a new feature that got added in RS 2005. It was not available in 2000.|||

Hi Fang,

Thank you for the reply.

Can you tell me how to achieve this functionality in SQL Server Reporting Service 2000?

Bharat Gadhia.