Stored Procedure Failing when run by SSIS

Question

I have an execute SQL step in SSIS that is failing inconsistently with the following error:

[Execute SQL Task] Error: Executing the query "sp name" failed with the following error: "The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

The stored procedure truncates a table and then inserts data into it with tablock. It isn't parameterized and does not return a record set.

• The stored procedure executes successfully in SSMS.
• I am running the SSIS in debug mode under my user in visual studio.
• I have sysadmin on this local desktop development box.
• Any one of the four tasks can fail.
• If the container is executed independent of the rest of the package, it works.
• The connection string is an ole db connection to a database on the same box.

The flow where this occurs looks like this, all four call different, but similar procedures which all load data from the same table into four different targets:

Other info:

• SQL Server 2016 SP1, Developer Edition
• Windows 8.1

Edit:

Stored Procedure is like this:

create or alter myprocedure as

truncate table mytable

insert into mytable with (tablock) 
(col1,col2,col3,col4,col5 ... col30)
select 
   try_cast(col1 as something) as col1
    ,try_cast(col2 as something) as col2
    ,try_cast(col3 as something) as col3
    ,col4
    ,col5
    ...
    ,col30
from
   myothertable

Edit: I have another process that is virtually identical - barring it targets different files for import. It works with not problems. The files are smaller 10Gb vs 15GB.

Edit: There are some stack dumps in the SQL Error log:

• BEGIN STACK DUMP:
• 03/04/17 17:39:43 spid 11668
• • Location: AllocPageRef.cpp:2436
• Expression: IAM_START_PGID (ext_id, ext_size) == startExtId
• SPID: 77
• Process ID: 7836
• Description: Looking for extent in wrong IAM page
• • Input Buffer 70 bytes -
• exec my_sp

Time to run some more DBCC commands.

Answer 1

Are you referencing "myothertable" in any of the other procedures? Have you checked how the default transaction handling of the package and the container are set? If you are using tablock and are inside a nested transaction as you would be if it was all defaulted, you may be immediately deadlocking.