sql server data warehouse: Problem in loading data from Excel -- sql server data warehouse

Problem in loading data from Excel

rahulcheeku NO[at]SPAM gmail.com
4/23/2007 2:45:04 PM

sql server data warehouse:

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?

Re: Problem in loading data from Excel

TheSQLGuru
4/24/2007 12:00:00 AM

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.

[quoted text, click to view]