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
TimeZone of my PC: (UTC-05:00) Eastern Time (US & Canada), changing this is not an option.
Any help or advice would be appreciated.
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.