I have a package that uses an OLE DB Command transformation that calls a stored procedure during a data flow to create an inferred date dimension member (when necessary). We have been running this package/stored procedure successfully for over a year now with SQL Server 2005. We just upgraded to SQL Server 2008, and we are in the middle of testing. However, this one specific package has a validation error thrown by the OLE DB Command task: Error at DFT Load BudgetExpense [Insert Inferred Date [14006]]: 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: "Conversion failed when converting date and/or time from character string.". Error at DFT Load BudgetExpense [Insert Inferred Date [14006]]: Unable to retrieve destination column descriptions from the parameters of the SQL command. Error at DFT Load BudgetExpense [SSIS.Pipeline]: "component "Insert Inferred Date" (14006)" failed validation and returned validation status "VS_ISBROKEN". Error at DFT Load BudgetExpense [SSIS.Pipeline]: One or more component failed validation. Error at DFT Load BudgetExpense: There were errors during task validation. However, there does not appear to be anything wrong with the stored procedure, as it parses and executes successfully when I call it manually with feasible values that would be sent from the transformation in the package. The syntax of the stored procedure has remained unchanged during the upgrade to SQL Server 2008. Here is the syntax of the stored procedure: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER procedure [ETL].[DimDate_CreateInferredMember] @surrogateKeyID int = null output , @businessKeyID int , @logID int , @logicalDate datetime = '1753-01-01 00:00:00.000' , @unknownCode varchar(2) = '*' , @unknownString varchar(20) = 'Unknown' , @unknownNumber int = -1 as set nocount on; -- declare error handling variables declare @errorMessage nvarchar(4000) , @errorSeverity int , @errorState int , @errorNumber int , @errorLine int , @procedureName nvarchar(126) , @rowcnt int -- declare local variables declare @quarterlySnapshotTypeID int -- initialize variables select @procedureName = object_name(@@procid) -- begin error handling block begin try -- if business key value passed in is null, then return error if @businessKeyID is null raiserror('Business key value passed in was null; a valid numeric value is required.', 16, 2); -- handle missing ETL ID if @logID is null set @logID = 0; -- if logical date is not provided, overwrite with beginning of time if @logicalDate is null set @logicalDate = '1753-01-01 00:00:00.000'; -- process new business key value into a date, and check validity of value passed in declare @dateString varchar(50) , @dateKey datetime set @dateString = convert(varchar, @businessKeyID) if len(@dateString) != 8 raiserror('Business key value [%d] is invalid; must be expressed in YYYYMMDD format.', 16, 2, @businessKeyID); set @dateKey = convert(datetime, left(@dateString, 4) + '-' + substring(@dateString, 5, 2) + '-' + right(@dateString, 2) + ' 00:00:00.000') -- if value being passed in already exists, return the identifier as surrogate (solves a race condition in ETL processing) if exists (select top 1 1 from Dim.Date (nolock) where DateID = @businessKeyID) begin set @surrogateKeyID = @businessKeyID; return; end -- populate inferred member insert into Dim.Date (DateID, DateKey, DayNumberOfWeek, EnglishDayNameOfWeek, DayNumberOfMonth, DayNumberOfYear, WeekNumberOfYear , EnglishMonthName, MonthNumberOfYear, CalendarYear, CalendarQuarter, CalendarSemester, AuditETLID) select @businessKeyID as DateID , @dateKey as DateKey , datepart(dw, @dateKey) as DayNumberOfWeek , datename(dw, @dateKey) as EnglishDayNameOfWeek , day(@dateKey) as DayNumberOfMonth , datepart(dy, @dateKey) as DayNumberOfYear , datepart(wk, @dateKey) as WeekNumberOfYear , datename(month, @dateKey) as EnglishMonthName , month(@dateKey) as MonthNumberOfYear , year(@dateKey) as CalendarYear , datepart(qq, @dateKey) as CalendarQuarter , case when month(@dateKey) <= 6 then 1 else 2 end as CalendarSemester , @logID as AuditETLID -- return the newly inserted surrogate key id set @surrogateKeyID = @businessKeyID; end try -- handle procedure errors begin catch -- capture error information select @errorNumber = error_number() , @errorMessage = error_message() , @errorSeverity = error_severity() , @errorState = error_state() , @errorLine = error_line() -- rollback open transactions while @@trancount > 0 rollback transaction -- distinguish between user thrown errors if @errorNumber = 16 and @errorState = 2 raiserror(@errorMessage, @errorSeverity, @errorState) else raiserror('Unexpected Error in [%s] - %s [%d] at Line %d', @errorSeverity, @errorState, @procedureName , @errorMessage, @errorNumber, @errorLine) end catch I searched and searched and found someone else having a somewhat similar issue, and he gave up and just reverted to using the SQLNCLI.1 provider as opposed to the SQLNCLI10.1 provider which we are now using, and his problem went away. I tried this, and the problem went away. The package validates and executes successfully when the connection string provider is set to SQLNCLI.1, but it fails validation for the SQLNCLI10.1 provider. Can someone please help? Thanks in advance, Ben

