I'm sure I'll get yelled at by someone for this post...

Don't be too bound on the "Stored Procedures are the only way" approach.

They are not necessarily the fastest or best...

You should take some time to review and understand the replication monitoring tools you get with SQL Server. You can set latency and failure notifications with the built-in tools.

If it is just a single record at the beginning and another one at the end of the file, you could set up your control flow to have a data...

You should post this in the SQL 2000 portion of the forums.

Case-sensitive is just the beginning. When using lookup, merge join, merge, aggregate, sort and any other included component that does any sort of join or SORTING, the component is...

You data flow should be pretty simple:

1) Data source to get data from Excel workbook

2) OLEDB Command transformation

In the OLEDB Command transformation, you will have a SQL statement that looks...

Your error messages are indicating that the meta-data in your source component of your data flow does not match the data in the spreadsheet.

This could be because you have some...

How many rows are in the dimension tables?

Lookup components can be tricky when you get to a large lookup table. Their performance can degrade quickly when the number of...

You may have a problem other than simply too many lookup tasks.

I have seen packages move millions of rows through almost 30 lookup tasks and they perform fine.

You can probably...

The folders make no difference. If you can have them logging on individual drive arrays you can possibly improve performance and reduce the risk of all of the databases...

There are lots of variables. If you have one transaction every few hours and it is a small transaction, you will get very different results from large transactions happening...

Look at the RPC settings on your linked servers.

I am not sure why you would be trying to avoid using joins while working with a SQL Server, but I suppose you could do something rediculous like this:

SELECT A.CustomerID,...

DT_STR does not pad extra spaces.

SSIS is pretty sensitive to data types. You could trim the extra space off in the lookup source

SELECT RTRIM(MyKeyField), MyValue

I would also recommend upper-casing...

You said you defined the variable as an Int4 and the output of max(record) is of numeric type.

Either CAST your query output or use a (if I remember correctly) Double...