Hello,

I am new to DTS. I currently use msft access to read several large text
files with no delimeters. I use the schema.ini file and link the files as a
table. Then I read them via vbascript into tables with the columns that I
need, while having indexes on them for the speed of my vba application.

I am looking to migrate this same method to sql server. I would like dts to
read my schema.ini file and I will then migrate via a transact-sql code.

Is this possible... if so, where can I read about this or can anybody help
me get started.... I am a good dba and programmer, just not familiar with DTS
and all it's flavors...

Thanks,
Charlie Cooperman
charlie (AT) visitcharlie (DOT) com

Hello Charlie,

Ok So I use Access very infrequently but it is my understanding that the
Schema.ini simply defines the text file. Underneath the covers of a DTS
Package that is exactly what happens also, slightly different format though.

Do you need to read the file each time? That would imply the definition
is never truly known and you need to read the file to get the metadata.

If this is true and you use the file in a DataPump task then you will perhaps
need to rebuild the datapump task each time also.

If you define a text file as you want using the Flat File manager in DTS
then save the package to VB .bas then you can see how the text file is defined
from within the package.

There is no ability to use a text file and a schema.ini file and have that
translte naturally into a table definition


Can you tell us why in particular you need to read the schema file?

Allan

Hello Allan,

Thanks for helping... The files are downloaded from the mainframe each day
and yes I refresh the access database... so you see I want to do the same in
SQL Server and with a scheduled event. I don't want to do with access
because the server would have to have access and I think it would be messy if
something went wrong...on the server...

A sample schema.ini looks like...
[fileA.txt]
ColNameHeader=False
Format=FixedLength
MaxScanRows=0
CharacterSet=OEM
Col1="Division" Char Width 2
Col2="PartNumber" Char Width 16
Col3="Available_Code" Char Width 1
Col4="WMS" Char Width 1
Col5="BOG" Char Width 3
Col6="Available_NegSign" Char Width 1
etc...

[fileB.txt]
ColNameHeader=False
Format=FixedLength
MaxScanRows=0
CharacterSet=OEM
Col1="Division" Char Width 2
etc...


I use the schema file because there are no delimeters and I need to know
when where the columns start and end... also if they make changes, mostly add
columns, I just modify the schema.ini file and refresh the link and all is
done... That is the beauty behind the schema. Also, when migrating to other
computers or server, it is just that easy...


What is a data pump and how do I get started? At my client, I do not have
VB and may not get... I have to use the tools that they provide...

Thanks,
Charlie



"Allan Mitchell" wrote:

Hello Charlie,

Thanks for the extra info

Right so the way it would have to work is this

You get 1 * Schema file and one * Data File

In your package you have an Active Script task and a DataPump (Transform
data task)

The Active Script task reads the file and rebuilds the Data Pump task.

This is not quite as trivial as it sounds but I can help.

The issue is though

Do you rebuild the destination also based on what you find in the schema
file? This is not an issue as you are in control.

I have a very basic package which takes a SQL Statement from an ini file,
creates a destination table from scratch based on the query in the file in
an Excel Spreadsheet, prepares the whole transformations part of the Data
Pump task.

Ping me if you want it

In 2005 we could do a whole lot more with this.

Allan

Hello Allan,

Again thank you for your enthusiasm.

Just to clarify:
there is only one schema.ini file. in it resides multiple txt file layouts.
I link these files via access and I call it [tablea txt]. My script reads
[tablea txt] and creates tablea. Yes, I use some columns and I also change
the text to decimal and stuff like that.

Is there something in msft or a book that describe this data pump, so I can
understand your technique?

I am really quiet technical... I can handle it... Again, just unfamiliar and
thought this community can point me in the right direction faster than me
looking for it...

If you want, send me a sample,
Charlie (AT) visitcharlie (DOT) com

Thanks,
Charlie



"Allan Mitchell" wrote:

Hello Charlie,

I do not know of anywhere that would detail this technique bur our site is
generally found to be useful (www.SQLDTS.com)

I am sending you a package which is along the lines of what you may need


Allan