Thanks for that.   We use synonyms to refer to other databases so hopefully that shouldn't cause too many issues.

I'll give it a go with a test database and...

Should be...

If you've got this all inside a VS database project, editing the .sql files for the tables and then Publishing does everything for you.

But behind the scenes, it is still...

One idea is to put your dataset queries into stored procs and call those from your report.

My preference is to keep complex business logic such as this within the database....

I had to look up "corpuscles", for a moment I thought it was a new drink at starbucks.

Thank you all for taking the time and giving me your take...

Jeff, that's nice code, well done!

I would define first week as any days of the month that land on the first week of the month.  i.e. if September 1 lands on Friday, then September...

• This reply was modified 2 years, 11 months ago by Phil Parkin.
• This reply was modified 2 years, 11 months ago by Phil Parkin.

If you want a load into a new table, then create the empty table AND create the Clustered Index.  Then, forget SSIS... use BULK INSERT with the TABLOCK...

I agree!...

SELECT STRING_AGG(CONCAT('''',td.name,''''),',')
FROM #tbl_db td

For finding out whether DBs are being used, there is also the (simple, but somewhat brutal) option of taking them offline and waiting for someone to complain.

why are you dropping and recreating the indexes? what are you trying to accomplish with it?

(for Phil - OP has DROP_EXISTING = ON so indexes are there already while...

Are you saying that this proc runs slower in SSIS than it would if you were running it from SSMS?

Have you determined which statement is the slow one (I imagine...

If that is the syntax that the app will use, and you cannot change it, won't it fail anyway, because of the 'CALL'?

Not sure I understand. Best (and possibly only) way in SQL is to use a series of INSERT queries. If that's not the answer you were looking for, please clarify.