Hi,

Currently we get data from more then 200 different sources and all of
our vendors provide data in different file formats. The problem is we
have more then 100 DTS packages now and the maintainance is very
diffucult.
Every time our vendor changes the format we have to change in multiple
DTS packages.
Is anybody know what would be the right way of reducing the no. of DTS
packages.
The type of file formats we get are .xls .txt .dat .csv etc. and .txt
& .dat files comes with different delimitters. The # of columns also
varies from file to file. Is it possible to have a DTS package which
can handle diff file formats and loads data into a staging table and
from there based of the source of the file we can move data into
respective tables & columns.

We are using SQL SERVER 2000

Thanks in advance.

Subodh


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

And this is the 6 million $ question. The short answer is that if your feed
provider is inconsistent in what they send you then it will be very
difficult for you to build a one package fits all solution even between the
same type of file.

You could look to parse the file each time you get it and try mapping the
file dynamically. This is fine if you know the names of the attributes and
can map them consistently (would be best to have a mapping table exposing
the destination columns to source column mapping should you get them). One
problem here is what if they send you a new attribute on their side? Do you
create a new attribute in the table and map dynamically as well.

Sorry I cannot provide you with an easy solution

--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Subodh Goyal" <sgoyal (AT) agline (DOT) on.ca> wrote