SSIS Truncate with in a transaction

0
votes

I have to execute a truncate table command with in a transaction but my execution freezes if I do so.

In a sequence container, I have enabled the Transaction as Required and have placed a Execute SQL Task and Data Flow Task. In the Execute SQL task I have set the Truncate Table Command.

enter image description here

When I run the package it just freezes after the Truncate command.

enter image description here

Please let me know if you require additional details. Thanks in advance.

1
ssis
Look at your Output window, is there any information displayed there? Errors/Warnings/Information messages? If you connect to the server where you are truncating the table and then loading, what does it display if you run sp_whoisactive/sp_who2? Does it show your process as blocked by a transaction? - billinkc
@billinkc, I separated the above piece as package and now it throws error after few seconds. "Error: 0xC0208457 at Data Flow Task, Insert into DB 1 [90]: Failed to get properties of external columns. The table name you entered may not exist, or you do not have SELECT permission on the table object and an alternative attempt to get column properties through connection has failed. - Swamy
@billinkc, If I change the Transaction Option as Supported/Not Supported it runs as expected. - Swamy
In the properties of your Data Flow task, if you change the value of ValidateExternalMetadata to False, does it resolve your issue? - billinkc
@billinkc, Yep that solved the problem, thanks. The ValidateExternalMetadata property description says the validation will be delayed until run time. so I hope there is no issue if I set this to false. Thanks again. - Swamy

1 Answers

6
votes

There's a known issue (KB2253391) where truncate can block other processes. The workaround is to specify the ValidateExternalMetadata to false.

This effect of doing this is that if you change your data types or column names, SSIS won't identify it until the things go south during execution. For stable packages and environments, this usually isn't a concern (in my experience).

Feel free to give Kin some love over on DBA stackexchange for having done the leg work SSIS package blocks itself if uses TRUNCATE