August 9, 2015 at 11:24 pm
Hi ,
I have 1 sequence container having 2 task.
1st one will delete which is a execute sql task and 2nd one is DFT which load data from source to destination
I have set transaction property Required on container level and supported on task level.
When I am executing the package I am getting below error.
[Execute SQL Task] Error: Failed to acquire connection "DB_Spectrum". Connection may not be configured correctly or you may not have the right permissions on this connection.
package without transaction is working fine.
Help to to resolve the above issue.
Regards,
Vipin
August 10, 2015 at 12:38 am
August 10, 2015 at 3:59 am
It is working properly when removing Transaction setting from container.
August 10, 2015 at 8:58 am
Are there multiple SQL Servers involved in the process? If there are you will need DTC running and configured correctly on all the servers.
If your data changes (inserts/updates/deletes) are all on one server I have done an explicit BEGIN TRANSACTION Execute SQL Task and a COMMIT TRANSACTION Execute SQL Task after all the work is done. Then I have put a ROLLBACK TRANSACTION Execute SQL Task in the OnError evetn handler in SSIS.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 10, 2015 at 10:30 am
I would check DTC as mentioned by Jack , also have a look here .
From here - https://msdn.microsoft.com/en-us/library/cc304421.aspx
When deciding whether and how to implement a transaction, follow these guidelines:
For transactions to be enabled in SSIS, you need to turn on the DTC service, and the tasks that you want to be part of the transaction must work with the DTC service natively.
If a series of tasks must be completed as a single unit, in which either all the tasks are successful and committed or an error occurs and none of the tasks are committed, then place the tasks within a Sequence Container, and then set the TransactionOption property of the container to Required.
A task can inherit the transaction setting of its parent when the TransactionOption property is set to Supported, which is the default setting when creating a task or container.
You can prevent a task from participating in a transaction by setting its TransactionOp-tion setting to NotSupported.
Transactions work at the control flow level and not within a data flow. This means that you can turn on a transaction for a data flow task, but you cannot turn it on separately for selected components within the data flow; either the entire data process will be successful or it will be rolled back.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply