December 10, 2013 at 1:00 pm
I'm executing a stored procedure that is doing a straight forward date conversion from an integer in YYYYMMDD format to a DATETIME object.
This works fine in SSMS. When I choose that stored procedure as my input source for a data flow, I get a data conversion error. "Conversion failed when converting date and/or time from character string.".
Here's what I don't understand. If I'm running a stored procedure, isn't the dataset returned ALREADY converted? In other words, why does SSIS think it needs to convert the data? (The HASHBYTES stuff is for some CDC stuff I'm doing but it isn't relevant to the problem I'm having.)
Note: The two date fields (OKRGDT and OKLMDT) are stored as numeric(8,0) in the source table.
SELECT [OKCONO] AS SrcCompanyNum
,[OKCUNO] AS SrcCustomerNum
,[OKCHID] AS SrcCreatedBy
,[OKCHID] AS SrcUpdBy
,CONVERT(DATETIME,CONVERT(CHAR(8),[OKRGDT])) AS SrcCreateDate
,CONVERT(DATETIME,CONVERT(CHAR(8),[OKLMDT])) as SrcLastModifiedDate
,HASHBYTES('MD5',
ISNULL(CONVERT(NVARCHAR(MAX), [OKCONO]),'')
+ ISNULL(CONVERT(NVARCHAR(MAX), [OKCUNO]),'')
) AS SrcHashKey
,HASHBYTES('MD5',
ISNULL(CONVERT(NVARCHAR(MAX), [OKCHID]),'')
+ ISNULL(CONVERT(NVARCHAR(MAX), [OKCHID]),'')
+ ISNULL(CONVERT(NVARCHAR(MAX), [OKRGDT]),'')
+ ISNULL(CONVERT(NVARCHAR(MAX), [OKLMDT]),'')
) AS SrcHashData
FROM StageCustomerMaster
"Beliefs" get in the way of learning.
December 10, 2013 at 10:23 pm
Sounds like bad data to me, eg. Single digit month
declare @OKRGDT numeric(8,0)
set @okrgdt = '2012510' --missing 0 in month
print CONVERT(DATETIME,CONVERT(CHAR(8),@OKRGDT))
Try checking the data and ensuring it is in the correct format with a query like:
SELECT [OKRGDT], [OKLMDT]
FROM StageCustomerMaster
WHERE (len([OKRGDT]) <8 OR len([OKLMDT]) < 8)
OR (isdate([OKRGDT]) <> 1 OR isdate([OKLMDT]) <> 1)
December 11, 2013 at 12:02 am
Does it work when you execute the SELECT statement directly in the OLE DB Source component?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply