Thinking more, I think colorizing based on login is likely not really what is useful. However, by role is interesting. It's usually the permission set we are about, so allowing...

Prompt does not colorize based on the login used for the instance. That's an interesting idea. I'll submit it.

No, I mean execute the query(ies) that the package runs from SSMS from the instance where the data will be stored.

My guess too, which is why I'd like to break something out to check.

If it hangs in SSIS, can you get it to hang in SSMS?

Thanks. Posted in the MVP list. Hopefully someone responds.

I wouldn't think column order matters. At least, it shouldn't. Not sure dynamic SQL is better than just the loop shown above. I might actually store the 30 cols in...

Separate physical drives is mostly what you want. You want separate IO paths. Depending on your architecture, it may or may not help. The other place that diff files helps...

It's by the instant/db, but the scope is per SSMS install. So I can color my dbs/instances separate from how you do it.

That's deprecated. I think you want master.sys.master_files

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-master-files-transact-sql?view=sql-server-ver15

Yes, the login isn't correct. That's what the error is.

The best way to speed up backups is to stripe them. The SQL engine can then spew out pages to multiple locations. If you have good hardware, I'd look at...

Does it pause at the same point each time?

I'd start to break apart the package and test things separately. Pick a certain item, then make a new package to do...

I like the idea of altering the temp file to include missing columns. It's a  simple solution that means the important part, the reads, don't change and you don't get...

Consummable data means posting the CREATE TABLE statements, with some INSERTs for sample data. Not just describing the columns.

+1 for Brian's thoughts. The indexing is driven by the query patterns.