Hi,
I am building a DTS package in Sql Server 2000. Data source is an
excel file and destination is Sql Server 2000. This package runs
monthly and loads the data into database. But evey month the number
of
columns in the source excel change. Now i am trying to get the number
of columns from the excel dynamically (Used range) and then load all
those columns in the database. But i am facing problem in
transforming
data dynamically from one excel column to one database column. Can
anybody please tell me if there is any way by which i can dynamically
make transfomations of a Transformation Task depending upon the
number
of columns present in the excel?

How about VBScript and ADO, which has providers for both excel and sql
server? Gonna be a bit complicated since you will have to evaluate the
excel spreadsheet, determine how many columns and their names, script a
create table statement from this, then import the data.

Another method is to build a staging table and import that uses WAY more
columns than you ever think you will have in excel. Then do the import,
with NULL values going into any column that doesn't have data in excel.

I am a bit confused as to how useful the data can be if the number of
COLUMNS changes each month. :-)

--
TheSQLGuru
President
Indicium Resources, Inc.

<rahulcheeku@gmail.com> wrote in message
news:1177364704.682071.287360@l77g2000hsb.googlegr oups.com...
> Hi,
> I am building a DTS package in Sql Server 2000. Data source is an
> excel file and destination is Sql Server 2000. This package runs
> monthly and loads the data into database. But evey month the number
> of
> columns in the source excel change. Now i am trying to get the number
> of columns from the excel dynamically (Used range) and then load all
> those columns in the database. But i am facing problem in
> transforming
> data dynamically from one excel column to one database column. Can
> anybody please tell me if there is any way by which i can dynamically
> make transfomations of a Transformation Task depending upon the
> number
> of columns present in the excel?
>