I am going to question your design a bit here - don't take it personally.

The first thing that I see as an issue is object ownership and permissions. If...

If you have a single connection in ADO and you start one transaction, when you begin a second transaction these will be "nested" transactions just as if you had opened...

The number of dimensions is less important than the number of attributes and how you have your attribute hierarchies configured, but 20 dimension is getting into the range of "a...

SSIS is designed to allow dynamic package creation. There are samples on the MSDN website. It involves writing an application (in a .net programming language) that uses the SSIS...

If you also had a table with a primary key of PageID, the composite key on the table with both would make a lot of sense and be necessary.

If for the 40 files you have different columns and they are going into tables with different schemas, you will really need 40 data flows. You could use some...

If you had another table with the PageID as an identity column and had a FK to this other table, the composite key on your table would make sense.

In your...

If the SQL Agent is running on Server 1, the SSIS package is being run there, not on Server 2. SSIS does not have built-in remote execution capabilities.

Since you...

Interestingly enough, SSIS is not a cluster-aware resource.

Here are some articles:

http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/07/06/16015.aspx

http://weblogs.sqlteam.com/geoffh/archive/2007/10/04/To-Cluster-or-Not-to-Cluster-That-SSIS-the-Question.aspx

MS gives some sample code (search google) for executing an SSIS package programatically. Wrap this in a web service and run the web service on the SSIS server. ...

To execute an SSIS package, you must be on an SSIS Server. So, unless you have done a full install of the SSIS Server service on your application server,...

Unfortunately, that is really a misuse of an identity column. The are designed to be used as meaningless primary keys so having gaps is unimportant. There is no...

A SORT component has the option to only return distinct values. Sort by and include just the one column you want. There is a checkbox for removing the...

You really need to collect more information about a project before trying to start a design.

Some things to think about are: the number of documents, the format you want to...

Service Broker is actually the tool that MS has given you to process actions in a queue. This sounds like what you should be using.

It is a bit confusing...