Hi,

I need to import various excel files into sql server. All Excel files
have different column names. To my datapumptask I want to provide
column mapping dynamically using map tables. One map table will be
maintained for each excel file. map table will contain mapping between
excel column name and sql column name. Can this be easily implemented
using lookups or there are better ways of doing this??

Thanks,
Pradeep

Sure you do this but it will require a bit of coding.

You will need to change the DataSourceProperties of the Excel file
You will need to remove all exising transformations

You will need to speify a new source object
You will need to change the SourceColumns property and the DestinationObjects property
You will then need to recreae the transformations

Like I said a lot of code but it is workable

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com


"pm" <mittal.pradeep (AT) gmail (DOT) com> wrote

Allan,

Thanks a lot. I understood all the pointers you gave.

I have around 1000 excel files to upload. So I feel that maintaining
1000 map tables is a better idea than maintaining 1000 dts packages.
Please suggest if i am right in thinking so? From your reply i
understand that my mapping table should provide not only names but
other properties like nullable, precision etc of source and destination
column. Can you please provide some sample code or links where such
thing is implemented.

Thanks,
Pradeep