The best way to go about these situations is to first try the query without the variable. Then, if it works, substitute the variable in.

From what you've said, it sounds...

I'm guessing PowerPivot is something which is not in SSIS 2005?

Yeah - not to mention other things like, in some cases I need to have a single worksheet contain multiple result sets within it - which means that I have...

Never seen that error. Did you try using the ExcelFilePath property instead of the ConnectionString property? And also setting DelayValidation to true?

I could be wrong, but I *think* that SSIS has its own login which needs the access, since it is the one handling the moving of the files.

It won't. Unless you have a linked server that is.

SSIS is not very good at doing updates. You could do it using a SQL Command, but that would involve updates...

If they are different servers it gets a bit tricker yeah. Then SSIS might be the better option - should be able to work with what I've given you though.

Gonna have to agree with the others - just don't use the global temp table. Use a real table, and run a TRUNCATE TABLE at the start of your task...

Not too difficult to do in SSIS, though I don't see why you would - it's easier to write a SQL stored proc to do it.

Using SSIS, first put an...

What I'd recommend is you send *all* the records to one table first, then once they're in that table, you can run a stored proc from within SQL to send...

SSIS is pretty crummy at doing updates, because it is intended to be used in a pipelined fashion.

What I'd recommend you do is the following:

Step 1: Use an Execute SQL...

Another perhaps clunky solution:

Have two completely separate DTSX files. One will have your excel source with all columns, and the other has your excel source with the missing column.

Have your...

This might be something you've already tried, but if not, why don't you try this:

Have two data flow tasks. One uses the excel source that has all the columns, and...

Do the tables / etc change with the different connection strings, or is it just that the parent will be deciding which amongst a series of replicated servers is being...

Try this:

(DT_DBTIMESTAMP) (SUBSTRING([Column 5], 1, 4) + "-" + SUBSTRING([Column 5], 5, 2) + "-" + SUBSTRING([Column 5], 7, 2) + " " + SUBSTRING([Column 5], 9, 2) + ":"...