Wow, that is so much faster. Thank you Steve. Now let me take a look to understand the code, is it the fnTally make it so much faster?

I will give it a try, so the fnTally is the one I can copy here? thanks

https://www.sqlservercentral.com/scripts/create-a-tally-function-fntally

Thanks, what is the benefit to use a calendar table, all we need is to know the first day and end day, If we create a calendar table,  that has...

try to attach file again

I attache the schema of sample data scrambled for testing

why there are some many spam from this site recently that sent to my email?

The process failed intermittenly. For example on Monday It runs OK, on Tuesday it fails, on Thurday it runs OK again. Everytime it shows connection failure. Also at the same...

Thanks GaryV, that step is a step that calls a web service to process each route of each student. and in the web service I know it is truncate tables...

The rsExecRole  needs to be granted to the user who run the subscription

Never mind. Figured out. I found I cannot delete my own post

• This reply was modified 4 years, 7 months ago by sqlfriend.

Thanks,

I found out the errors is actually cannot find sqlcmd, please see my top original post.

SQLcmd is in path, but the sql service account which is a domain account got...

Thanks,

But the new server uses the same service account as the old server. and the  service account does have access to the network drive.

And the same account runs the job...

another try I got MSSQLOLEDB.1  instead of SQLOLEDB.1 in the conneciton string.

THanks all

Thanks, I tried again, this time I did get MSOLEDBSQL.1; in the connection string.

I wonder how I got SQLOLEDB.1 in early operation, I remember I chose the same one.

Thanks all

Thanks. Yes it works. But if I look at the connection string: it is like I posted earlier:

Data Source=myserver;Initial Catalog=Truancy;Provider=SQLOLEDB.1;Integrated Security=SSPI;Application Name=SSIS-2019SSIS-{9CDABED8-B895-4E1F-86F9-1BE15149E31F}mserver.mydb;Auto Translate=False;

It is SQLOLEDB not MSOLEDB.

And microsoft online doc...