Stored procedures using Dynamic SQL Fail SSIS Pre-Execute stage in SQL Server 2008 - by c34miller

Status : 

  Not Reproducible<br /><br />
		The product team could not reproduce this item with the description and steps provided.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


0
1
Sign in
to vote
ID 406992 Comments
Status Closed Workarounds
Type Bug Repros 2
Opened 1/28/2009 1:58:11 PM
Access Restriction Public

Description

I have upgraded my SSIS packages from 2005 to 2008 (along with my SQL Server to 2008) and have run into a rather surprising "enhancement".  We use several stored procedures (accessed through SQL Command Data flow transforms) which generate and execute dynamic SQL.  It appears that in 2008, this dynamic SQL fails pre-execute where in 2005 it did not.

This stored procedure (using AdventureWorks for the example):

CREATE PROCEDURE [dbo].[usp_UpdateFieldWithCount]
	@EmployeeID int,
	@TableName VARCHAR(256),
	@FieldName VARCHAR(256)
AS
BEGIN
	DECLARE @SQL VARCHAR(MAX)

	SET NOCOUNT ON ;

	SET @SQL = 'UPDATE ' + @TableName
	SET @SQL = @SQL + ' Set ' + @FieldName + ' = ' + @FieldName + ' + 1 '
	SET @SQL = @SQL + ' WHERE EmployeeID = ' + convert(varchar(12),@EmployeeID)

	EXEC ( @SQL )
END

Executes from a SQL Command transform with SSIS and SQL Server 2005 without an issue:

[DTS.Pipeline] Information: Pre-Execute phase is beginning. 
Progress: Pre-Execute - 0 percent complete
Progress: Pre-Execute - 25 percent complete
Progress: Pre-Execute - 50 percent complete
Progress: Pre-Execute - 75 percent complete
Progress: Pre-Execute - 100 percent complete

However in 2008 this same transform fails, even when pre-validate is disabled:

[SSIS.Pipeline] Information: Pre-Execute phase is beginning.
Progress: Pre-Execute - 0 percent complete
Progress: Pre-Execute - 25 percent complete
Progress: Pre-Execute - 50 percent complete
[Dynamic SQL [16]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Incorrect syntax near the keyword 'Set'.".
[Dynamic SQL [16]] Error: Unable to retrieve destination column descriptions from the parameters of the SQL command.
[SSIS.Pipeline] Error: component "Dynamic SQL" (16) failed the pre-execute phase and returned error code 0xC0202080.
Progress: Pre-Execute - 75 percent complete


Interestingly, this problem can be solved by deprecating OLE DB connection manager to "Provider=SQLNCLI.1;".

Two questions
1) Is there another work around to this problem?
2) What risk are we taking by using "Provider=SQLNCLI.1;" with SQL Server 2008?

Thanks.
Sign in to post a comment.
Posted by Rakesh [MSFT] on 3/16/2011 at 5:21 PM
Based on the information on the item, we tried reproducing the issue on a package created on SQL Server 2005 and later upgrading and excuting on 2008. We were not able to reproduce the exact issue and the packages were executed successfully. If you feel this issue is being closed incorrectly please reply with any more steps which can help us reproduce the issue more accurately ie. the exact package that you used.

Thanks you again for reporting this issue.

-Rakesh, SSIS Team
Posted by Microsoft on 2/4/2009 at 1:17 PM
Thank you for your submission. We will be reviewing your information and will provide you feedback on its status. Thank you for your interest and suppport of SSIS.