"I'm going with the assumption that it's impossible to import multiple different Excel files":angry:

Well if you mean with SSIS you might be able to do it but I will not...

If you want to do more than just export some unformated data to Excel

you will have a steep learning curve using SSIS. You have to learn VB script.

You have what...

Chris

"Remember SSIS is just a tool. Sometimes its better to get the data into SQL and then massage it."

I completely agree. People try to use SSIS for everything. In order...

Kevinray64

Can you tell us a litle bit more about what you want do do.

I usually use VBA (you can access a remote SQL-server with OLEDB

which is very usefull if...

Thank you Adam.

At first it is almost necessary to have control of the the whole "ETL" process. Missing data or

bad quality is major problem in many cases. You can seldom...

Sperry

You say : "This is an automated process and i HAVE to use excel."

I then suppose you have Excel installed at server computer.

I always avoid to use SSIS to handle...

"This approach seems to be one that could be useful in many BI type projects, and to a large extent, seems to be a good use of Powerpivot as a...

"our requirement is to use SSIS only"

Can you give me a reason for this requirement?

I do work with SSIS and Office automation , vb.net, VBA etc.

The case you describe...

Do you have to use SSIS?

Do you need to save the data in tables in a database?

Why not use VBA in Excel to accomplish what you want?:-)

It does not look...

The main advantage with PP is that you don't need to create Olap cubes. But that doesn't remove the

need to create a data warehouse. The ability to import data from...

"If I recall correctly someone (might hav been Chris Webb) said in a PowerPivot-demo that it was important that your keys are a single, numeric column."

My comment is if that...

I assume that you want to create a calculated column in a PP table.

I tested that procedure in the beta version of PP with problems. It might work now however.

I...

Another alternative is to look at EXCEL 2010 PowerPivot.

I think PowerPivot is great except that there is no API for PowerPivot.

You must use SharePoint to automatically refresh the content.

After some...

Is there a better way to do this?

YES I think so. If you are to do extensive work in Excel like running macros etc

use VBA ADO and remote DB...

masheh

You don't tell us anything about the source. I have one case where the source

delivers a bunch of text files to be loaded inte the DW. One file contains invoicelines

many...