1753 - hmm why and It is hard to read the SP, but try to drop the milliseconds in @logicalDate datetime = '1753-01-01 00:00:00.000' See if it works then. PS: I just remember somebody had an issue with this format. I also suspect some SP/updates are missing on your new box. Arthur My Blog

I suspect the datetime formatting is different in the new instance of SQL Server. For example, some of the servers have the date format of dd-mm-yyyy while some others have mm-dd-yyyy. Therefore, the values 31-01-2011 is correct on some servers while it is incorrect in the other one. Run SELECT CURRENT_TIMESTAMP on the new instance of SQL Server to see what is the datetime format on it, and whether you are passing the values in correct format or not. Please mark as answer if this helps. Thank you. http://thebipalace.wordpress.com

Thanks to both of you for your responses. I did try removing the millisecond specification, and this did not help. I also checked the date format on both instances (the 2005 instance and the 2008 instance), and they are both yyyy-mm-dd. Furthermore, the package still executes successfully against the 2008 instance, but only when using the 2005 provider for the connection string (SQLNCLI.1). Any other suggestions? Ben

I suspect the datetime formatting is different in the new instance of SQL Server. For example, some of the servers have the date format of dd-mm-yyyy while some others have mm-dd-yyyy. Therefore, the values 31-01-2011 is correct on some servers while it is incorrect in the other one. Run SELECT CURRENT_TIMESTAMP on the new instance of SQL Server to see what is the datetime format on it, and whether you are passing the values in correct format or not. Please mark as answer if this helps. Thank you. http://thebipalace.wordpress.com

At a guess I would say that the string parsing may be causing problems Instead of set @dateKey = convert(datetime, left(@dateString, 4) + '-' + substring(@dateString, 5, 2) + '-' + right(@dateString, 2) + ' 00:00:00.000') How about SELECT CONVERT(DATETIME, CAST(@businessKeyID AS VARCHAR(8)), 120) Tested with DECLARE @BusinessKeyID INT = 20110209 SELECT CONVERT(DATETIME, CAST(@businessKeyID AS VARCHAR(8)), 120) Paul

Paul, I should have mentioned this previously, but yes, your assumption about which line is problematic is correct. It is the line that reads set @dateKey = convert(datetime, left(@dateString, 4) + '-' + substring(@dateString, 5, 2) + '-' + right(@dateString, 2) + ' 00:00:00.000') that is causing the problems. If I comment out this line of the stored procedure, the package validates just fine, but obviously the stored procedure no longer performs its intended function. I did try replacing that line with your suggested code snippet, Paul, but the same problem persisted. Any other ideas? Ben

Use this line of code: SET @datekey = CONVERT(nvarchar(50), @datestring, 121) Please mark as answer if this helps. Thank you. http://thebipalace.wordpress.com

That did not work either -- same results.

Use this line of code: SET @datekey = CONVERT(nvarchar(50), @datestring, 121) Please mark as answer if this helps. Thank you. http://thebipalace.wordpress.com

What is the value you load into @BusinessKeyID ? I can see in your code that you assign the value already loaded in @BusinessKeyId into @datestring: set @dateString = convert(varchar, @businessKeyID) I suspect the problem is from the value already in @businessKeyID.. Please mark as answer if this helps. Thank you. http://thebipalace.wordpress.com

The error occurs during the SSIS validation phase, before any values are inserted. It fails during design-time validation, and if I delay validation of the component, it still fails during pre-execute phase. The stored procedure is never actually called during the execute phase of the data flow task.

What is the value you load into @BusinessKeyID ? I can see in your code that you assign the value already loaded in @BusinessKeyId into @datestring: set @dateString = convert(varchar, @businessKeyID) I suspect the problem is from the value already in @businessKeyID.. Please mark as answer if this helps. Thank you. http://thebipalace.wordpress.com

I have submitted this as a bug to MS Connect (https://connect.microsoft.com/SQLServer/feedback/details/642422/problem-with-ole-db-command-transformation-and-sqlncli10-1-provider).

SomeOtherDeveloper, Have you found any workarounds to this issue? I'm stuck with simple set @date = convert(datetime, @str, 112) in my stored procedure.