Multithreaded run of SSIS packages

Is it safe to run several SSIS packages in parallel in a multithreaded application? The package objects are not accessed from multiple threads: every thread is handling its own package (see below).

void task_inside_thread()
{
System::String ^s = "...";
Microsoft::SqlServer::Dts::Runtime::Package p;
p.LoadFromXML(s, nullptr);

// callback handler
MyEventsClass ^ eventsClass = gcnew MyEventsClass();

DTSExecResult result = p.Execute(nullptr, nullptr, eventsClass, nullptr, nullptr);
return;

}

Is there any relevant documentation with respect to this issue?

Thanks,

Bogdan

Yes, this should be safe, packages are independent of each other.|||

I worried more about the fact the package instances might use global objects for registration and for signalling their current status.

Thanks for your answer.

Bogdan

|||Yes, they sometime do. I have to re-phrase it - they behave as independent objects, taking care of locking global state when needed.

Multiple transactions not working in package

I have a package with two sequence containers, each containing two SQL tasks and a data flow task, executed in that order. I want to encapsulate the data flow task in a transaction but not the SQL tasks. I have the TransactionOption property set to 'required' on the data flow tasks and 'supported' on the SQL tasks and the sequence containers. When I run the package I get a distributed transaction error on the first SQL task of the second sequence container:

"[Execute SQL Task] Error: Executing the query "TRUNCATE TABLE DistTransTbl2" failed with the following error: "Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."

The only way I can get the package to succeed is to set the TransactionOption = 'required' on the sequence containers and 'supported' on all subordinate tasks. This is not what I want, however. Any ideas?

Thanks,

Eric

Eric,

Can you please send me your package?

So far I couldn’t repro the problem.

|||

Eric, have you tried using a DELETE FROM statement instead of a truncate statement. Truncate doesn't log, so it's very fast, but is probably the reason you're blocking on your second task that's attempting to insert into the table.

K

|||

Hi

I am getting the EXACT same problem, but I am not using any TRUNCATE statements or similar.

Actually, I find that the problem only seems to exist when I try to enlist a Data Flow task in a transaction.

If I have 2 sequence containers, each containing an Execute SQL Task, everything works fine. If I try to replace one of these with a Data Flow Task, then I get the same error.

Does anyone have a solution to this?

|||I could not reproduce the issues reported.

Multiple transactions not working in package

I have a package with two sequence containers, each containing two SQL tasks and a data flow task, executed in that order. I want to encapsulate the data flow task in a transaction but not the SQL tasks. I have the TransactionOption property set to 'required' on the data flow tasks and 'supported' on the SQL tasks and the sequence containers. When I run the package I get a distributed transaction error on the first SQL task of the second sequence container:

"[Execute SQL Task] Error: Executing the query "TRUNCATE TABLE DistTransTbl2" failed with the following error: "Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."

The only way I can get the package to succeed is to set the TransactionOption = 'required' on the sequence containers and 'supported' on all subordinate tasks. This is not what I want, however. Any ideas?

Thanks,

Eric

Eric,

Can you please send me your package?

So far I couldn’t repro the problem.

|||

Eric, have you tried using a DELETE FROM statement instead of a truncate statement. Truncate doesn't log, so it's very fast, but is probably the reason you're blocking on your second task that's attempting to insert into the table.

K

|||

Hi

I am getting the EXACT same problem, but I am not using any TRUNCATE statements or similar.

Actually, I find that the problem only seems to exist when I try to enlist a Data Flow task in a transaction.

If I have 2 sequence containers, each containing an Execute SQL Task, everything works fine. If I try to replace one of these with a Data Flow Task, then I get the same error.

Does anyone have a solution to this?

|||I could not reproduce the issues reported.