Force IS to use column headings

Hi,
I've got an IS package which reads a lot of records from a text file and loads that into the database. The text file has column such as Firstname, Lastname, phone number etc and same as the database table.

The problem:
IS works fine if I have the text file columns in the same order as the database columns but for example if have phone number in the place of firstname (in the text file) IS puts the phone numbers as firstname in the database and moves all the columns dow the order.

Is there anyway I could force IS to use the heading names in the text file and put it in the appropriate database columns?

Thanks guys...

The connection manager defines the ordering in the text file so if IS is putting your data into the wrong columns in the database it is because the connection manager is defined incorrectly. If your files vary their order of columns then you would need different connection managers (and therefore different sources) for each ordering.

Matt

|||Thanks for the reply but the problem I'm facing is the text file may not have some of the columns or the columns will be in different order etc. I don't know what the file contains at the time of loading.

Is it possible for me to get IS to load what ever columns are in the text file and just put null (in the database) for the once we are missing?

|||

If you really have no idea what is coming in until it's loaded, then my suggestion would be to load the text file, including the first row with the names, into a SQL table with columns called "col1", "col2", "col3" etc. up to the max you will have. That gets you over the problem of loading the table using a single data flow task.

Then the problem is one of how to split the data into the relevant columns in your "proper" destination table. ;-D

I'm still gettng my head round the new tools in SSIS, so personally I wouldn't know how to do it (maybe conditional split?). What I would do would be to write a T-SQL stored procedure to parse the first row and construct an SQL string to select the columns from the table.

The example below gives you the idea.

Hope this helps,

Rich

Code to follow --

CREATE TABLE [dbo].[tbl_RAW](

[col1] [varchar](50) COLLATE Latin1_General_CI_AS NULL,

[col2] [varchar](50) COLLATE Latin1_General_CI_AS NULL,

[col3] [varchar](50) COLLATE Latin1_General_CI_AS NULL

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[tbl_People](

[Name] [varchar](50) COLLATE Latin1_General_CI_AS NULL,

[Phone] [varchar](50) COLLATE Latin1_General_CI_AS NULL,

[Sex] [varchar](50) COLLATE Latin1_General_CI_AS NULL

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[tbl_ValueList](

[col1] [varchar](50) COLLATE Latin1_General_CI_AS NULL,

[col2] [varchar](50) COLLATE Latin1_General_CI_AS NULL,

[col3] [varchar](50) COLLATE Latin1_General_CI_AS NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

TRUNCATE TABLE tbl_RAW;

TRUNCATE TABLE tbl_People;

TRUNCATE TABLE tbl_ValueList;

-- Populate RAW

INSERT INTO dbo.tbl_RAW

Values ('sex','Name','Phone');

INSERT INTO dbo.tbl_RAW

Values ('Male','Eric','1234');

INSERT INTO dbo.tbl_RAW

Values ('Male','Tim','00000');

INSERT INTO dbo.tbl_RAW

Values ('Female','Simone','9876');

INSERT INTO tbl_ValueList

SELECT top 1 *

FROM dbo.tbl_RAW

DECLARE @.ValueList varchar(50)

DECLARE @.strSQL varchar(100)

DECLARE @.col1value varchar(50)

SELECT @.ValueList = '(' + col1 +','+ col2 +','+ col3 +')' FROM tbl_ValueList

SELECT @.Col1Value = col1 FROM tbl_ValueList

SET @.strSQL = 'INSERT INTO dbo.tbl_People ' + @.ValueList + 'SELECT * FROM dbo.tbl_RAW WHERE col1 <> '''+ @.col1Value +''''

print @.strSQL

EXECUTE (@.strSQL)

SELECT * FROM dbo.tbl_People

-- End of Code--

|||

I'm having a similar problem.

I'm using CSVDE.exe to do a bulk export of Active Directory users. The problem is that the column order that CSVDE outputs seems to be non-deterministic.

If I dump the file, go into the connection and do a "Reset Columns", everything works fine. However, I would like to do the dump as part of my Control Flow, and I can't find a way to force a "Reset Columns" before the processing begins.

I know what columns I'm getting, just not the order they'll come in. I also know that the first row will have the column names in it.