since I apparently took too long posting my original answer and the page timed out you're goign to get a short sweet and to the point one.  I compact my...

How is performance on your current server?  Good, bad, ugly?  Given the 2 optiosn you list I'd suggest option 2, becasue it doesn't look like option one has any redundancy...

Or this may have some insights as well...

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=190160

Have you granted the "users" group any acess to specific databases, tables, veiws etc?  will they only need read, or will them need CRUD access? 

Is this what you're looking for???  Please excuse the formatting....

CREATE TABLE #myTable(

Key_m int IDENTITY NOT NULL,

GeographicCode varchar(10) NULL,

Year_census_estimate int NULL,

Sex int  NULL,

Age_start int NULL,

Age_end int NULL,

Person_count float(8) NULL,

Age_range varchar(21)...

Have you set the task that is failing to run on the Main Package Thread?  I have seen this error thrown before and the solution I'd found on the web...

Is this what you're looking for?

UPDATE #temp

Set col3 = 'whatever', col4 = 'whatever2', col5 = 'number'

WHERE col1 = 'A' and col2 = '101

What I do is write to the local machine and then in the next step of the SQL Agent Job I copy it out to my network location.  I've found...

Theoretically the SELECT INTO Sytax should create the new table for you and then populate with whatever records you need.  Since you already know what columns should be in your...

Are you usign the copy table method or an SQL Query in the Source tab of your Data Transformation step?  Would it make sense to store the start time and...

Alternantively if you really want to read through all of those signle quotes, you can still use your open query, but use a

Select * INTO #mytable FROM OPENQUERY(...

Why not just bring the data over with DTS.  It's much easier and you dont' have to worry about being in Single quote hell.  Just create a connection onject to...

Depending on what kind of clients you have you may need to do this either in Wins or DNS or most likely BOTH.  Why not setup an alias that points...

Are both servers at the same Service pack/hotfix level?  I've never seen this particular problem but have had issues in the past when transfering DTS jobs and that was the...

Take a look at this and see if it helps...

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=150&messageid=185005