Locking?

You might find performance improvements if you use this method instead.

I have not tried this before, but have you experimented with Shared Datasets?

If you were to create one which included the calculated 'time difference' column, you could then create another...

OK. The Previous() function should get you the value of a data item from the previous row, which you can then use to calculate the time difference. I'm not...

Unless you are going to justify this...

Do you have to do this in SSRS, or can you do it within the source query?

Put *.* in the filespec of the Foreach loop and it will pick up whatever file is in the folder, regardless of name.

Thanks Phil. As suggested by you i could see buffer setting has already changed 50% more from default, fast load option is already enabled, table lock to destination table...

I can't see how data in the Subaccounts table is linked to AccountCategories.

How us SUB1 linked to Description1, SUB2 to Description2, etc?

Database security is one of those things which in my experience is difficult to handle within SSDT.

It can be handled in environment-aware post-deployment scripts, or completely outside SSDT. Outside SSDT...

Is it going to be impossible to discuss without sample data? If so, let me know and I will create a spreadsheet. If you have a test data generator...

Option 2 is probably the way to go. Cross database dependencies are very difficult to deal with in SSDT. I always found I had to do a certain amount...

As it's a full table scan, there's not so much you can do.

You could read this & then experiment with the DefaultBuffer settings.

Definitely use Fast Load and destination table...

Are you sure that the user executing the script has rights to update MSDB?

As this is a large script, it's usually considered good practice to put it into a stored...

Rather than continuing down the path you're on, I suggest getting SSIS running as a domain service account user. Then you can assign the required privs to the service account...