Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS - Date conversion in SQL Server data source Expand / Collapse
Author
Message
Posted Tuesday, December 10, 2013 1:00 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 12:23 PM
Points: 545, Visits: 641
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.
Post #1521653
Posted Tuesday, December 10, 2013 10:23 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 3:40 PM
Points: 1,077, Visits: 1,496
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)

Post #1521736
Posted Wednesday, December 11, 2013 12:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:35 AM
Points: 12,206, Visits: 9,169
Does it work when you execute the SELECT statement directly in the OLE DB Source component?



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1521754
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse