Forcing Column Headings

Hi...

I have a report that does not use the detail line. It groups the information it needs and prints out in the lowest section heading (Product Type).

The problem arises when we have an employee ( next group up) who has worked on loads of product types in the date range selected.

The report prints perfectly except that when it skips to a new page we only get the page heading, not the section headings.

I have tried moving the section headings to the page headings - report "looks" fine, but does not work as it fails to recognise any changes in the groups.

Anyone any ideas how I can "force" it to print a heading when a certain number of lines have been printed??

Am new to Crystal so am sorry if this is a stupid posting...Will checking the 'repeat group header on each page' box do the trick for you?|||Thanks for the response.

Was not aware of that box, and finally tracked it down.

Unfortunately tried it and it did not change anything. Will carry on experimenting, but if you have any other ideas they will be gratefully received!!

Thanks again|||Update....

The more digging round I did, the more your suggestion seemed to be what I wanted, so I could not understand why it did not work.

So I went back and tried it again... and it now works!

No idea what I did before - can only assume I put it in the wrong section. I can only blame it being early and a lack of caffeine...

But it now works, as I said.

Many, many thanks for your help.sql

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.

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.