How to reader header and footer in EBCDIC file?

I have data arriving in fixed-width EBCDIC format. Each file contains one or more groups of records. Before and after each group there is a header/footer, which is not in the same layout as the records that it describes. Header, record and footer each have a different layout to the other but are consistent within themselves.

Thankfully the one thing header, footer and record layout have in common is their length, so at the moment, using the appropriate code page in the Flat File Connection Manager, I'm able to read all the columns as strings. The headers and footers just come through, albeit a bit weird looking, and I can filter them out with a conditional splt.

However, the header contains information that needs to be appended to each record in the group. Does anyone have any suggestions about how to achieve this? I'm trying to avoid developing a custom data source for this task but, if there's no other way, has anyone done it and do they have any tips?

You should search this forum for discussions on topics similar to this.

You can read the file twice, using two data flows. The first time, to extract just the header and to store its values in a table, perhaps. The second time, to extract just the detail records and to join to the table from the first run to pull in the information you need.

Or, you can use a script task to read through the file. Or a script component inside the data flow. You may have to read in the entire line as one big string though.|||I would read the file twice as does not involved custome code. I knmow John(Jwelch) has some sample code on his blog so that may be another resource apart from searching the forum

How to read block of rows from database tables

have created a Database Application in Java and display all the records in tabular format of one Table. This table have Millions of Rows, If I run Select * from Table, then my Machine not responding, so Now I wants to add paging of 1000 rows at one time.

Is there are any option/query to read block of rows at one time and then query again for next page ?

i.e In MYSQL have LIMIT clause with Select Statement

Please let me know..

Database : SQL Server 2000/2005,

Thanks in Advance
Laxmilal

Whenever you use Select statement you must use WHERE condition to limit the rows.

Eg.

Select col1,col2.... From Tablename WHERE someid between 1 and 1000

Madhu

|||

You 'Machine not responding' is most likely due to waiting for millions of rows to come back from the server.

You really need to limit the quantity of data that you are requesting from SQL Server. Without WHERE clause criteria that enforces limits, you are unnecessarily wasting bandwidth (and time) as more data than you need for the operation at hand is being transported to the client application.

With SQL 2005, you may wish to explore using the new ROW_NUMBER() function to assist in retrieving specific 'blocks' of rows.

Referring to Books Online, Topic: ROW_NUMBER()

Example:

USE AdventureWorks; GO

WITH OrderedOrders AS ( SELECT SalesOrderID, OrderDate, ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber' FROM Sales.SalesOrderHeader ) SELECT * FROM OrderedOrders WHERE RowNumber BETWEEN 50 AND 60;(You should be able to adapt this concept for your needs.)

How to query the date whose value is in text format

Hello,

I get a problem in developing the company report. The column of "BATCH" in Oracle view is shared by all departments that need the different entries, so we have to use "Text" data type for this column in order meet all departments needs.

One department uses it as a sample date column. The text entry value is in the format of mm/dd/yyyy.

If I do not do the data converting, when I set the query: Where SampleDate between '01/01/2005' and '01/31/2005', the outcome not only include the January data of 2005 but also include the January data from all privious years, because system evaluates the data in text format rather than date format.

What I have done is to use Oracle To_Date(SampleDate,'mm/dd/yyyy') function to convert the text format to Oracle date format in report dataset.

I have two problems:

1) How to take out the wrong format enties? for example, user enters 1102/2005 instead of 11/02/2005. I find that the wrong formating entries cause the failure of data retrival. Micro T-SQL has a function of IsDate() to flag out the entries that are not in date formating, but it does not work here, I guess the reason may be our data source is Oracle.

2) Even the data is conveted, it still does not work propertly.

For example, I write:

Where To_Date(Batch,'mm/dd/yyyy') between '01/01/2005' and '01/31/2005'

it does not work

I write: Where To_Date(Batch,'mm/dd/yyyy') between To_date('01/01/2005','mm/dd/yyyy') and To_date('01/31/2005','mm/dd,yyyy')

it still does not work.

How to resolve these issues.

Thanks,

Zixing

For problem #1, if you are using Oracle 10g you can use a regular expression in your query so you only get valid matching dates. If you aren't using 10g, then you could use the like operator with some wildcards.

For problem #2, try running your query in an Oracle client first just to make sure that you have the query syntax right. I am not familiar with Oracle tools for performing queries but I am thinking something like MSSQL Query Analyzer.