OLE DB Source Conversion Error

[FlaBusInt]

I am using a query in my OLE DB Source to pull a date and convert it into an integer smart date key in the format yyyymmdd. The code I am using as part of my query is

case
		when svc.InstallDate is Null then Null
		else cast(convert(char(8), svc.InstallDate, 112) as int)
		end

If I run the query in the Management Studio query window, it works fine. However, when I try to execute the package I get a fatal error: "Conversion failed because the data value overflowed the specified type".

Anyone have any ideas why it would work in MS but not as part of an OLE DB source?

 

[mrdenny]

It probably doesn't like the null. Try putting

case
        when svc.InstallDate is Null then ''
        else cast(convert(char(8), svc.InstallDate, 112) as int)
        end

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[FlaBusInt]

Thanks, Mr. Denny. I tried your suggestion and it still fails, but now with an error of "Invalid character value for cast specification."

The source data type is smalldatetime. Other than a Null, I don't know what could be causing it to barf like this.

 

[FlaBusInt]

Never mind. I turn into an idiot on Friday afternoons.

The error wasn't with the SELECT statement. The destination table didn't have an int data type.

I wish the SSIS error messages were a little easier to decipher....