That's the difficulty. I don't want to create a table first.

CREATE PROCEDURE MyProc

AS

SELECT 1 AS column1, 2 AS column2

--At this point my data-consumer could use code like this

CREATE TABLE #myDynamicTable...

Can you you arrange to copy the file to a location the test server can access? You could add this to the backup job or use a separate process.

You existing backup process should already be automated.

Assuming this is the case: determine where those backups are going; determine whether the test server has access to the backup files;...

>>(substantial platform differences) = (substantial performance differences)

That was exactly it...and not in a linear fashion either.

Thanks to you folks for educating me about REBAR in the first place. It gives...

I'll explore the test/prod server differences, the differences are substantial.

Duration is always 0 in profiler which may indicate SQL Server not being fed by the software at the normal production...

Took a look at job history, we use dtexec a lot. I'll have to sleuth this out the old fashioned way....

Perhaps you can give the users access to a table where they can set a flag to disable the job. Then add a step to the job that will check...

Consider making you packages dynamic using Package Configurations.

http://msdn.microsoft.com/en-us/library/cc895212.aspx

This could be important as far as disaster recovery where you server names, file locations and even database names may be...

SSIS package does not run when called from a SQL Server Agent job stephttp://support.microsoft.com/kb/918760

Its a fairly common issue and there are tons of resources if you google 'package fails SQL...

Truncate requires elevated permissions beyond GRANT DELETE. I think you need to be db_owner.

If the table is small you can use a DELETE without a where clause.

Or explore ways...

Applying permissions up the folder path to Folder1 seems to have done the trick. \\server\Folder1\Folder2\Test.txt

Thanks!

Adding the account to db_ssisoperator role seems to resolve the issue.

I am still troubleshooting some issues with overwriting an existing file..

Using dtexec will create the file but will fail if...

I think the account needs to be added the db_ssisoperator role in the MSDB database.

I'll post back final testing results....

Same result when I call the 32 or 64 bit executable.

I'll continue to research the error.....

Could not load package ".." because of error 0xC0014062.

Description: The LoadFromSQLServer method has encountered OLE...

Could not load package ".." because of error 0xC0014062.

Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80004005 (Login failed for user.....). The SQL statement that was issued...