CODEWITHSUNDEEP
excelssis
gaurav gupta
gaurav gupta

Reputation: 11

Date format issue in ssis

I have to import data from Excel file to SSIS but i am facing a problem in date column,in excel sheet date format is yyyy/mm/dd and when it gets upload in database it get change into yyyy/dd/mmm format.

How to fix this?

Upvotes: 1

Views: 2231

Answers (3)

Amarnath
Amarnath

Reputation: 8865

Use the SUBSTRING function in the derived column while importing the date,

(LEN(TRIM(SUBSTRING(ReceivedDateTime,1,8))) > 0 ? (DT_DBDATE)(SUBSTRING(ReceivedDateTime,1,4) + "-" + SUBSTRING(ReceivedDateTime,5,2) + "-" + SUBSTRING(ReceivedDateTime,7,2)) : (DT_DBDATE)NULL(DT_WSTR,5))

If the Data is there then use Substring function to extract the exact date that sets in the DB or if the date does not exist then insert NULL in the DB.

Upvotes: 1

kuldeep verma
kuldeep verma

Reputation: 326

First Create Table into Your Database Using below Command 

   CREATE TABLE [dbo].[Manual] (
   [Name] nvarchar(255),
   [Location] nvarchar(255),
   [Date] datetime
   )
   SET DATEFORMAT YDM

By using DATEFORMAT YDM ,Date Will import in YYYY/DD/MM Format .Before runnung package modify the package and at the time of Column mapping select The Check Box "Delete Rows in Destination Table" .

Then Execute the Package . It Will work .

Upvotes: 0

Diego
Diego

Reputation: 36146

I see two options:

  1. Data Conversion Transformation to convert to a text string in the appropriate format. Using SSIS data types.
  2. Add a script task that converts the data type. Using VB data types

Upvotes: 0

Related Questions