0

I am getting confusion when loading data from staging to target for the date column by using ssis as ETL tool. Staging:

varchar (datatype)-'09/05/2017'

Target: conversion transformation is used to convert to date datatype.Now records like 09/05/2017. But while loading to final table Record is like 2017-09-05 00:00:00 which is datetime datatype.

Actual expected result:2017-05-09 00:00:00

Note:OTHER records are inserted with correct format.

Troy Witthoeft
  • 2,498
  • 2
  • 28
  • 37
Ali
  • 3
  • 1
  • 6
  • 1
    Possible duplicate of [Default Culture Used by SQL Server For Parsing](https://stackoverflow.com/questions/45508704/default-culture-used-by-sql-server-for-parsing) – Tanner Aug 09 '17 at 08:10

1 Answers1

2

In SSIS - The LocaleID property of your DataFlow components handle whether dates are parsed into MM/DD/YYYY or DD/MM/YYYY.

enter image description here

In SQL - A similar thing is handled by the SET LANGUAGE command.

SET LANGUAGE us_english
DECLARE @d DATETIME = '1929/12/18'

SET LANGUAGE british
DECLARE @d DATETIME = '1929/12/18' -- fails

Check your settings in both SQL and SSIS. Adjust if necessary.

Troy Witthoeft
  • 2,498
  • 2
  • 28
  • 37
  • @thanks for the help troy ....I have changed the setting,Now it is working fine.. – Ali Aug 11 '17 at 10:53