CSV Not Displaying Correct Format From SSIS Export

Question

I am calling a stored procedure from a data flow task in SSIS in which I am selecting the HOUR datepart of a datetime field. (code below from the stored procedure)

SELECT
DATEPART (HOUR, R.received_date) AS [Hour] ,
CONVERT (VARCHAR(10), R.received_date, 101) AS [Date] ,
COUNT (R.id) AS [NumberofFilings]

And in my data flow task, I have a OLE DB Source task in which I call the stored procedure:

And when I preview the data with the OLE DB source task, the data looks like I would expect - with the hour column displaying an integer between 0 & 24:

The issue occurs after I export the results to a CSV file and the hour becomes a datetime field where the values become '1/11/1900 0:00' which is not what I'm expecting.

In my flat file destination connection manager, I set the Hour properties to be four-byte signed integer but the hour will not display as an integer but as a datetime.

I've tried other datatypes for the Hour column but nothing will convert this to a single integer / character. Any suggetions?

How are you looking at the csv file to determine that the Hour field is a datetime? It wouldn't be in Excel, by any chance, would it? — Tab Alleman
Yes, it would be. But if I open it in EmEditor, I'm still seeing the datetime field instead of just the Hour. — MISNole
What if you open it in Notepad? And without first opening it in Excel, which might save some Excel-driven changes. — Tab Alleman
Looks like "1900-01-11 00:00:00" - so it is no different in Notepad or Excel. — MISNole
Just as an experiment, can you try changing the column name to "Bob" or something that has no connotation of being a date/time. — Tab Alleman

Tab Alleman Tab Alleman · Accepted Answer · 2016-04-08T19:23:11

If you are opening the .csv file in Excel, I suspect that Excel is looking at a column named "Hour" and thinking, "Must be a datetime field. I'll just help my user out and make it so."

Try opening the .csv file in notepad and see what the actual contents look like.

EDIT:

I am unable to reproduce your results. When I follow your steps I get a CSV file that looks like this in notepad:

"Col1","Col2"
"0","04/05/2016"
"0","04/02/2016"
"0","04/01/2016"
...

You must be doing something that you are not including in your description of the issue.

Or maybe your package has gotten corrupted. You could try re-building it from scratch to eliminate that possibility.

But I have tested and proved that what you are trying to do should work.

CSV Not Displaying Correct Format From SSIS Export

1 Answers