if you want to avoid a script task,i would think you could use foreach loop pointing to the directory, with a filter like "MyFile_*.txt"  for the pattern identify the records,...

id want to see the execution plan and know what indexes are on the table.

i don't know if the STGeoFromText is slowing things down, if i have the...

can't you just add another WHEN THEN?

case 
    when icd.ECCN is not null
  then icd.ECCN
  when sola.ECCN is not null 
  then sola.ECCN 
else 'missing' end 
AS TESTVALUEECCN

we need Details. the actual DDL of your current table, a couple of fake sample rows of data, and the DDL of your destination tables.
with that, we can provide...

performance wise, SQL server can insert millions of rows in about the same time as a single row or small number of rows,
so breaking up an operation into multiple...

I've seen  this on a web server due to connection pooling, but it's been a while.
way back when , we resolved it via two ways: we assigned a default...

my rule of thumb is for any table over say, 100K rows or so, data transfers like this should be done using the Import/Export Wizard, or via SSIS, so that...

did you resolve this? when i read it, the first thing i though was it might be database context.
if the database context was master, you'd get that exact error,...

thanks Eirikur, I've got several typical styles I've inherited via copy paste over time  that would be improved that way, I know.
I still have things that are selecting rownum from...

things like logins and user permissions i kind of prefer to do via TSQL instead of a black box tool that is scripting via SMO.

I like Perry Whittle's...

i created this simple parsename function that grabs items left to right. 
so if the item you want is always int eh seventh split, this works great:
/*

From the description, I'm thinking the issue is you have a slow report,and if multiple people hit it, it's slow, bogs down the system, etc.

I don't think there...

when you connect via SSMS, it is using the dns and wins services to resolve an name/alias to a specific IP address; therefor since the server already existed, it would...

this is what i use: 
with extremely rare exceptions, I want @@servername to return the name of the machine.
So if I clone VMOriginal as VMNew, when i connect to...

there's an additional table to join to, the msdb.dbo.sysmail_event_log , where the [description] column will tell you exactly what the issue is;
it's typically an error from the mail server, username...