SSIS Failing pre-execution when RetainSameConnection is set to TRUE

SSIS Failing pre-execution when RetainSameConnection is set to TRUE by Goodie

Closed

as Won't Fix Help for as Won't Fix

Type:	Bug
ID:	682902
Opened:	8/3/2011 7:26:24 PM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

When setting the RetainSameConnection property to TRUE, packages may fail during the pre-execution phase because of the order of statements executed at run-time.

I'm not sure why this happens but it's probably an issue of having to use the same connection throughout during the pre-execution phase. It's quite bothersome because as such, I can't every modify my package without modifying the OLE Source by doing a cut/paste to have BIDS put it at the end of the XML code of the DTSX and prevent the error from happening.

I'm suspecting a few other hanging issues during validation phases (with Execute SQL Tasks before Data Flow Tasks that play with the same tables) could be related to this.

Details (expand)

English

SQL Server 2008 SP1

Integration Services (DTS)

Windows Server 2008 Enterprise

English

I have encountered an issue with SSIS packages in SSIS 2008 (running SP1). The flow is as follows: I have a simple table with a few rows in it and a stored proc:

CREATE TABLE test(ID int, descr varchar(50);
insert into dbo.test values (1,'1');
insert into dbo.test values (2,'2');
insert into dbo.test values (3,'3');
GO

CREATE PROCEDURE dbo.test @aID int
AS
RETURN 0;
GO

Package has 1 OLE DB Con. Manager with RetainSameConnection set to TRUE.

I want to use explicit SQL transactions and NOT the DTC (it's all on the same server anyway).

DTSX contents (in order):

1- Execute SQL Task: SELECT * FROM <table>
2- Execute SQL Task: BEGIN TRANSACTION
3- Sequence Conainer:
3a- Execute SQL Task: SELECT * FROM <table>
3b- Data Flow Task
3c- Execute SQL Task: COMMIT TRANSACTION

- In Data Flow Task (3b):
- Ole Source: SELECT * FROM <table>
- Ole DB Command: call dbo.test @aID = ? (parameter mapping ? is set to the ID value from ole source)

When this is executed, an error is thrown. Depending on the version of the OLEDB provider, it changes. In 2005, it was about the connection being already busy with a recordset. In 2008, it's more generic (permission, syntax etc. error)

Now, if I change RetainSameConnection to TRUE, it works. But I don't want that. Clearly there's something bugging the package during the pre-execution phase.

Digging in the dtsx code, if you take the <component> for the OLE DB Command and put it *before* the <component> for the OLE Source, the package will succeed. Same if you had created your OLE DB Command visually before the OLE Source in BIDS.

No matter what flags for delayed validation, no external metadata validation etc. I have tried this issue still occurs unless I go and force the OLE Source to be after the other components.

Failure during pre-execution phase. Messages vary based on version of OLE Provider.

No errors

X64

0 attachments

Hi Goodie,

Thanks for the excellent problem report – the amount of thought and detail that you put into your report was incredibly valuable to the team as we triaged and worked to reproduce the behavior you describe.

We are going to close this issue as “Won’t fix” for SQL Server 2008. We have confirmed that the problem exists in SQL Server 2008 SP1, but the scope and impact of the problem doesn’t meet our criteria for a fix at this point. As you have pointed out there is a workaround for the problem; hopefully this will minimize the impact that this behavior has on your development.

We have also confirmed that the problem has been resolved in the next pre-release version of SQL Server “Denali”. If you are planning to upgrade to SQL Server “Denali” you will not be affected by this issue.

Matthew