0

I have an SSIS package, which I am running a Stored Procedure and save the resultset in a object type of variable (as temptable) in the control flow, and in the data flow I am using a script to retrieve data from the object type of variable saving the result set in an excel file.

Everything works well except the date column. It should be in dd/mm/yyyy format, however whatever I have tried I could not prevent it to be in m/d/yyyy format in the excel.

What I have tried are:

1- Added this to connectionstring of Excel ConnectionManager -> Excel 12.0;HDR=Yes;IMEX=0;Readonly=False and Excel 12.0;HDR=Yes;IMEX=1;Readonly=False. Both did not work.

2- Added a Derived Column task, derived a new "DerivedMyDate" column as STR, WSTR, Date. Used this column to save the date value in the excel file, where type is set as STR and DATE. None of my trials worked.

3- Added Data Conversion task, tried all same things that I've tried in 2nd step, none worked.

4- Added a dummy first line in the excel, where the data column is like 15/01/1900. Does not work, it is adding the other rows below where date values in m/d/yyyy format.

Checked those pages, the answers did not work for me: Date format problem using SSIS for Excel into SQL Server

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/7e34a0af-a744-4a51-b925-3284d0fba296/problem-in-date-format-while-exporting-to-excel-destination-from-flat-file-source-in-ssis?forum=sqlintegrationservices

TimeZone of my PC: (UTC-05:00) Eastern Time (US & Canada), changing this is not an option.

Any help or advice would be appreciated.

Eray Balkanli
  • 7,752
  • 11
  • 48
  • 82
  • 1
    Why not create a template xlsx file, with all the columns with the correct formatting, and then export to that instead? *(You may want to copy the file first to a different name, if you are generating dynamically named files.)* – Thom A Nov 26 '18 at 22:09
  • i did, even there is a dummy row in the template xlsx. Still, a value with date (row[mydate].tostring()) is saved as "date" in the excel when new rows are added, instead of "custom dd/mm/yyyy" – Eray Balkanli Nov 26 '18 at 22:11
  • Pass the value as a date, not as a formatted string. Like I said, make sure the **column** has the formatting (not the top row, that's not useful for row 3 onwards). – Thom A Nov 26 '18 at 22:13
  • 1
    All ye who pass here give up all hope.... or run excel specific code afterwards in a script task that opens the excel file and formats the column afterwards. Something like this: https://stackoverflow.com/questions/48093071/format-excel-destination-column-in-ssis-script-task if at all possible, _avoid_ using excel as any kind of data interchange or report format. – Nick.Mc Nov 26 '18 at 23:09

1 Answers1

1

I will try to propose a little modify a T-SQL query in a next solution for you.
At the step of extracting data from the Stored Procedure, if this one returns not many columns, you should declare a table variable with a names, types and columns count appropriated with your stored procedure columns, for example @var. Then you need to pass executed stored prococedure result in variable,

INSERT INTO @VAR
EXEC sp_your_stored_prodedure

At the last step you should retrieve data not from stored procedure, but from @var, and to add in the select statement new field with CONVERT T-SQL function with date format 103 :

SELECT
      v.*
      ,CONVERT (NVARCHAR, v.date_field, 103) as target_date_format
FROM @var AS v

After this, you may work with this DataFlow and Date in the appropriate format.

mchist
  • 97
  • 8
  • After adding a new field in nvarchar format, I got this value using tostring() in my script (Data flow) and saved it to a WSTR type of field. Worked. Thanks. – Eray Balkanli Nov 27 '18 at 16:47