cannot connect to database after putting tasks in

Question

I had a package that worked perfectly until i decided to put some of its tasks inside a sequence container (More on why I wanted to do that - How to make a SSIS transaction in my case?).

Now, i keep on getting an error -

[Execute SQL Task] Error: Failed to acquire connection "MyDatabase". Connection may not be configured correctly or you may not have the right permissions on this connection.

Why could this be happening and how do I fix it ?

Answer 1

I started writing my own examples to reply to your question. Then I remember that I met Matt Mason when I talked at a SQL Saturday in New Hampshire. He is the Microsoft Program Manager for SSIS.

While I spent 3 years between 2009 and 2011 writing nothing else but ETL code, I figured Matt had an article out there.

http://www.mattmasson.com/2011/12/design-pattern-avoiding-transactions/

Here is a high level summary of the approaches and the error you found.

[ERROR]

The error you found is related to MSDTC having issues. This must be configured and working correctly without any issues. Common issues are firewalls. Check out this post.

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/3a5c847e-9c7e-4628-b857-4e6edaa7936c/sql-task-transaction-required?forum=sqlintegrationservices

[SOLUTION 1] - Use transactions at the package, task or container level.

Some data providers do not support MSDTC. Some tasks do not support transactions. This may be slow in performance since you are adding a new layer to support two phase commits.

http://technet.microsoft.com/en-us/library/aa213066(v=sql.80).aspx

[SOLUTION 2] - Use the following tasks.

A - BEGIN TRAN (EXECUTE SQL)

B - YOUR DATA FLOW

C - TEST THE RETURN CODE

1 - GOOD = COMMIT (EXECUTE SQL)

2 - FAILURE = ROLLBACK (EXECUTE SQL)

You must have the RetainSameConnection property set to True on the connection.

This forces all calls thru one session or SPID. All transaction management is now on the server.

[SOLUTION 3] - Write all you code so that it is restartable. This does not mean you go out and use check points.

One solution is to always use UPSERTS. Insert new data. Update old data. Deletes are only a flag in a table. This pattern allows a failed job to be executed many times with the same final state being achieved.

Another solution is to handle all error rows by placing them into a hospital table for manual inspection, correction, and insertion.

Why not use a database snapshot (keeps track of just changed records)? Take a snapshot before the ETL job. If an error occurs, restore the database from the snapshot. Last step is to remove the snapshot from the system to clean up house.

In short, I hope this is enough ideas to help you out.

While the transaction option is nice, it does have some down falls. If you need an example, just ping me.

Sincerely

J

Answer 2

What package protection level are you using? Don't Save Sensitive? Encrypt Sensitive with User Key? I'd recommend changing it to use Encrypt Sensitive with Password and enter a password. The password won't disappear.

Have you tried testing the connection to the database in the connection manager?