August 22, 2012 at 1:58 pm
Dear all,
I have a simple SSIS package, which contains only one "Execute SQL Tasks" task. Inside that task,there are several querys. I am using a "native OLE DB\SQL Server Native Client 10.0" data connection to connect to a remote SQL server 2008. The SSIS package is running on Visual Studio in my desktop (eventually will be deployed on that DB server). Both my desktop and DB server are in same domain and I am using windows logon to connect to DB server. I pretty sure domain account I am using has administrator role in both my desktop and remote database server. The package runs well if I set "TransactionOption" to "supported".
Now I want to add some transaction control to this task so if one query goes wrong, the whole task could be rolled back. So I put those queries into several "Execute SQL Tasks" and put them in a "secquence container". Both of "Sequence Container" and Task are set to TransactionOptions=required.
Then I got error when I try to execute this container:
[Execute SQL Task] Error: Failed to acquire connection "databaseserver.businessTable". Connection may not be configured correctly or you may not have the right permissions on this connection.
"databaseserver.businessTable" is the database connection to remote database.
I tried to import this package to the visual studio installed on same database server and it works pretty well. Seems I can't add transaction controll to the packages if run that package outside database server.
I tried to set database server's DTC perporty to "allow network access" from comonent configuration, but have no luck....
I got exact same issue when I try to connection to a SQL Server 2005 as well. These issues has been bugging me for a while....Any suggestion are highly apprecatied. Thanks in advance!
August 23, 2012 at 2:37 am
When you use transactions in SSIS, MSDTC is used (Distributed Transaction Coordinator). Maybe you don't have permissions on that?
Or maybe the SQL Server instance doesn't allow distributed transactions?
An alternative is to build the transaction yourself in the TSQL code, using BEGIN TRAN and COMMIT/ROLLBACK.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 23, 2012 at 9:06 am
Koen Verbeeck (8/23/2012)
When you use transactions in SSIS, MSDTC is used (Distributed Transaction Coordinator). Maybe you don't have permissions on that?Or maybe the SQL Server instance doesn't allow distributed transactions?
An alternative is to build the transaction yourself in the TSQL code, using BEGIN TRAN and COMMIT/ROLLBACK.
.
Thanks a lot for the reply....BEGIN TRAN is what i am using now....I confirmed MSDTC is running in both client and server, but how can I check if I have permission on server's MSDTC? Thanks a lot.
August 23, 2012 at 11:24 pm
Troubleshooting MSDTC Permission Issues When a Distributed Transaction Starts
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 24, 2012 at 9:45 am
Thanks dude...I will read this
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply