CODEWITHSUNDEEP
sql-serverexceldatessisssis-2012
mehtat_90
mehtat_90

Reputation: 628

Convert excel column from General to Date in SSIS

I have a nvarchar(50) column in SQL which is of format mm/dd/yyyy. I am trying to import this data in excel using SSIS. In ssis package, I am creating a column Date_1 with datatype Date in Execute SQL task (Since .xlsx file needs to be created dynamically) and in my data flow task I am converting Date_1 to date(DT_DATE). But after data migration when I check my file column Date_1 is of General data type and not Date. Can someone help me how to convert General column to Date

enter image description here

enter image description here

Upvotes: 1

Views: 905

Answers (1)

Hadi
Hadi

Reputation: 37313

The drop down list you are showing in the Microsoft Excel interface is not related to the data type it is the Number Format property which is used to change the way the value is shown in Excel.

To change this property you need to use Microsoft.Interop.Excel library within a Script Task and change the Excel.Range.NumberFormat property. As example:

Range rg = (Excel.Range)xlWorksheet.Cells[1,1];
rg.EntireColumn.NumberFormat = "MM/dd/yyyy";

References

Upvotes: 1

Related Questions