I will chime in with support for do not use job failures to indicate a procedure or data problem. A job failure should be investigated by a DBA to...

Same way you did the tables. In SSMS under the table you will see a folder 'Indexes' expand that then right click on the index and script it out.

is there an Maintenance plan set up?

It was at a SQL saturday event:

/http://www.sqlsaturday.com/viewsession.aspx?sat=92&sessionid=5774/

I am unable to access his website here at work but I believe there are slides and demos available.

If my memory is working I believe if you use dtexecui.exe you can choose all the options and the tool will provide the correct command line syntax to cut and...

Rather than using an optimizer hint why not just change the table variable to a temp table and let the optimizer do its thing.

I suspected it was due to load on the main database that is why I suggested pulling from a database snapshot instead of directly from the databases.

another way...

In my opinion the best tool for extracting data in SQL server is SSIS, lots of flexibilty and good performance and has a lot of tools to help loading the...

Whew, I was on the edge of my seat while reading this. Wasn't sure if we were ever going to get here. 😀

Use the SQL server configuration manager to specify the port SQL is listening on.

Yes you can use SSIS to do this. Either use the dataflow to move the whole table then run T-SQL to merge the data into the destination table or...

The main benefit with Merge is the single transaction for both update and insert. Helps guarantee that no change can slip in between the insert and update statements.

ideally, you would create a database role then grant to that role the EXECUTE permission on the schema(s) that contain the stored procedures. Then add that role to any...

Usually, the application takes care of it's own security and then connects to the database with a service account that has the necesary access.

Is the entire dataflow configured inside a Sequence container?