SSIS - Transactions
-
Hi,
I am fairly new to SSIS and am currently struggling to make a package work as a Transaction.
I have 3 text files I need to import and these are each imported to a Tmp1 file and then transferred to a Tmp2 file for manipulation after which they can be inserted into a Production table. At present my processs is split into 3 similar steps (each of 3 steps).
Firstly the Tmp1 and Tmp2 files for the first import are truncated (SQL Task) then I run a Flow Task to import the data into Tmp1. Then I run a second SQL Task to import to Tmp2 and manipulate the data. The steps are then repeated for import 2 and import 3. Each Import process has its own Tmp tables. I have use 2 Tmp tables to get around issues with DataTypes. At present I do not have code to import to the production table, but this should be straightforward.
The process runs fine until I make the TransactionOption "Required" at which point it seems to hang as soon as the first Flow Task is accessed. So I am thinking that it is something to do with Locks on the Tmp tables, caused by the initial Truncate command (which succeeds). I have tried including the processes in a Sequence Container, but still get the same issues.
-
Not sure what happened there, but just wanted to say thanks for any suggestions that people may have.
Cheers
Colin
-
Colin Betteley (1/26/2010)
Not sure what happened there, but just wanted to say thanks for any suggestions that people may have.Cheers
Colin
What's happen with this issue? Did you make it work?
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
-
Hi,
-
The issue is still outstanding.
Posts seem to go before I finish writing them! Sorry about that.
Any help in this area would be gratefully received.
Thanks
Colin
-
Here is a screenshot. You can see that the first SQL (Truncate) works, but it then hangs on the first data Flow task.
Colin
-
Dou you really need to implement this as one transaction?
This is how I would do it (without knowing your exact requirements :-):
create 3 sequence containers and put the process for each file (PD, PS and ST) in a separate container.
transactionoption on the package level = supported
transactionoption on containers = required (this will create separate transactions for each process)
transactionoption on truncate = not supported
transactionoption on data flow task = supported
Works as expected on my machine.
-
Thanks. Issue could be that truncate command does not go through the log file and therefore cannot be part of a transaction.
There are 3 more steps to include until I get to the final requirement, but will take on board your suggestion and redsign/rewrite the package. This may not be for a couple of days, but I will post back the outcome of the changes.
Many thanks for your advice.
Colin
-
You're welcome.
I also found this. Tried it on my machine and it works.
You don't have to change ValidateExternalMetadata to False on ALL source and destination components as the article recommends - just changing it on the components that use the table you are truncating is enough.
Seems like a bug in the Data Flow Task if you ask me. I tried replacing the Data Flow Task with an Execute SQL Task (that does exactly the same) and then there is no problem.
-
I have moved the truncate command to outside the Sequence Box and it now seems to be working as required.
Great to chat and thanks for your time.
Colin
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply