The accounts are different.

The account (using which sql server/integration services are running) is different from the account which used to install the oracle client on the machine.

I am not getting this, please put light on this

Is the account that you are running the SSIS package from the same one that you installed/configured the Oracle client from?...

As both Execute SQL Tasks are pointing to different databases.

Those Execute SQL Tasks calls stored procedures (that populate the data into the corressponding tables).

Now what I want is lets...

No issue with the query.

As I mentioned this error doesn't comes very often. 10 in 160 (less than 10%) times this error comes.

Its done!!!! 🙂

What I did is after renaming the database, go to Job Activity Monitor and re-start the cdc capture job of the database. It will start capturing the changes.

Disabling and re-enabling the CDC will leads to data loss in CDC capture instance table.

I do not want to loss the data.

I also tried adding a Execute SQL Tasks with query "BEGIN DISTRIBUTED TRANSACTION" with RetainSamConnection for that connection manager is TRUE to support "Transaction across multiple databases" but that is...

Try this:

1. Create a variable which will store the connection string (in below code: strSourceConnection).

2. In script-task:

ConnectionManager SourceoledbConnectionManager = Dts.Connections["SourceConnection"];

...

Yes Paul. We need to process huge data. Thats why we want them to be run in parallel but within a transaction. Running them sequenctially will take a lots of...

Thanks Paul for suggesting the solution.

As the requirement is to run the sql tasks in parallel. So is there any way to run the tasks in parallel (within transaction)?

The package fails when two SQL tasks run simultaneously

Package => TransactionOption=Required

Execute SQL Task => TransactionOption=Supported

The transaction starts working by changing the TransactionOption to Required along with setting ValidateExternalMetadata to FALSE for all components in Data Flow.

However, I am getting the following error when I...

I have multiple Execute SQL Tasks (running on different databases) and Data Flows

Can I use BEGIN DISTRIBUTED TRAN inside "Execute SQL Task"?

DTC is enabled.