0

I'm trying to automate a process with SSIS that exports data into a flat file (.csv) that is then saved to a directory, where it will be scanned and imported by some accounting software. The software (unfortunately) only recognizes dates that are in MM/DD/YYYY fashion. I have tried every which way to cast or convert the data pulled from SQL to be in the MM/DD/YYYY, but somehow the data is always recognized as either a DT_Date or DT_dbDate data type in the flat file connection, and saved down as YYYY-MM-DD.

I've tried various combinations of data conversion, derived columns, and changing the properties of the flat file columns to string in hopes that I can at least use substring operations to get this formatted correctly, but it never fails to save down as YYYY-MM-DD. It is truly baffling. The preview in the OLE DB source will show the dates as "MM/DD/YYYY" but somehow it always changes to "YYYY-MM-DD" when it hits the flat file.

I've tried to look up solutions (for example, here: Stubborn column data type in SSIS flat flat file connection manager won't change. :() but with no luck. Amazingly if I merely open the file in Excel and save it, it will then show dates in a text editor as "MM/DD/YYYY", only adding more mystery to this Bermuda Triangle-esque caper.

If there are any tips, I would be very appreciative.

Community
  • 1
  • 1
ibuddy
  • 1
  • 2
  • If I understand correctly, you are _exporting_ to a file that doesn't exist yet. Do you have a derived column transform which converts the SQL column to a string? Here's a link that shows you how to do that: http://stackoverflow.com/questions/16711349/ssis-expression-convert-date-to-string. You need to ensure you are adding a new column, not replacing the existing column. Now you should be able to right click/show advanced editor and in _input and output properties_, you can observe the data type of the column. It should stay string all the way through. – Nick.Mc Aug 27 '15 at 23:35
  • One more thing - make sure you are opening the CSV file in a **text** editor (notepad) to inspect. Don't open it in Excel - that just confuses things. A csv file is actually a text file not an excel file. – Nick.Mc Aug 27 '15 at 23:37
  • ..and lastly don't be afraid to delete the text file target and readd. Sometimes it can get it's datatypes stuck and confused even if the file doesn't exist. Unfortunately wrestling with data types in SSIS is a daily occurrence. It often gets it wrong and stubbornly refuses to fix it until you perform a magical set of operations in a set order that you won't be able to remember next time. – Nick.Mc Aug 27 '15 at 23:39
  • Thanks for your help; I've been replacing the column in the derived column step, so I'll try adding a new column. Also, I do usually inspect the resulting file in notepad - my only point with Excel was some puzzlement over the fact that if do open it with Excel and save it, then when I reopen with a text editor everything is formatted correctly (yet apparently this is a herculean task in SSIS). Though the plan B would then be executing some sort of script to open it with Excel and save it again, I suppose. Thank you very much for your tips, though – ibuddy Aug 28 '15 at 15:22

2 Answers2

0

It's been a while but I just came across this today because I had the same issue and hope to be able to spare someone the trouble of figuring it out. What worked for me was adding a new field in the Derived Column transform rather than trying to change the existing field.

Edit

I can't comment on Troy Witthoeft's answer, but wanted to note that if you have a Date type input, you wouldn't be able to do SUBSTRING. Instead, you could use something like this:

(DT_WSTR,255)(MONTH([Visit Date])) + "/" + (DT_WSTR,255)(DAY([Visit Date])) + "/" + (DT_WSTR,255)(YEAR([Visit Date]))

RiSt
  • 63
  • 1
  • 8
0

This is a date formatting issue.

In SQL and in SSIS, dates have one literal string format and that is YYYY-MM-DD. Ignore the way they appear to you in the data previewer and/or Excel. Dates are displayed to you based upon your Windows regional prefrences.

enter image description here

Above - unlike the US - folks in the UK will see all dates as DD/MM/YYYY. The way we are shown dates is NOT the way they are stored on disk. When you open in Excel it does this conversion as a favor. It's not until you SAVE that the dates are stored - as text - according to your regional preferences.

In order to get dates to always display the same way. We need to save them not as dates, but as strings of text. TO do this, we have to get the data out of a date column DT_DATE or DT_DBDATE and into a string column: DT_STR or DT_WSTR. Then, map this new string column into your csv file. Two ways to do this "date-to-string" conversion...

First, have SQL do it. Update your OLE DB Source query and add one more column... 

SELECT 
    *, 
    CONVERT(VARCHAR(10), MyDateColumn, 101) AS MyFormattedDateColumn
FROM MyTable

The other way is let SSIS do it. Add a Derived Column component with the expression 

 SUBSTRING([MyDateColumn],6,2) + "/" + SUBSTRING([MyDateColumn],8,2) + "/" + SUBSTRING([MyDateColumn],1,4)

Map the string columns into your csv file, NOT the date columns. Hope this helps.

Troy Witthoeft
  • 2,498
  • 2
  • 28
  • 37