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 3 years, 9 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...

I did google before I posted and found the page too.

But that is not the answer. Microsoft said those two are deprecated, new development should use the new one. But...

Thank you, but when I tried to add a connection manager, in the provider list, I don't see there is a MSOLEDSQL. I do see one named microsoft OLEDB provider...

Thank you. We have the two situations you mentioned.

First if export then import , when doing import from the Ispac, will that automatically upgraded it to 2019 SSIS?

second, if deploy...

Another  place  I saw similar of this is in a sql script:

select cast(stu.VisibleID as int) as StudentID

,case when r.VisibleRouteID like '[0-9][0-9][0-9][0-9]-T%' then trex.OriginPointID

when r.VisibleRouteID like '[0-9][0-9][0-9][0-9]-I%' then trex.OriginPointID

when r.VisibleRouteID like...

Thank you, just saw that  too. I did unintall CU7, and apply CU6.  For now I think we are fine.

We will have to find another maitenance time for next CU8...

Thanks for the article.

Question, 1. how can you know the applilcation is using kerberos? In my environment, I always use domain account instead of local system account for the SQL...

This is more context of the script code:

Public Overrides Sub PreExecute()

'Sample valid value:

'Eff 20070905 Rev 00

'Pattern should match Route Number in VisibleRouteID string, allowing for a few minor formatting errors...