4

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:

enter image description here

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.

Bill
  • 176
  • 2
  • 12
  • Can you show us the code in the stored proc? Also, are the four tasks in the data flow loading into the same table? – digital.aaron Mar 03 '17 at 16:48
  • All 4 Load tasks use the same sproc? If yes what is the content of the sproc? – Kannan Kandasamy Mar 03 '17 at 16:48
  • aaron/kannan - Edited question to answer your comments. SP is rather simple but on a large dataset. The four tasks target different procs. – Bill Mar 03 '17 at 16:58
  • can you run the profiler and see it on sql profiler – goofyui Mar 03 '17 at 17:40
  • goofyui what are you looking for from profiler? – Bill Mar 03 '17 at 17:49
  • Do the four procs target any common tables? Either as the insert target or source? – Tab Alleman Mar 03 '17 at 19:04
  • The four procs load from the same table but load into different tables. My storage subsytem may not be able to keep up. I'll runs some diags. – Bill Mar 03 '17 at 20:33
  • There is usually a more useful error message in the SSIS log if you go digging for it – Nick.Mc Mar 05 '17 at 12:21
  • How long does the stored procedure take to run on SSMS? Does that time exceed the SSIS timeout (note: default is 0 = infinite)? I see you are not using precedent constraints, so all four will run simultaneously. Can you run all four procs simultaneously on the server without fail? – J Weezy Jun 12 '18 at 00:46

1 Answers1

0

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.

Chris Tucker
  • 383
  • 2
  • 10